RE: output parameters not working in if return resultset

Hi Charts,

Welcome to ASPNET newsgroup.
Regarding on the Calling SQLSERVER Stored Prrocedure with output and
return value question, here are some of my understanding and suggestions:

For output parameter, we can just create the parameter with the correct
name and set the Direction to "ParameterDirection.Output"

for return value, we should always create the Parameter with the name of
"ReturnValue", and set the Direction to
"ParameterDirection.ReturnValue"

Also, for SqlCommand.ExecuteReader call, we need to close the Reader before
we try accessing the OutputValue or ReturnValue (otherwise the parameter
remain empty....)

Here is a simple codesnippet demostrate the things I mentioned above:

=====================================
SqlConnection conn = new SqlConnection("Data Source=localhost;Initial
Catalog=Northwind;Integrated Security=True");
conn.Open();


SqlCommand comm = new SqlCommand("sampleprocedure", conn);
comm.CommandType = CommandType.StoredProcedure;

SqlParameter param = comm.Parameters.Add("@EmployeeIDParm",
SqlDbType.Int);
param.Value = 1;

param = comm.Parameters.Add("@MaxQuantity", SqlDbType.Int);
param.Value = 1;
param.Direction = ParameterDirection.Output;

param = comm.Parameters.Add("@ReturnValue", SqlDbType.Int);
param.Direction = ParameterDirection.ReturnValue;

SqlDataReader sqlrdr = comm.ExecuteReader();
sqlrdr.Close();


Response.Write("<br>@MaxQuantity: " +
comm.Parameters["@MaxQuantity"].Value);
Response.Write("<br>@ReturnValue: " +
comm.Parameters["@ReturnValue"].Value);

=====================================

Hope helps. Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

--------------------
| Thread-Topic: output parameters not working in if return resultset
| thread-index: AcXf81TcF9YVjQyhRpGDLzTWtgz9tg==
| X-WBNR-Posting-Host: 24.173.128.186
| From: =?Utf-8?B?Q2hhcnRz?= <Acharts@xxxxxxxxxxxxxxxx>
| Subject: output parameters not working in if return resultset
| Date: Wed, 2 Nov 2005 13:21:04 -0800
| Lines: 51
| Message-ID: <5E2351E9-40A1-4345-91D6-F0CED439013A@xxxxxxxxxxxxx>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.dotnet.framework.aspnet.datagridcontrol
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl
microsoft.public.dotnet.framework.aspnet.datagridcontrol:5898
| X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet.datagridcontrol
|
| I am calling a stored procedure in SQL Server to return resultset to fill
a
| datagrid in ASP.NET (using C#). I also want to return error code in ether
| return value or output parameters for ASP.NET calling program. I execute
the
| stored procedure and tried to get hold of either return value or output
| parameters. However I got the error that the object reference is not set
for
| the parameter which hold return value or output parameters. I enclosed
| sample SQL Server stored procedure and ASP.NET C# code. I used output
| parameters as an example here. Only code difference between return value
and
| output parameters is that I need to set Direction as ReturnValue instead
of
| Output.
|
| I also found that if remove select statement in stored procedure, i.e.,
if I
| donâ??t want to return resultset, both return value and output parameters
will
| work for the same code. Is that mean that I cannot have any return value
or
| output parameters if I want to return resultset? If so, is there way
that I
| can get some error code back to my calling program.
| Thanks,
| Charts
|
| CREATE PROCEDURE spAuthors
| @contract bit,
| @errorcode int OUT
| AS
| SET NOCOUNT ON
| SELECT [au_id], [au_lname], [au_fname], [phone], [address], [city],
[state],
| [zip], [contract] FROM [pubs].[dbo].[authors]
| where contract=@contract
| SET NOCOUNT OFF
| set @errorcode=1
| return 0
| GO
|
|
| conPubs = new SqlConnection( @"Server=myserver;Integrated
Security=SSPI;
| Database=Pubs" );
| cmdSelect = new SqlCommand();
| cmdSelect.CommandText = "[dbo].[spAuthors]";
| cmdSelect.CommandType = System.Data.CommandType.StoredProcedure;
| cmdSelect.Connection = conPubs;
| cmdSelect.Parameters.Add("@Contract", SqlDbType.Int).Value = Contract;
| retValParam = new SqlParameter("@errorcode", SqlDbType.Int);
| retValParam.Direction = ParameterDirection.Output;
| cmdSelect.Parameters.Add(retValParam);
|
| conPubs.Open();
| dtrAuthors = cmdSelect.ExecuteReader();
|
| intretValParam=(int)retValParam.Value;
|
|
|
|

.