SqlCommand.ExecuteReader does not populate Output Parmeters?

I have a stored procedure that returns a recordset as well as an output parameter. When I use ExecuteReader and then
try to get the value of the output parameter I get an error saying "Object reference not set to an instance of an
object.". Is it not possible to get a datareader and output parameters at the same time?


--Buddy

Cor
Fri Sep 02 03:14:17 CDT 2005

Buddy,

>I have a stored procedure that returns a recordset as well as an output
>parameter. When I use ExecuteReader and then try to get the value of the
>output parameter I get an error saying "Object reference not set to an
>instance of an object.". Is it not possible to get a datareader and output
>parameters at the same time?

Can you explain this a little bit more, in my opinion does the SQL
transaction code not know a recordset (maybe a resultset). However in my
opinion is that not important for the datareader. More important is how the
SQL transaction code looks like (it it is SP or dynamic is not so important
for this).

Cor

ErPotenza
Fri Sep 02 04:21:57 CDT 2005

On Thu, 01 Sep 2005 22:06:09 -0500, Buddy Ackerman
<buddy_nospam@buddyackerman.com> wrote:

>I have a stored procedure that returns a recordset as well as an output parameter. When I use ExecuteReader and then
>try to get the value of the output parameter I get an error saying "Object reference not set to an instance of an
>object.". Is it not possible to get a datareader and output parameters at the same time?

You should post your code, anyway this is an example:

Dim cnn = New SqlClient.SqlConnection
cnn.ConnectionString = "..."
cnn.Open()

Dim cmd As New SqlClient.SqlCommand
With cmd
.CommandText = "StoredProcedureName"
.CommandType = CommandType.StoredProcedure
.Connection = cnn
End With

Dim par As New SqlClient.SqlParameter("@OutParm", _
SqlDbType.Int)
par.Direction = ParameterDirection.Output
cmd.Parameters.Add(par)

Dim dr As SqlClient.SqlDataReader
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
dr.Close()
Dim outParm As Integer = par.Value()

According to MSDN, the DataReader must be closed before getting the
returned value.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlparameterclassdirectiontopic.asp

W
Fri Sep 02 09:52:07 CDT 2005

Yes, you can use both, but you need to iterate through the datareader first
(i.e. while(dr.Read()){ } ) . Check out www.betav.com - > Articles ->
MSDN, Retrieving the Gozoutas - it will explain it in detail. My guess is
you're trying to retrieve the value up front which ain't gonna work
unfortunately.

HTH,

Bill
"Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message
news:%23lRgFt2rFHA.528@TK2MSFTNGP09.phx.gbl...
>I have a stored procedure that returns a recordset as well as an output
>parameter. When I use ExecuteReader and then try to get the value of the
>output parameter I get an error saying "Object reference not set to an
>instance of an object.". Is it not possible to get a datareader and output
>parameters at the same time?
>
>
> --Buddy

Buddy
Fri Sep 02 10:52:04 CDT 2005

Yeah, of course I need to value before I loop through the datareader. I just made the output parameter into a recorset
with one value and I just read it then use the NextResult method to get the result set that I need to loop through.

Thanks.


--Buddy


W.G. Ryan MVP wrote:
> Yes, you can use both, but you need to iterate through the datareader first
> (i.e. while(dr.Read()){ } ) . Check out www.betav.com - > Articles ->
> MSDN, Retrieving the Gozoutas - it will explain it in detail. My guess is
> you're trying to retrieve the value up front which ain't gonna work
> unfortunately.
>
> HTH,
>
> Bill
> "Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message
> news:%23lRgFt2rFHA.528@TK2MSFTNGP09.phx.gbl...
>
>>I have a stored procedure that returns a recordset as well as an output
>>parameter. When I use ExecuteReader and then try to get the value of the
>>output parameter I get an error saying "Object reference not set to an
>>instance of an object.". Is it not possible to get a datareader and output
>>parameters at the same time?
>>
>>
>>--Buddy
>
>
>

William
Thu Sep 01 23:05:30 CDT 2005

Not really. You need to fully fetch the rowset first, close the DataReader
and then fetch the OUTPUT parameters.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

"Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message
news:%23lRgFt2rFHA.528@TK2MSFTNGP09.phx.gbl...
>I have a stored procedure that returns a recordset as well as an output
>parameter. When I use ExecuteReader and then try to get the value of the
>output parameter I get an error saying "Object reference not set to an
>instance of an object.". Is it not possible to get a datareader and output
>parameters at the same time?
>
>
> --Buddy

William
Fri Sep 02 23:02:14 CDT 2005

This is a common mistake. The OUTPUT parameters don't arrive until all of
the rows have been returned.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

"Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message
news:uWxxEZ9rFHA.3060@TK2MSFTNGP09.phx.gbl...
> Yeah, of course I need to value before I loop through the datareader. I
> just made the output parameter into a recorset with one value and I just
> read it then use the NextResult method to get the result set that I need
> to loop through.
>
> Thanks.
>
>
> --Buddy
>
>
> W.G. Ryan MVP wrote:
>> Yes, you can use both, but you need to iterate through the datareader
>> first (i.e. while(dr.Read()){ } ) . Check out www.betav.com - >
>> Articles -> MSDN, Retrieving the Gozoutas - it will explain it in detail.
>> My guess is you're trying to retrieve the value up front which ain't
>> gonna work unfortunately.
>>
>> HTH,
>>
>> Bill
>> "Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message
>> news:%23lRgFt2rFHA.528@TK2MSFTNGP09.phx.gbl...
>>
>>>I have a stored procedure that returns a recordset as well as an output
>>>parameter. When I use ExecuteReader and then try to get the value of the
>>>output parameter I get an error saying "Object reference not set to an
>>>instance of an object.". Is it not possible to get a datareader and
>>>output parameters at the same time?
>>>
>>>
>>>--Buddy
>>
>>

Buddy
Sat Sep 03 12:51:13 CDT 2005

Is MS aware of how stupid that is? There is no reason that those parameters can't be available immediately upon execution.



William (Bill) Vaughn wrote:
> Not really. You need to fully fetch the rowset first, close the DataReader
> and then fetch the OUTPUT parameters.
>

William
Sat Sep 03 15:42:12 CDT 2005

Ah, SQL Server is one of the fastest (if not the fastest), most respected
and most sophisticated DBMS systems on the planet. This behavior (as stupid
as you might think) has been in place since the first implementation of
stored procedure OUTPUT parameters. Just because it does not work the way
you want it does not make it stupid. The problem is that setting an OUTPUT
parameter to a value like @@ROWCOUNT is meaningless. Consider that SQL
Server does not know how many rows qualify for the most recent query until
all the rows are fetched. Since SQL Server is designed to work with hundreds
to thousands of users a second, the data is constantly in flux. As rows are
added, changed, removed and moved around in the DBMS, the engine won't
(can't) know how many rows will eventually be sent to the client until it
has completed the process of retrieving and transmitting the rows to the
client. SQL Server does not send any rows to the client until your
application actually asks for the rows. This means your application must
retrieve each and every row fetched from the server before SQL Server can
compute the @@ROWCOUNT. That's one reason why OUTPUT parameters are sent
last.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

"Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message
news:O$ceUALsFHA.2072@TK2MSFTNGP14.phx.gbl...
> Is MS aware of how stupid that is? There is no reason that those
> parameters can't be available immediately upon execution.
>
>
>
> William (Bill) Vaughn wrote:
>> Not really. You need to fully fetch the rowset first, close the
>> DataReader and then fetch the OUTPUT parameters.
>>

Buddy
Mon Sep 05 12:02:25 CDT 2005

It is stupid and you don't understand the issue. This has nothing do with the return of the @@ROWCOUNT before teh rcord
set has completed.

sample proc:


create proc test
@numrows int output
AS
SET NOCOUNT ON

select identity(1,1) rownum,* into #tmp from mytable

SET @numrows = @@rowcount


select * from #tmp
where rownum between 11 and 20




Typical of a paged return. The issue here has nothing to do with SQL server the issue is that the DataReader interface
is not reading the output parameter until after the recordset has been paged through. No reason for it. Stupid.






William (Bill) Vaughn wrote:
> Ah, SQL Server is one of the fastest (if not the fastest), most respected
> and most sophisticated DBMS systems on the planet. This behavior (as stupid
> as you might think) has been in place since the first implementation of
> stored procedure OUTPUT parameters. Just because it does not work the way
> you want it does not make it stupid. The problem is that setting an OUTPUT
> parameter to a value like @@ROWCOUNT is meaningless. Consider that SQL
> Server does not know how many rows qualify for the most recent query until
> all the rows are fetched. Since SQL Server is designed to work with hundreds
> to thousands of users a second, the data is constantly in flux. As rows are
> added, changed, removed and moved around in the DBMS, the engine won't
> (can't) know how many rows will eventually be sent to the client until it
> has completed the process of retrieving and transmitting the rows to the
> client. SQL Server does not send any rows to the client until your
> application actually asks for the rows. This means your application must
> retrieve each and every row fetched from the server before SQL Server can
> compute the @@ROWCOUNT. That's one reason why OUTPUT parameters are sent
> last.
>
>

Pablo
Thu Sep 08 21:57:19 CDT 2005

The fact that output parameters are exposed at the end is actually
completely unrelated to the DataReader interface. It has to do with how SQL
Server executes statements and how that information flows between the client
and the server.

Without going into details on why the server works like this, you can verify
that output parameters really come at the end of the results at the network
protocol level. Simply execute a batch like this:

SELECT * FROM <some table>
SET @output_param = 'output parameter marker'

if you use network monitor to look at the network traffic while executing
the statement you'll see the text that you put in the output parameter
effectively comming last, after all the rows returned by the previous SELECT
statement. So we simply don't have the information before we finished
consuming the reader :)

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.


"Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message
news:elntYujsFHA.3732@TK2MSFTNGP11.phx.gbl...
> It is stupid and you don't understand the issue. This has nothing do with
> the return of the @@ROWCOUNT before teh rcord set has completed.
>
> sample proc:
>
>
> create proc test
> @numrows int output
> AS
> SET NOCOUNT ON
>
> select identity(1,1) rownum,* into #tmp from mytable
>
> SET @numrows = @@rowcount
>
>
> select * from #tmp
> where rownum between 11 and 20
>
>
>
>
> Typical of a paged return. The issue here has nothing to do with SQL
> server the issue is that the DataReader interface is not reading the
> output parameter until after the recordset has been paged through. No
> reason for it. Stupid.
>
>
>
>
>
>
> William (Bill) Vaughn wrote:
>> Ah, SQL Server is one of the fastest (if not the fastest), most respected
>> and most sophisticated DBMS systems on the planet. This behavior (as
>> stupid as you might think) has been in place since the first
>> implementation of stored procedure OUTPUT parameters. Just because it
>> does not work the way you want it does not make it stupid. The problem is
>> that setting an OUTPUT parameter to a value like @@ROWCOUNT is
>> meaningless. Consider that SQL Server does not know how many rows qualify
>> for the most recent query until all the rows are fetched. Since SQL
>> Server is designed to work with hundreds to thousands of users a second,
>> the data is constantly in flux. As rows are added, changed, removed and
>> moved around in the DBMS, the engine won't (can't) know how many rows
>> will eventually be sent to the client until it has completed the process
>> of retrieving and transmitting the rows to the client. SQL Server does
>> not send any rows to the client until your application actually asks for
>> the rows. This means your application must retrieve each and every row
>> fetched from the server before SQL Server can compute the @@ROWCOUNT.
>> That's one reason why OUTPUT parameters are sent last.
>>

William
Fri Sep 09 11:14:03 CDT 2005

I told him as much. He still thinks it's stupid.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

"Pablo Castro [MS]" <pablocas@online.microsoft.com> wrote in message
news:OUaG1oOtFHA.2212@TK2MSFTNGP15.phx.gbl...
> The fact that output parameters are exposed at the end is actually
> completely unrelated to the DataReader interface. It has to do with how
> SQL Server executes statements and how that information flows between the
> client and the server.
>
> Without going into details on why the server works like this, you can
> verify that output parameters really come at the end of the results at the
> network protocol level. Simply execute a batch like this:
>
> SELECT * FROM <some table>
> SET @output_param = 'output parameter marker'
>
> if you use network monitor to look at the network traffic while executing
> the statement you'll see the text that you put in the output parameter
> effectively comming last, after all the rows returned by the previous
> SELECT statement. So we simply don't have the information before we
> finished consuming the reader :)
>
> --
> Pablo Castro
> Program Manager - ADO.NET Team
> Microsoft Corp.
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
> "Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message
> news:elntYujsFHA.3732@TK2MSFTNGP11.phx.gbl...
>> It is stupid and you don't understand the issue. This has nothing do
>> with the return of the @@ROWCOUNT before teh rcord set has completed.
>>
>> sample proc:
>>
>>
>> create proc test
>> @numrows int output
>> AS
>> SET NOCOUNT ON
>>
>> select identity(1,1) rownum,* into #tmp from mytable
>>
>> SET @numrows = @@rowcount
>>
>>
>> select * from #tmp
>> where rownum between 11 and 20
>>
>>
>>
>>
>> Typical of a paged return. The issue here has nothing to do with SQL
>> server the issue is that the DataReader interface is not reading the
>> output parameter until after the recordset has been paged through. No
>> reason for it. Stupid.
>>
>>
>>
>>
>>
>>
>> William (Bill) Vaughn wrote:
>>> Ah, SQL Server is one of the fastest (if not the fastest), most
>>> respected and most sophisticated DBMS systems on the planet. This
>>> behavior (as stupid as you might think) has been in place since the
>>> first implementation of stored procedure OUTPUT parameters. Just because
>>> it does not work the way you want it does not make it stupid. The
>>> problem is that setting an OUTPUT parameter to a value like @@ROWCOUNT
>>> is meaningless. Consider that SQL Server does not know how many rows
>>> qualify for the most recent query until all the rows are fetched. Since
>>> SQL Server is designed to work with hundreds to thousands of users a
>>> second, the data is constantly in flux. As rows are added, changed,
>>> removed and moved around in the DBMS, the engine won't (can't) know how
>>> many rows will eventually be sent to the client until it has completed
>>> the process of retrieving and transmitting the rows to the client. SQL
>>> Server does not send any rows to the client until your application
>>> actually asks for the rows. This means your application must retrieve
>>> each and every row fetched from the server before SQL Server can compute
>>> the @@ROWCOUNT. That's one reason why OUTPUT parameters are sent last.
>>>
>