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
>-----Original Message-----
>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
>
>
>.
>

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" <(E-Mail Removed)> wrote in message
news:OAUWPG$(E-Mail Removed)...
> 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 (Bill) Vaughn" <(E-Mail Removed)> wrote in message
news:uJB8Ou$(E-Mail Removed)...
> 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

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

"Steve - DND" <steve!@!digitalnothing.com> wrote in message
news:(E-Mail Removed)...
>
> "William (Bill) Vaughn" <(E-Mail Removed)> wrote in message
> news:uJB8Ou$(E-Mail Removed)...
> > 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
>
>