Click here to Skip to main content
13,356,787 members (65,087 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: , +
I have created the following stored procedure in sql server 2012

Alter Proc usp_InsertUserEntries
( @userName varchar(35)
, @RoleId char(1)
, @password varchar(50)
, @userGuid UNIQUEIDENTIFIER
, @UserStat char(1) 
, @UserId int output
)
As
Begin
	Declare @count int
	Declare @ReturnCode int

	select @count=Count(*) from Lib_Users where UserName=@userName
	If @count=0
	Begin
		Insert into Lib_Users ([UserName],[RoleID] ,[UserPassword] ,[UserGuid] ,[StatusID] ,[UpdatedTimestamp],[EntryTimestamp])
		Values(@userName,@RoleId,@password,@userGuid,@UserStat,GETDATE(),GETDATE())
		Select @ReturnCode = @@Error
		if @ReturnCode=0
		Begin
			select @UserId=SCOPE_IDENTITY()
			--print @UserId
			--print @ReturnCode
		END
		else
		Begin
			Set @ReturnCode=1
		END
	End
	Else 
	Begin 
		Set @ReturnCode=2
	End
		Return @ReturnCode	
End

When i am executing the stored procedure in sql server it is getting executed properly with out any error/issue with proper error code returned.
But when i am calling the same proc from my code in VS 2010, it is returning error code 1 or -1.
Did i wrote the C# code wrong?

public static string AddUser(string userName, string roleId, string passWrd, Guid usrGuid
            , string userStatus)
        {
            int returnCode;
            string userID = string.Empty;
            try
            {
                SqlCommand cmdInsertNewRec = new SqlCommand("usp_InsertUserEntries", sqlConn);
                cmdInsertNewRec.CommandType = CommandType.StoredProcedure;

                cmdInsertNewRec.Parameters.Add("@userName", SqlDbType.VarChar, 35).Value = userName;
                cmdInsertNewRec.Parameters.Add("@RoleId", SqlDbType.Char, 1).Value = roleId;
                cmdInsertNewRec.Parameters.Add("@password", SqlDbType.VarChar, 50).Value = passWrd;
                cmdInsertNewRec.Parameters.Add("@userGuid", SqlDbType.UniqueIdentifier).Value = usrGuid;
                cmdInsertNewRec.Parameters.Add("@UserStat", SqlDbType.Char, 1).Value = userStatus;


                SqlParameter userIdOut = new SqlParameter("@UserId", SqlDbType.Int);
                userIdOut.Direction = ParameterDirection.Output;
                cmdInsertNewRec.Parameters.Add(userIdOut);

                OpenSqlConnection();
                returnCode = cmdInsertNewRec.ExecuteNonQuery();
                
                if (returnCode == 0)
                {
                    userID = userIdOut.Value.ToString();
                }
            }
            catch (Exception ex)
            {
                //Log
                return userID;
            }
            finally
            {
                CloseSqlConnection();
            }
            return userID;
        }
    }


I am not able to debug from VS 2010 cause it is giving me this message "This server version is not supported. Only servers up to Microsoft SQL Server 2008 are supported."

I am stuck with this issue for long. Any suggestion would be appreciated.
Posted 17-Apr-14 23:44pm
Updated 18-Apr-14 2:34am
v3
Comments
syed shanu 18-Apr-14 4:52am
   
Did you check with your SP of same input of your code "@userName varchar(35)
, @RoleId char(1)
, @password varchar(50)
, @userGuid varchar (50)
, @UserStat char(1) "
All this parameter give the same input as in your c# input and run the sp and check for the result.
Member 10758061 18-Apr-14 7:35am
   
using same input as code in the sql server while executing proc..it is executing successfully
saber rezaii magham 18-Apr-14 8:17am
   
"This server version is not supported. Only servers up to Microsoft SQL Server 2008 are supported."
this is a clear error!
your visual studio cant connect to sql 2012!
to do this you must use vs2010 SP1
Tadit Dash 18-Apr-14 9:02am
   
Yeah !!! Correct.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

In your SP i don't think any problem,first insert value in your SP from back end, check you got any issue.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Debug your SP first,try executing inserting some values, if it works fine, thn obviously it will problem with your DAL layer

or the other scenario would be compatibility version problem.

Regards,
Praveen Nelge
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Resolved the error...but any of my stored proc is returning return code as non-zero, although they are executing fine..may be an compatibility issue..ignoring the return code for now...
Thanks for your help
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

USE [test]
GO
/****** Object: StoredProcedure [dbo].[AddUpdateEmpData_sp] Script Date: 10/17/2015 14:21:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- exec dbo.AddUpdateEmpData_sp '','Mr','ABC','PQR','XYZ','APX','ENGG','M','','abc@gmail.com','xyz@gmail.com','2302046','253','2485918','240','1234','98437424922','12345','',''
ALTER PROCEDURE [dbo].[AddUpdateEmpData_sp]( @UserID uniqueidentifier,
@Title nvarchar(100),
@FirstName nvarchar(100),
@MiddleName nvarchar(100),
@LastName nvarchar(200),
@DisplayName nvarchar(max),
@Salutation nvarchar(5),
@Gender nvarchar(1),
@ManagerID uniqueidentifier,
@Email nvarchar(150),
@AlternateEmail nvarchar(300),
@Phone nvarchar(20),
@PhoneExt nvarchar(5),
@Phone1 nvarchar(20),
@Phone1Ext nvarchar(5),
@Fax nvarchar(20),
@Mobile nvarchar(20),
@Pager nvarchar(20),
@CreatedBy uniqueidentifier,
@ModifiedBy uniqueidentifier
)
AS
BEGIN
declare @ActiveStatus bit,@CreatedOn datetime,@ModifiedOn datetime
--insert
set @ActiveStatus='1'
set @CreatedOn= GETDATE()
set @ModifiedOn = GETDATE()
if(@UserID is null)
BEGIN
set @UserID=NEWID()
set @CreatedBy=@UserID
set @ModifiedBy=@UserID
insert into test.dbo.EmpDb(UserID,Title,FirstName,MiddleName,LastName,DisplayName,Salutation,Gender,ManagerID,
Email,AlternateEmail,Phone,PhoneExt,Phone1,Phone1Ext,Fax,Mobile,Pager,ActiveStatus,
CreatedOn,CreatedBy,ModifiedOn,ModifiedBy)
VALUES(@UserID,@Title,@FirstName,@MiddleName,@LastName,@DisplayName,@Salutation,@Gender,@ManagerID,
@Email,@AlternateEmail,@Phone,@PhoneExt,@Phone1,@Phone1Ext,@Fax,@Mobile,@Pager,@ActiveStatus,
@CreatedOn,@CreatedBy,@ModifiedOn,@ModifiedBy);
if(@@ROWCOUNT=0)
begin
--error
select 'Record Not Inserted' as ReturnStatus---Record Not Saved
return
end
select 'Record Inserted SuccessFully' as ReturnStatus--- Record Saved Successfully
END
else
begin
--update
update test.dbo.EmpDb set Title=@Title,FirstName=@FirstName,MiddleName=@MiddleName,LastName=@LastName,DisplayName=@DisplayName,
Salutation=@Salutation,Gender=@Gender,ManagerID=@ManagerID,Email=@Email,AlternateEmail=@AlternateEmail,Phone=@Phone,
PhoneExt=@PhoneExt,Phone1=@Phone1,Phone1Ext=@Phone1Ext,Fax=@Fax,Mobile=@Mobile,Pager=@Pager,ActiveStatus=@ActiveStatus,
ModifiedBy=@ModifiedBy
WHERE UserID=@UserID;
if(@@ROWCOUNT=0)
begin
--error
select 'Record Not Updated' as RetStatus---Record Not Saved
return
end
select 'Record Updated Successfully' as RetStatus--- Record Saved Successfully
end
END

this is my stored procedure, the problem i facing is
Msg 8114, Level 16, State 5, Procedure AddUpdateEmpData_sp, Line 0
Error converting data type varchar to uniqueidentifier.
  Permalink  
v2
Comments
Richard Deeming 20-Oct-15 10:22am
   
Do not post your questions as solutions to other questions.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web03 | 2.8.180111.1 | Last Updated 20 Oct 2015
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100