Getting "Error converting data type varchar to int" error only when called the stored proc from my code

Question

1.00/5 (1 vote)

See more:

, +

I have created the following stored procedure in sql server 2012

SQL

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?

C#

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 22:44pm

Member 10758061

Updated 18-Apr-14 1:34am

v3

Add a Solution

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.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

SRKarn · Answer 1 · 2014-04-18T05:59:00

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.

Posted 18-Apr-14 5:59am

SRKarn

praveen_07 · Answer 2 · 2014-04-18T17:14:00

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

Posted 18-Apr-14 17:14pm

praveen_07

Member 10758061 · Answer 3 · 2014-04-19T09:56:00

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

Posted 19-Apr-14 9:56am

Member 10758061

Member 11871351 · Answer 4 · 2015-10-16T23:08:00

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

SQL

Msg 8114, Level 16, State 5, Procedure AddUpdateEmpData_sp, Line 0
Error converting data type varchar to uniqueidentifier.

Getting "Error converting data type varchar to int" error only when called the stored proc from my code

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us