By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,335 Members | 1,293 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,335 IT Pros & Developers. It's quick & easy.

ExecuteReader() v. ExecuteNonQuery()

P: n/a
It appears that you can only retrieve an Output parameter from a SQL Server
stored procedure when using the ExecuteNonQuery of the SqlCommand class, and
cannot use the ExecuteReader() method. In the code sample below, it bombs
on the line with three *** when using the ExecuteReader, but works just fine
when you use the ExecuteNonQuery. When using the ExecuteReader, the
Output parameter appears to return a value of Null. The stored procedure
was not changed inbetween.

Suggestions? Thanks in advance.
Mark

String strConn =
ConfigurationSettings.AppSettings["myConnection"].ToString();
SqlConnection sqlConn = new SqlConnection(strConn);

SqlCommand sqlComm = new SqlCommand("p_my_proc", sqlConn);
sqlComm.CommandType = CommandType.StoredProcedure;

sqlComm.Parameters.Add(new SqlParameter("@bitSomeResult", SqlDbType.Bit));
sqlComm.Parameters["@bitSomeResult"].Direction = ParameterDirection.Output;

sqlConn.Open();
SqlDataReader dr = sqlComm.ExecuteReader();
//sqlComm.ExecuteNonQuery();
String strSomeResult =
sqlComm.Parameters["@bitSomeResult"].Value.ToString(); //***

if (strSomeResult.ToLower() == "True".ToLower())
{
//Do something
}
else
{
//Do something else
}

sqlConn.Close();
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
I believe to retrieve output parameters after an executereader call, you
have to get to the end of the reader, and I think close it.

"Mark" <fi**************@umn.edu> wrote in message
news:OX**************@tk2msftngp13.phx.gbl...
It appears that you can only retrieve an Output parameter from a SQL Server stored procedure when using the ExecuteNonQuery of the SqlCommand class, and cannot use the ExecuteReader() method. In the code sample below, it bombs
on the line with three *** when using the ExecuteReader, but works just fine when you use the ExecuteNonQuery. When using the ExecuteReader, the
Output parameter appears to return a value of Null. The stored procedure
was not changed inbetween.

Suggestions? Thanks in advance.
Mark

String strConn =
ConfigurationSettings.AppSettings["myConnection"].ToString();
SqlConnection sqlConn = new SqlConnection(strConn);

SqlCommand sqlComm = new SqlCommand("p_my_proc", sqlConn);
sqlComm.CommandType = CommandType.StoredProcedure;

sqlComm.Parameters.Add(new SqlParameter("@bitSomeResult", SqlDbType.Bit));
sqlComm.Parameters["@bitSomeResult"].Direction = ParameterDirection.Output;
sqlConn.Open();
SqlDataReader dr = sqlComm.ExecuteReader();
//sqlComm.ExecuteNonQuery();
String strSomeResult =
sqlComm.Parameters["@bitSomeResult"].Value.ToString(); //***

if (strSomeResult.ToLower() == "True".ToLower())
{
//Do something
}
else
{
//Do something else
}

sqlConn.Close();

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.