Output parameters not returned from SP

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

re: Output parameters not returned from SP

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" <janaka@magicalia.com> wrote in message
news:eJzBbrlPEHA.2716@tk2msftngp13.phx.gbl...[color=blue]
> Hi this is kind of an ASP.NET/ADO.NET question. I've got a stored[/color]
procedure[color=blue]
> on SQL Server that returns a results set. It also sets 3 output[/color]
parameters[color=blue]
> 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.[/color]
It[color=blue]
> executes and returns a SqlDataReader which works fine. However when I go[/color]
to[color=blue]
> read the parameter values they are always null? Does anyone know why you
> cannot get output parameters with a results set?
>
> Thanks, J
>
>[/color]

re: Output parameters not returned from SP

"Janaka" <janaka@magicalia.com> wrote in
news:eJzBbrlPEHA.2716@tk2msftngp13.phx.gbl:
[color=blue]
> 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?[/color]

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.

re: Output parameters not returned from SP

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" <joteke@aspalliance.com> wrote in message
news:%23ndA2ulPEHA.1276@TK2MSFTNGP11.phx.gbl...[color=blue]
> 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" <janaka@magicalia.com> wrote in message
> news:eJzBbrlPEHA.2716@tk2msftngp13.phx.gbl...[color=green]
> > Hi this is kind of an ASP.NET/ADO.NET question. I've got a stored[/color]
> procedure[color=green]
> > on SQL Server that returns a results set. It also sets 3 output[/color]
> parameters[color=green]
> > 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.[/color]
> It[color=green]
> > executes and returns a SqlDataReader which works fine. However when I[/color][/color]
go[color=blue]
> to[color=green]
> > read the parameter values they are always null? Does anyone know why[/color][/color]
you[color=blue][color=green]
> > cannot get output parameters with a results set?
> >
> > Thanks, J
> >
> >[/color]
>
>[/color]

re: Output parameters not returned from SP

"Janaka" <janaka@magicalia.com> wrote in
news:eBAE44lPEHA.2996@TK2MSFTNGP12.phx.gbl:
[color=blue]
> 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.[/color]

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.