c# sql server procedure return object not set to an instance of an object

Question

I have a stored procedure

create proc GetCodes
@Code varchar(10),
@Page int = 1,
@Rows int = 10,
@output int output

as

select * from(
  Select *, row_number() over(order by code) as RowNum
  from codes
  where code like '%'+@code+'%'
) as tbl
where @Rows = 0 or RowNum between ((@Page - 1) * @Rows + 1) and (@Page * @Rows)
order by code

set @output = @@ROWCOUNT

and I have an c# script

SqlCommand cmdSelect = new SqlCommand("GetCodes", SQLData);
cmdSelect.CommandType = CommandType.StoredProcedure;
cmdSelect.Parameters.Add(new SqlParameter("@Code", (Request.QueryString["Code"] != null) ? Request.QueryString["Code"] : ""));
cmdSelect.Parameters.Add(new SqlParameter("@Page", (Request.QueryString["Page"] != null) ? Request.QueryString["Page"] : "1"));
cmdSelect.Parameters.Add(new SqlParameter("@Rows", (Request.QueryString["Rows"] != null) ? Request.QueryString["Rows"] : "10"));
cmdSelect.Parameters.Add(new SqlParameter("@output", SqlDbType.Int));
cmdSelect.Parameters["@output"].Direction = ParameterDirection.Output;
SQLData.Open();
SqlDataReader dtrReader = cmdSelect.ExecuteReader();

int numRows = (int)cmdSelect.Parameters["@output"].Value;

it is giving me an error "object reference not set to an instance of an object" for the line

int numRows = (int)cmdSelect.Parameters["@output"].Value;

with the same outcome, any suggestions?

Check your exception message or inner exception. You'll see what's not initialized — Odys, Jun 13 '13 at 21:51
Almost all cases of `NullReferenceException` are the same. Please see "[What is a NullReferenceException in .NET?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-in-net)" for some hints. — John Saunders, Jun 14 '13 at 02:37
@odyodyodys: how would an inner exception show what's not initialized? — John Saunders, Jun 14 '13 at 02:38

Steve · Accepted Answer · 2013-06-14T07:11:54.800

You need to close the SqlDataReader before trying to read the value of an output parameter.

 SqlDataReader dtrReader = cmdSelect.ExecuteReader();
 ..... do your stuff....
 dtrReader.Close();

 // Without the previous close the Value is still null and casting to int fails
 int numRows = (int)cmdSelect.Parameters["@output"].Value;

From the REMARKS section on SqlDataReader

While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.

Remember also that the best approach to using disposable objects like SqlConnection, SqlCommand and SqlDataReader is through the use of the using statement that take care to close and dispose the objects. So, for example you could write

 using(SqlDataReader dtrReader = cmdSelect.ExecuteReader())
 {

     ..... do your stuff....

 } // At the closing brace the dtrReader will b closed and disposed

You should use "using". using (SqlDataReader dtrReader = cmdSelect.ExecuteReader()) { // do stuff } and the command should be closed as well. — Bauss, Jun 14 '13 at 00:58

c# sql server procedure return object not set to an instance of an object

1 Answers1