When I run cmd.ExecuteScalar() or cmd.ExecuteNonQuery() the Output and InputOutput parameters on the command object get updated from the changes in the stored procedure. However the same does not happen for cmd.ExecuteReader(). This is happening on both Sql Server and MySql Connections. Is this behavior by design?
+3
A:
Hey this may help you. clicky...
It appears this can possibly be an issue under certain circumstances.
Blounty
2009-10-16 10:26:06
The answer was in there. The reader has to be read to the end or closed for the parameters to get updated. I moved my evaluation of parameters to the after the reader is done and now it works. Thanks.
My Other Me
2009-10-16 10:33:47
A:
You should be able to retrieve the value via an Output parameter. Take a look at this MS Support article and see if your issue is one of those mentioned: link text
Also, what are you trying to return? If it's just a single value, it would be worth using ExecuteScalar() rather than ExecuteReader().
fat_tony
2009-10-16 10:26:56
+1
A:
The output parameters are only available after you read to the end of the recordset.
For example, in this procedure:
alter procedure db.TestProc(@p int output)
as
select 1
select 1
set @par = 1
The database will only set @par after you've read both recordsets. The database doesn't even execute the second SELECT before you're done reading the first. It is streaming results as you request them.
Andomar
2009-10-16 10:33:30