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.

 

[Jesper Stocholm]

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

 

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

 

[Jesper Stocholm]

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

)

 

[ratnakarg]

SQLDataReader and Output Parameters

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

Here's the trick pal......the output parameters are returned within a seperate resultset, so .....after doing the datareader = command.executereader() statement...

do datareader.nextresult() and then suck the values of your output parameters into the variables.......it works!!!!

Ratnakar Garikipati