By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,731 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,731 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
"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.