Error converting int to nvarchar
c#, sql, sqldatatypes
Solution
These two lines are the culprits:
SqlParameter outNewAgentNumber = new SqlParameter("@agentNumber", "");
SqlParameter outCurrentAgentNumber = new SqlParameter("@currentAgentNum", "");
By specifying an empty string (`""`) as the second parameter, the ADO.NET runtime assumes it's a string paramter. And since you didn't specify any lengths - it probably defaults to just one character length (that's why it's returning only the first digit).
So, my recommendation would be to always explicitly define the datatype (and if it's a string - also define a length!) for your SQL parameters:
SqlParameter outNewAgentNumber = new SqlParameter("@agentNumber", SqlDbType.Int);
outNewAgentNumber.Direction = ParameterDirection.Output;
outNewAgentNumber.Value = 0;
or
SqlParameter outNewAgentNumber = new SqlParameter("@agentNumber", SqlDbType.NVarChar, 50);
outNewAgentNumber.Direction = ParameterDirection.Output;
outNewAgentNumber.Value = "";
Problem
This error has me very confused. I have a stored procedure with two output parameters as defined below ``` @agentNumber int OUTPUT, @currentAgentNum int OUTPUT, ``` They are then populated using the SELECT statements below ``` SELECT @agentNumber = AgentNumber FROM AgentIdentification WHERE AgentTaxId = @ssn SELECT @currentAgentNum = AgentNumber FROM UniqueAgentIdToAgentId WHERE AgentId = @agentId ``` In the database, `AgentNumber` is defined in both tables, as an `int`. However, when I call this stored procedure in my C# code, I get a SQL exception stating: Error converting data type int to nvarchar. If I change the data types of the output parameters to `nvarchar`, the code will execute, however it will only return nothing more than the first digit of the whole number. Below is how the variables are defined in the C# code ``` SqlParameter outNewAgentNumber = new SqlParameter("@agentNumber", "");//Output parameter - leave blank SqlParameter outCurrentAgentNumber = new SqlParameter("@currentAgentNum", "");//Output parameter - leave blank outNewAgentNumber.Direction = ParameterDirection.Output; outCurrentAgentNumber.Direction = ParameterDirection.Output; ``` I add these parameters to a `SqlCommand` object, specify the appropriate database and `commandType`, then use `.ExecuteNonQuery()` to call the procedure. Again, what has me really confused is the error message stating that I'm using `nvarchar` as a data type, which could only (to the best of my knowledge) be referring to something on the database side. However, as I said I've double/triple checked and both `AgentNumber` columns are of type `int`. EDIT Changing the sqlParameter declarations to a starting value of 0 has solved this issue. I'm now running into the same problem with two other parameters. ``` SqlParameter outOperator = new SqlParameter("@operator", "");//Output parameter - leave blank SqlParameter outDate = new SqlParameter("@date", "");//Output parameter - leave blank ```