Output parameter with SqlDataReader...

[mo]

I'm trying to use an Output parameter to return the number of records at the
same time as filling a SqlDataReader using cmd.ExecuteReader(); I get a
'Object reference not set to an instance of an object.' when I try to do
this... Is this not possible???

int recordCount = 0;
SqlConnection cn = new
SqlConnection(ConfigurationSettings.AppSettings["MyDB"]);
SqlCommand cmd = new SqlCommand("stored_procedure_name",cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@criteria", SqlDbType.VarChar, 50).Value = criteria;
cmd.Parameters.Add("@searchString", SqlDbType.VarChar, 50).Value =
searchString;
cmd.Parameters.Add("@sortBy", SqlDbType.VarChar, 50).Value = sortBy;
cmd.Parameters.Add("@sortDirection", SqlDbType.VarChar, 4).Value =
sortDirection;
cmd.Parameters.Add("@page", SqlDbType.Int).Value = pageNumber;
cmd.Parameters.Add("@pageSize", SqlDbType.Int).Value = pageSize;
cmd.Parameters.Add("@recordCount", SqlDbType.Int);
cmd.Parameters["@recordCount"].Direction = ParameterDirection.Output;

SqlDataReader dr;
cn.Open();
try
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
recordCount = (int)cmd.Parameters["@recordCount"].Value; //why won't this
work???
if (recordCount == 1)
{
while (dr.Read())
{
//go directly to the record...
Response.Redirect("default.aspx?tab=company&coid=" +
dr["coid"].ToString());
}
}
else
{
//display a grid for the user to select from...
srchResultsCompany.DataSource = dr;
srchResultsCompany.DataBind();
lblSearchResults.Text = "<i>" + recordCount.ToString() + " results for " +
searchString + "...</i>";
}
}
catch (SqlException sqlError)
{
//handle error...
}

[Lachlan]

Im not 100% sure but I think a datareader will not retrieve any rows until
you call read(). So I think you would have to read through the datareader to
get the row count. I could be wrong you should post this to the ado.net
group.

Lachlan

"mo" <m...@industrypro.com> wrote in message
news:#lYdvo2MCHA.2488@tkmsftngp12...

[Ryan LaNeve]

"mo" <m...@industrypro.com> wrote in message news:#lYdvo2MCHA.2488@tkmsftngp12...

> I'm trying to use an Output parameter to return the number of records at the
> same time as filling a SqlDataReader using cmd.ExecuteReader(); I get a
> 'Object reference not set to an instance of an object.' when I try to do
> this... Is this not possible???

> <snipped>

> dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
> recordCount = (int)cmd.Parameters["@recordCount"].Value; //why won't this

> <snipped>

Straight from the documentation:
(SqlDataReader Class - Overview)
While the SqlDataReader is in use, 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.

Ryan LaNeve

[mo]

thanks for the info...

I found that calling ExecuteNonQuery() first lets me get the output param
then I can call the ExecuteReader()... not the most efficient way I can
think of, but it seems better than some of the other examples I've been
looking through...

"Ryan LaNeve" <rya...@spamlaneve.com> wrote in message
news:er4Qbs4MCHA.944@tkmsftngp10...