How do you get output parameters?

[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

 

[William Ryan]

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

 

[William \(Bill\) Vaughn]

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.
__________________________________

 

[Steve - DND]

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

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

 

[William \(Bill\) Vaughn]

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.
__________________________________