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.