[Resolved] [2.0] SQL data type problem

[Techno]

this is a wierd one but hope someone can help

I have a stored proc which returns me nvarchar (50) - the data for this column is something like this:

23vv54sdu4 (or whatever)

the stored procedure works fine as I have ran it in Query Analyzer and the syntax is correct. it gives me the return value (the example data I gave above)

in C#, I am trying to call this stored procedure however I always get this error:

"Syntax error converting the nvarchar value '23vv54sdu4 'to a column of data type int."

but no where have I set the parameters to data type int. Any ideas:

SqlParameter theCustomerIDParam = new SqlParameter("@theCustomerID", SqlDbType.Int);
theCustomerIDParam.Value = theCustomerID;

SqlParameter theReturnResult = new SqlParameter("@theReturnValue", SqlDbType.NVarChar, 50);
theReturnResult.Direction = ParameterDirection.Output;
                
theSQLCommand.Parameters.Add(theCustomerIDParam);
theSQLCommand.Parameters.Add(theReturnResult);

theSQLCommand.Connection.Open();
theSQLCommand.ExecuteNonQuery(); //error
theSQLCommand.Connection.Close();

[Techno]

Resolved.

no idea what I did. I guess I changed the stored procedure to not return the value but to leave it as is. in other words:

SQL:

//do stuff

return @theResult

now when you use ExecuteQuery() - it only returns the number of rows effected, therefore it cannot convert the returned value from SQL

you could use ExecuteScaler instead() as this returns the the first column of the first row - which is what I am looking for

Best practice:

in such a situation, use ExecuteScaler()