Error converting nvarchar to data type int

Question

I am getting this error when I try to call my stored procedure form code behind in my website. I have been stuck for quite a while now, as I do not know anywhere I am converting or declaring a value as an integer. This is my SQL statement:

create procedure GetRepPhoneID
@Rep        nvarchar(100),
@phoneID    nvarchar(100) output
as
set @phoneID = (select concat(CustomerRepPh, '~', cast(RepID as nvarchar(100))) as 'PhoneAndID'
from Reps
where CustomerRep=@Rep)
return @phoneID
go

Then from my c# code behind I am trying to call the stored procedure:

public static string GetRepPhone(string Rep) { string Connection = WebConfigurationManager.ConnectionStrings["JDC_DatabaseConnectionString"].ConnectionString; SqlConnection sqlConnection = new SqlConnection(Connection);

    //This funciton will take all of the values and create them.
    try
    {
        sqlConnection.Open();
    }
    catch (Exception err)
    {
        Console.WriteLine(err.Message);
    }

    SqlCommand cmd = new SqlCommand();
    cmd.Connection = sqlConnection;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "GetRepPhoneID";           //getting the  procedure created in SQL.

    SqlParameter CustomerParam = new SqlParameter();
    CustomerParam.ParameterName = "Rep";
    CustomerParam.SqlDbType = SqlDbType.NVarChar;
    CustomerParam.Value = Rep;
    CustomerParam.Direction = ParameterDirection.Input;

    //We are using an output parameter not a return one because it is a string.
    SqlParameter ReturnParam = new SqlParameter("phoneID", SqlDbType.NVarChar, 100);
    ReturnParam.Direction = ParameterDirection.Output;

    cmd.Parameters.Add(CustomerParam);
    cmd.Parameters.Add(ReturnParam);

    cmd.ExecuteNonQuery();

    sqlConnection.Close();
    return ReturnParam.Value.ToString();
}

I am doing the same thing multiple times in my code, but they all return integers so there has been no error thrown so I know it should work. The error is being thrown on the cmd.ExecuteNonQuery() line. The exact error is:

Conversion failed when converting the nvarchar value '(111)222-6666~29' to data type int.

I understand that I cannot convert that string to an integer, but I do not see anywhere in my code I am declaring an integer, or I am trying to convert.

Any help will be appreciated. Thanks.

Solomon Rutzky · Accepted Answer · 2015-01-15 17:30:36Z

3

You are confusing a RETURN value for an OUTPUT parameter. A RETURN is an optional status code of type INT. Declare another parameter as OUTPUT.

Meaning, this is invalid in the Stored Procedure:

return @phoneID

Instead, add @phoneID nvarchar(100) OUTPUT to the parameter list and remove the DECLARE @PhoneID:

CREATE PROCEDURE GetRepPhoneID
(
  @Rep        NVARCHAR(100),
  @phoneID    NVARCHAR(100) OUTPUT
)
AS
SET NOCOUNT ON;

SELECT @phoneID = concat(CustomerRepPh, '~', RepID)
FROM Reps
WHERE CustomerRep = @Rep;

The above represents the entire proc. You don't need the RETURN or the SET.

Then in the C# code, you need to change how that parameter is specified:

SqlParameter ReturnParam = new SqlParameter("phoneID", SqlDbType.NVarChar, 100);
ReturnParam.Direction = ParameterDirection.Output;

Then remove this line as it is not needed since the value of the parameter will remain after the connection is closed:

string PhoneAndID = cmd.Parameters[1].Value.ToString();

And change the return to be:

return ReturnParam.Value.ToString();

Lastly, you probably need to update the declaration of the input param as follows:

SqlParameter CustomerParam = new SqlParameter("Rep", SqlDbType.NVarChar, 100);
CustomerParam.Value = Rep;
CustomerParam.Direction = ParameterDirection.Input;

edited Jan 15, 2015 at 17:30

answered Jan 15, 2015 at 17:11

Solomon Rutzky

49.3k11 gold badges141 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Chase Ernst Over a year ago

Thank you for your help. This got rid of the original error, but now is throwing String[1]: the Size property has an invalid size of 0.

Solomon Rutzky Over a year ago

@ChaseErnst I just edited the C# part with the param declaration.

Chase Ernst Over a year ago

This sent me back to the original error. I have updated the question to my current code.

Solomon Rutzky Over a year ago

@ChaseErnst you did not do all of my edits. You still have the initial problem of using the RETURN. I posted the stored proc code in its entirety. And I posted a final edit showing how to declare the input param in the C# code.

Chase Ernst Over a year ago

Oh yes, I just noticed that and made the change this worked out for me. Thank you for you help and patience!!

Collectives™ on Stack Overflow

Error converting nvarchar to data type int

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related