Return value parameter returns null
I was really confused this morning. I was executing a stored proc that returned a single row of data, and a return value. However, the return value was always null - even though the stored proc ALWAYS returned a return value. The code was domething like this:
-
retval.Direction = ParameterDirection.ReturnValue;
-
command1.Parameters.Add(retval);
-
-
using (SqlDataReader reader = command1.ExecuteReader())
-
{
-
if (reader.Read())
-
{
-
var1 = reader["Col1"].ToString();
-
var2 = reader["col2"].ToString();
-
}
-
if (retval.Value != null)
-
errorCode = Convert.ToInt32(retval.Value);
-
else
-
errorCode = -1;
-
}
It turns out that you need to close the reader before you can access the return value parameter.
From MSDN Library: SqlDataReader.Close()
The Close method fills in the values for output parameters, return values and RecordsAffected...
I can see why it's been implemented like that (the return value, output parameters and number of records affected come at the end, after the result set) - but I have to say it is counter-intuitive to have to close the reader before you can access these values.