Output parameters not returned from SP

  • Thread starter Thread starter Janaka
  • Start date Start date
J

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
 
Hi,

did you close the DataReader before reading the parameters? You need to
close it first.
 
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?
Click to expand...

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
 
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.
 
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.
Click to expand...

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)
 
Back
Top