c# - 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.

Answer 1

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;