output parameters not working in if return resultset

C

Charts

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;
 
S

Steven Cheng[MSFT]

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?= <[email protected]>
| Subject: output parameters not working in if return resultset
| Date: Wed, 2 Nov 2005 13:21:04 -0800
| Lines: 51
| Message-ID: <[email protected]>
| 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;
|
|
|
|
 
C

Charts

That works! Thanks so much! Charts

Steven Cheng said:
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?= <[email protected]>
| Subject: output parameters not working in if return resultset
| Date: Wed, 2 Nov 2005 13:21:04 -0800
| Lines: 51
| Message-ID: <[email protected]>
| 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;
|
|
|
|
Click to expand...
 
S

Steven Cheng[MSFT]

You're welcome Charts,

Good luck!

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: AcXghHn0SgrvcU5bTCiBGYCU26YYlQ==
| X-WBNR-Posting-Host: 24.173.128.186
| From: =?Utf-8?B?Q2hhcnRz?= <[email protected]>
| References: <[email protected]>
<[email protected]>
| Subject: RE: output parameters not working in if return resultset
| Date: Thu, 3 Nov 2005 06:40:03 -0800
| Lines: 153
| Message-ID: <[email protected]>
| 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:5904
| X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet.datagridcontrol
|
| That works! Thanks so much! Charts
|
| "Steven Cheng[MSFT]" wrote:
|
| > 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?= <[email protected]>
| > | Subject: output parameters not working in if return resultset
| > | Date: Wed, 2 Nov 2005 13:21:04 -0800
| > | Lines: 51
| > | Message-ID: <[email protected]>
| > | 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;
| > |
| > |
| > |
| > |
| >
| >
|
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Output parameters 1
DB API question - where is a stored procedure's return value? 0
sqldatasource using a stored procedure with OUTPUT parameters only 3
sqldatasource using a stored procedure with OUTPUT parameters only 0
output parameters 3
Retrieving Output Parameters and Recordset 1
Gridview Stored Procedure output paramters or return value 2
parameters 0

Members online

No members online now.
Total: 30 (members: 2, guests: 28)
Robots: 203

Forum statistics

Threads
473,764
Messages
2,569,567
Members
45,041
Latest member
RomeoFarnh

Latest Threads

Top