Output parameters not returned from SP

[Janaka]

Hi this is kind of an ASP.NET/ADO.NET question. I've got a stored procedure
on SQL Server that returns a results set. It also sets 3 output parameters
in a seperate Select statement. When checking this on the database it
returns all the results and output parameters. Now when I set up my
SqlCommand objects parameters I specify 2 input and 3 output parameters. It
executes and returns a SqlDataReader which works fine. However when I go to
read the parameter values they are always null? Does anyone know why you
cannot get output parameters with a results set?

Thanks, J

[Teemu Keiski]

Hi,

did you close the DataReader before reading the parameters? You need to
close it first.

--
Teemu Keiski
MCP, Microsoft MVP (ASP.NET), AspInsiders member
ASP.NET Forum Moderator, AspAlliance Columnist
http://blogs.aspadvice.com/joteke
"Janaka" <ja****@magicalia.com> wrote in message
news:eJ**************@tk2msftngp13.phx.gbl...

Hi this is kind of an ASP.NET/ADO.NET question. I've got a stored procedure on SQL Server that returns a results set. It also sets 3 output parameters in a seperate Select statement. When checking this on the database it
returns all the results and output parameters. Now when I set up my
SqlCommand objects parameters I specify 2 input and 3 output parameters. It executes and returns a SqlDataReader which works fine. However when I go to read the parameter values they are always null? Does anyone know why you
cannot get output parameters with a results set?

Thanks, J

[Jesper Stocholm]

"Janaka" <ja****@magicalia.com> wrote in
news:eJ**************@tk2msftngp13.phx.gbl:

Hi this is kind of an ASP.NET/ADO.NET question. I've got a stored
procedure on SQL Server that returns a results set. It also sets 3
output parameters in a seperate Select statement. When checking this
on the database it returns all the results and output parameters. Now
when I set up my SqlCommand objects parameters I specify 2 input and 3
output parameters. It executes and returns a SqlDataReader which
works fine. However when I go to read the parameter values they are
always null? Does anyone know why you cannot get output parameters
with a results set?

It is my understanding, that you cannot access the output parametres from a
stored procedure, if you return a datareader. You must return either
nothing or a dataset to access these properties.

(please correct me, if I am wrong)

Maybe you should try to post your question in
microsoft.public.dotnet.adonet

--
Jesper Stocholm http://stocholm.dk

Programmer's code comment:
//It probably makes more sense when you're stoned.

[Janaka]

Thanks I didn't realise the reader's got to be closed first. Jesper, that
explains why creating a DataSet also works cause the connection is closed.
"Teemu Keiski" <jo****@aspalliance.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

Hi,

did you close the DataReader before reading the parameters? You need to
close it first.

--
Teemu Keiski
MCP, Microsoft MVP (ASP.NET), AspInsiders member
ASP.NET Forum Moderator, AspAlliance Columnist
http://blogs.aspadvice.com/joteke
"Janaka" <ja****@magicalia.com> wrote in message
news:eJ**************@tk2msftngp13.phx.gbl...

Hi this is kind of an ASP.NET/ADO.NET question. I've got a stored procedure

on SQL Server that returns a results set. It also sets 3 output

parameters

in a seperate Select statement. When checking this on the database it
returns all the results and output parameters. Now when I set up my
SqlCommand objects parameters I specify 2 input and 3 output parameters.

It

executes and returns a SqlDataReader which works fine. However when I go to

read the parameter values they are always null? Does anyone know why

you cannot get output parameters with a results set?

Thanks, J

[Jesper Stocholm]

"Janaka" <ja****@magicalia.com> wrote in
news:eB**************@TK2MSFTNGP12.phx.gbl:

Thanks I didn't realise the reader's got to be closed first. Jesper,
that explains why creating a DataSet also works cause the connection
is closed.

I got this from the SDK documentation on the SqlDataReader object

"..."
The Close method fills in the values for output parameters, return values
and RecordsAffected, increasing the amount of time it takes to close a
SqlDataReader that was used to process a large or complicated query.
"..."

:o)

--
Jesper Stocholm http://stocholm.dk

Programmer's code comment:
//It probably makes more sense when you're stoned.