ExecuteReader() v. ExecuteNonQuery()

[Mark]

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();

[Marina]

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();