How do you get output parameters?

  • Thread starter Thread starter Steve - DND
  • Start date Start date
S

Steve - DND

This is driving me insane, I have looked at examples left and right, and no
matter what, I can never get values for my output parameters! I have
confirmed that the output parameter is being set properly by SQL Server.
Below is the code I have. Can anyone tell me where I might be going wrong? I
have tried it using ExecuteReader, and ExecuteNonQuery. No matter what, both
of the parameters are always empty strings when I'm done.

SqlCommand cmd = new SqlCommand();
cmd.Connection = new SqlConnection(m_ConnString); //m_cn;
cmd.Parameters.Add("@Username", "");
cmd.Parameters["@Username"].Direction = ParameterDirection.Output;
cmd.Parameters["@Username"].DbType = SqlDbType.VarChar;
cmd.Parameters.Add("@Posts", "");
cmd.Parameters["@Posts"].Direction = ParameterDirection.Output;
cmd.Parameters["@Posts"].DbType = SqlDbType.VarChar;
if (cmd.Connection.State != ConnectionState.Open)
cmd.Connection.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = commandText;
output = new Hashtable();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read()) {}
//Close up the connection
dr.Close();
foreach(SqlParameter p in cmd.Parameters) {
//Only get parameters that have been marked as some form of output.
if (p.Direction != ParameterDirection.Input)
output.Add(p.ParameterName, p.Value);
}

Thanks,
Steve
 
How did you verify the Values are set? Can you post the
proc b/c everything else looks ok down there. I'm
assuming the HashTable key is returning the name, it's
just the value is blank right?

A while ago, I had a proc that was setting everything
correctly, but b/c of my Where clause, nothing got
returned and thereby the params never got set.

Try changing the value from "" that you are passing in to
a literal of some sort, Like "Param1" and "Param2" and see
what you get back. If they are returned to you, it's
probably the query.

For instance, if I had a Proc GetData with @CustomerID and
@CustomerName as output params and I sent had this query

SELECT @CustomerID = CustomerID, @CustomerName =
CustomerName where (1 = 2), then I'd never get a value
back. If I sent in values, that'd be what I got back b/c
they never got set even though syntactically my set
statement is correct. You can verify this by passing in
Literals.

Hopefully this helps.

Bill
 
Perhaps your SP is returning more than one resultset. Try adding SET NOCOUNT
ON in the SP or step to the next resultset.
I wrote an MSDN article on SPs and handling parameters--it might help too,
but it looks like you're doing everything right.
http://www.betav.com/msdn_magazine.htm

hth

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
William (Bill) Vaughn said:
Perhaps your SP is returning more than one resultset. Try adding SET NOCOUNT
ON in the SP or step to the next resultset.
I wrote an MSDN article on SPs and handling parameters--it might help too,
but it looks like you're doing everything right.
http://www.betav.com/msdn_magazine.htm

hth
Click to expand...

Thanks for the help guys. It turns out it all revolved around the parameter
direction. I had specified them as Output, once I changed them to
InputOutput it worked perfectly. I can now get output parameters through
ExecuteReader, ExecuteNonQuery, ExecuteScalar, etc... Now that I think about
it, I recall this being the same way in regular ADO. Is this some kind of
bug? If not, then what purpose does specifying a parameter with an Output
direction serve if you can't retrieve it?

Steve
 
You shouldn't have to set IO as the direction. I have never had to do that
to get it to work. I suspect something else is wrong.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top