CODEWITHSUNDEEP
c#sqlsql-serverstored-proceduresado.net
user841311
user841311

Reputation: 597

Error converting data type varchar to int

This is my stored procedure code 

ALTER procedure [Proc_Add_User]
    (@UserId varchar(20), 
     @UserName varchar(100),
     @Page_Name varchar(20), 
     @AccessIndicator int, 
     @CreatedBy varchar(50), 
     @returnStatus varchar(50) output)
as
   DECLARE @intErrorCode INT
DECLARE @Page_Indicator INT
begin
BEGIN TRAN
Set @Page_Indicator = (select Page_Indicator from Pages where Page_Name=@Page_Name);
if (select count(*) from Users where UserId=@UserId and UserName=@UserName) > 0 begin               
    if (select count(*) from User_Credentials where Page_Indicator=@Page_Indicator and 
    UserId=@UserId  ) > 0 
    set @returnStatus='User already has access'             
    else 
    insert into User_Credentials(UserId,Page_Indicator,Access_Indicator,CreatedBy) 
        values (@UserId,@Page_Indicator,@AccessIndicator,@CreatedBy)
    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM            
end
else begin  
    insert into Users(UserId,UserName,CreatedBy) 
    values(@UserId,@UserName,@CreatedBy)
    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM
    insert into User_Credentials(UserId,Page_Indicator,Access_Indicator,CreatedBy) 
        values (@UserId,@Page_Indicator,@AccessIndicator,@CreatedBy)
    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM    
end
COMMIT TRAN
if(@returnStatus is null)
    set @returnStatus='Success';
PROBLEM:
IF (@intErrorCode <> 0) BEGIN
set @returnStatus= 'Unexpected error occurred!'
ROLLBACK TRAN
end
end

And I am calling this from the code pasted below:

Con.Open();
cmd = new OleDbCommand();
cmd.Connection = Con;

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Proc_Add_User";

cmd.Parameters.Clear();
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = userLanId;
cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = userName;
cmd.Parameters.Add("@Page_Name", SqlDbType.VarChar).Value = pageName;
cmd.Parameters.Add("@AccessIndicator", SqlDbType.Int).Value = accessIndicator;
cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = createdBy;

OleDbParameter output = new OleDbParameter("@returnStatus", SqlDbType.VarChar);
output.Direction = ParameterDirection.Output;
cmd.Parameters.Add(output); 

int result = cmd.ExecuteNonQuery();

I am getting the error mentioned at the ExecuteNonQuery statement. What's confusing to me is I am able to execute the stored procedure in SSMS but not from my application (front-end). I provided the same values too yet it fails from my app.

I double checked to make sure the order of parameters passed match and are of same data type but still it throws this error. I can paste my stored proc code here if wanted so let me know..Thanks in advance!

EDIT

OOPS! I just realized that all the inserts are all happening and getting committed fine in the database. It's just this error is getting caught inside catch block in my app. Any ideas?

I can not ignore it because based on the return value of ExecuteNonQuery(), I have some statements and also it's not going through the code present after ExecuteNonQuery().

Upvotes: 0

Views: 4216

Answers (1)

Daniel Paoliello
Daniel Paoliello

Reputation: 306

This is most likely because you are using SqlDbType with OleDbParameters:

OleDbParameter output = new OleDbParameter("@returnStatus", SqlDbType.VarChar);

This causes .NET to use the OleDbParameter(String, Object) constructor, setting the value of the parameter to SqlDbType.VarChar which it assumes is an int.

You should use this instead:

OleDbParameter output = new OleDbParameter("@returnStatus", OleDbType.VarChar);

And change your calls to cmd.Parameters.Add to use OleDbType as well.

Alternatively, you could use System.Data.SqlClient instead of OleDb

Upvotes: 1

Related Questions