469,291 Members | 1,783 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,291 developers. It's quick & easy.

returning datareader with output parameter

How do you return a datareader from a stored procedure, but also return
an output parameter?

Here is my code, which is just returning a data reader at the moment :

_conn.Open();
SqlCommand _comm = new SqlCommand();
_comm.Connection = _conn;
_comm.CommandType = CommandType.StoredProcedure;
_comm.CommandText = "usp_CE_User_Authenticate";
_comm.Parameters.AddWithValue("@UserName",
txtUserName.Text.Trim());
_comm.Parameters.AddWithValue("@Password",
txtPassword.Text.Trim());
SqlDataReader dr = _comm.ExecuteReader();
User u;
if (dr.HasRows)
{
while (dr.Read())
*** Sent via Developersdex http://www.developersdex.com ***
Sep 11 '07 #1
8 21097
Mike,

You won't have access to the parameters values until you have cycled
through the data reader and read all the data.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Mike P" <mi*******@gmail.comwrote in message
news:ug**************@TK2MSFTNGP04.phx.gbl...
How do you return a datareader from a stored procedure, but also return
an output parameter?

Here is my code, which is just returning a data reader at the moment :

_conn.Open();
SqlCommand _comm = new SqlCommand();
_comm.Connection = _conn;
_comm.CommandType = CommandType.StoredProcedure;
_comm.CommandText = "usp_CE_User_Authenticate";
_comm.Parameters.AddWithValue("@UserName",
txtUserName.Text.Trim());
_comm.Parameters.AddWithValue("@Password",
txtPassword.Text.Trim());
SqlDataReader dr = _comm.ExecuteReader();
User u;
if (dr.HasRows)
{
while (dr.Read())
*** Sent via Developersdex http://www.developersdex.com ***

Sep 11 '07 #2
Mike,

Re-reading your post, it occurs to me that my response might not have
been the answer you were looking for. If you are looking to access the
return value, or the values of output parameters, then you have to access
the SqlParameter instance exposed by the Parameters collection, and get the
value from that, as it will be changed after execution (what I said in my
previous post about reading through all the data still applies).

To get a return value, you have to create a parameter with the Direction
property set to ParameterDirection.ReturnValue.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Mike P" <mi*******@gmail.comwrote in message
news:ug**************@TK2MSFTNGP04.phx.gbl...
How do you return a datareader from a stored procedure, but also return
an output parameter?

Here is my code, which is just returning a data reader at the moment :

_conn.Open();
SqlCommand _comm = new SqlCommand();
_comm.Connection = _conn;
_comm.CommandType = CommandType.StoredProcedure;
_comm.CommandText = "usp_CE_User_Authenticate";
_comm.Parameters.AddWithValue("@UserName",
txtUserName.Text.Trim());
_comm.Parameters.AddWithValue("@Password",
txtPassword.Text.Trim());
SqlDataReader dr = _comm.ExecuteReader();
User u;
if (dr.HasRows)
{
while (dr.Read())
*** Sent via Developersdex http://www.developersdex.com ***

Sep 11 '07 #3
I have tried this way, but I am just getting a null value being
returned. The sproc does return a value, so my syntax must be wrong :

_conn.Open();
SqlCommand _comm = new SqlCommand();
_comm.Connection = _conn;
_comm.CommandType = CommandType.StoredProcedure;
_comm.CommandText = "usp_CE_User_Authenticate";
_comm.Parameters.AddWithValue("@UserName",
txtUserName.Text.Trim());
_comm.Parameters.AddWithValue("@Password",
txtPassword.Text.Trim());
SqlParameter prmLogID = new SqlParameter("@LogID",
SqlDbType.Int, 4);
prmLogID.Direction = ParameterDirection.Output;
_comm.Parameters.Add(prmLogID);

SqlDataReader dr = _comm.ExecuteReader();

int intLogID =
Convert.ToInt32(_comm.Parameters["@LogID"].Value);

User u;
if (dr.HasRows)
{
while (dr.Read())
*** Sent via Developersdex http://www.developersdex.com ***
Sep 11 '07 #4
Mike,

As per my first post, the parameter won't be populated until AFTER you
have cycled through all the rows.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Mike P" <mi*******@gmail.comwrote in message
news:es**************@TK2MSFTNGP04.phx.gbl...
>I have tried this way, but I am just getting a null value being
returned. The sproc does return a value, so my syntax must be wrong :

_conn.Open();
SqlCommand _comm = new SqlCommand();
_comm.Connection = _conn;
_comm.CommandType = CommandType.StoredProcedure;
_comm.CommandText = "usp_CE_User_Authenticate";
_comm.Parameters.AddWithValue("@UserName",
txtUserName.Text.Trim());
_comm.Parameters.AddWithValue("@Password",
txtPassword.Text.Trim());
SqlParameter prmLogID = new SqlParameter("@LogID",
SqlDbType.Int, 4);
prmLogID.Direction = ParameterDirection.Output;
_comm.Parameters.Add(prmLogID);

SqlDataReader dr = _comm.ExecuteReader();

int intLogID =
Convert.ToInt32(_comm.Parameters["@LogID"].Value);

User u;
if (dr.HasRows)
{
while (dr.Read())
*** Sent via Developersdex http://www.developersdex.com ***

Sep 11 '07 #5
On Sep 11, 11:08 am, Mike P <mike.p...@gmail.comwrote:
I have tried this way, but I am just getting a null value being
returned. The sproc does return a value, so my syntax must be wrong :

_conn.Open();
SqlCommand _comm = new SqlCommand();
_comm.Connection = _conn;
_comm.CommandType = CommandType.StoredProcedure;
_comm.CommandText = "usp_CE_User_Authenticate";
_comm.Parameters.AddWithValue("@UserName",
txtUserName.Text.Trim());
_comm.Parameters.AddWithValue("@Password",
txtPassword.Text.Trim());
SqlParameter prmLogID = new SqlParameter("@LogID",
SqlDbType.Int, 4);
prmLogID.Direction = ParameterDirection.Output;
_comm.Parameters.Add(prmLogID);

SqlDataReader dr = _comm.ExecuteReader();

int intLogID =
Convert.ToInt32(_comm.Parameters["@LogID"].Value);

User u;
if (dr.HasRows)
{
while (dr.Read())
I believe that you need to Close() the SqlDataReader before you can
access output parameters...

Sep 11 '07 #6
Mel
Here is from the help file

the ParameterDirection is output, and execution of the associated SqlCommand
does not return a value, the SqlParameter contains a null value.
Output, InputOut, and ReturnValue parameters returned by calling
ExecuteReader cannot be accessed until you call Close or Dispose on the
SqlDataReader.

"Mike P" <mi*******@gmail.comwrote in message
news:es**************@TK2MSFTNGP04.phx.gbl...
>I have tried this way, but I am just getting a null value being
returned. The sproc does return a value, so my syntax must be wrong :

_conn.Open();
SqlCommand _comm = new SqlCommand();
_comm.Connection = _conn;
_comm.CommandType = CommandType.StoredProcedure;
_comm.CommandText = "usp_CE_User_Authenticate";
_comm.Parameters.AddWithValue("@UserName",
txtUserName.Text.Trim());
_comm.Parameters.AddWithValue("@Password",
txtPassword.Text.Trim());
SqlParameter prmLogID = new SqlParameter("@LogID",
SqlDbType.Int, 4);
prmLogID.Direction = ParameterDirection.Output;
_comm.Parameters.Add(prmLogID);

SqlDataReader dr = _comm.ExecuteReader();

int intLogID =
Convert.ToInt32(_comm.Parameters["@LogID"].Value);

User u;
if (dr.HasRows)
{
while (dr.Read())
*** Sent via Developersdex http://www.developersdex.com ***

Sep 11 '07 #7
Mike,

You're not listening to what Nicholas said. The value will not be in
the output parameter until AFTER you have read through all the data ...
so you would have to pick up the value AFTER your while loop.

If you need the value in advance then you'll have to make a separate
call to get that info. If that's not practical, and it's not a large
result set, you could consider filling a DataTable instead of using a
DataReader.

--Bob

Mike P wrote:
I have tried this way, but I am just getting a null value being
returned. The sproc does return a value, so my syntax must be wrong :

_conn.Open();
SqlCommand _comm = new SqlCommand();
_comm.Connection = _conn;
_comm.CommandType = CommandType.StoredProcedure;
_comm.CommandText = "usp_CE_User_Authenticate";
_comm.Parameters.AddWithValue("@UserName",
txtUserName.Text.Trim());
_comm.Parameters.AddWithValue("@Password",
txtPassword.Text.Trim());
SqlParameter prmLogID = new SqlParameter("@LogID",
SqlDbType.Int, 4);
prmLogID.Direction = ParameterDirection.Output;
_comm.Parameters.Add(prmLogID);

SqlDataReader dr = _comm.ExecuteReader();

int intLogID =
Convert.ToInt32(_comm.Parameters["@LogID"].Value);

User u;
if (dr.HasRows)
{
while (dr.Read())
*** Sent via Developersdex http://www.developersdex.com ***
Sep 11 '07 #8
Thanks everybody, I got it working by reading the output parameter after
I had closed my datareader.
*** Sent via Developersdex http://www.developersdex.com ***
Sep 12 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by vivienne.netherwood | last post: by
reply views Thread by Chris Fink | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.