469,646 Members | 1,385 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,646 developers. It's quick & easy.

Reading both result set and output parameter of SP in .Net Framework......

I have a stored procedure which returns records and output
parameters(SQL2K). How to call this stored procedure using ADO.Net so that
I can use both results in my application. I have .Net Framework 1.1.
when I use ExecuteReader() of Command object I cannot read the output
parametrs of the stored procedure in code.
Please advice.
Hewit


Nov 19 '05 #1
3 1884
in sqlserver, the output parameter values are returned after all result
sets. with a sql datareader (a forward only reader), this means you have to
read though all the rows (and result sets - MoreResults), or close the
reader which does the same. after this step, you can read the output
parameters (be sure to declare direction to include output).

-- bruce (sqlwork.com)
"Hewit" <He********@hotmail.com> wrote in message
news:e$**************@TK2MSFTNGP09.phx.gbl...
| I have a stored procedure which returns records and output
| parameters(SQL2K). How to call this stored procedure using ADO.Net so
that
| I can use both results in my application. I have .Net Framework 1.1.
| when I use ExecuteReader() of Command object I cannot read the output
| parametrs of the stored procedure in code.
| Please advice.
| Hewit
|
|
|
|
Nov 19 '05 #2
Bruce,
Since I am not finding the close method for datareader, I used Dispose and
Clear methods. But no help.
Hewit
"bruce barker" <no***********@safeco.com> wrote in message
news:uH**************@TK2MSFTNGP15.phx.gbl...
in sqlserver, the output parameter values are returned after all result
sets. with a sql datareader (a forward only reader), this means you have to read though all the rows (and result sets - MoreResults), or close the
reader which does the same. after this step, you can read the output
parameters (be sure to declare direction to include output).

-- bruce (sqlwork.com)
"Hewit" <He********@hotmail.com> wrote in message
news:e$**************@TK2MSFTNGP09.phx.gbl...
| I have a stored procedure which returns records and output
| parameters(SQL2K). How to call this stored procedure using ADO.Net so
that
| I can use both results in my application. I have .Net Framework 1.1.
| when I use ExecuteReader() of Command object I cannot read the output
| parametrs of the stored procedure in code.
| Please advice.
| Hewit
|
|
|
|

Nov 19 '05 #3
"Hewit" <He********@hotmail.com> confessed in news:e2#bUdc$EHA.2788
@TK2MSFTNGP15.phx.gbl:
Bruce,
Since I am not finding the close method for datareader, I used Dispose and Clear methods. But no help.
Hewit
"bruce barker" <no***********@safeco.com> wrote in message
news:uH**************@TK2MSFTNGP15.phx.gbl...
in sqlserver, the output parameter values are returned after all result
sets. with a sql datareader (a forward only reader), this means you have

to
read though all the rows (and result sets - MoreResults), or close the
reader which does the same. after this step, you can read the output
parameters (be sure to declare direction to include output).

-- bruce (sqlwork.com)
"Hewit" <He********@hotmail.com> wrote in message
news:e$**************@TK2MSFTNGP09.phx.gbl...
| I have a stored procedure which returns records and output
| parameters(SQL2K). How to call this stored procedure using ADO.Net so
that
| I can use both results in my application. I have .Net Framework 1.1.
| when I use ExecuteReader() of Command object I cannot read the output
| parametrs of the stored procedure in code.
| Please advice.
| Hewit
|
|
|
|



First, assume you've created appropriate parameter associated with the
command:

SqlParameter pReturn = new System.Data.SqlClient.SqlParameter
("@RETURN_VALUE", System.Data.SqlDbType.Int,
System.Data.ParameterDirection.ReturnValue, false, ... );

myCommand.Parameters.Add(pReturn);

...

Then call the command creating the reader and set it up to close the
connection:

myConnection.Open();
SqlDataReader rdr = myCommand.ExecuteReader
(CommandBehavior.CloseConnection);

Then when finished with the reader, close. It will close the associated
connection:

rdr.Close();

Then the return value parameter of the SPROC are made available by
ADO.NET

int myInt = (int)pReturn.Value;
Give it a try. But I am surprised you couldn't find a Close() method for
the SqlDataReader class?

-- ipgrunt

Nov 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by John | last post: by
9 posts views Thread by EkteGjetost | last post: by
8 posts views Thread by Yusuf INCEKARA | last post: by
3 posts views Thread by tshad | last post: by
7 posts views Thread by ianenis.tiryaki | last post: by
7 posts views Thread by sonet | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.