output parameters not working in if return resultset

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

[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! [url]www.microsoft.com/security[/url]
(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@newsgroup.nospam>
| 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@microsoft.com>
| 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.datagridc ontrol
| 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.datagridc ontrol:5898
| X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet.datagridc ontrol
|
| 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;
|
|
|
|

[Charts]

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! [url]www.microsoft.com/security[/url]
> (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@newsgroup.nospam>
> | 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@microsoft.com>
> | 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.datagridc ontrol
> | 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.datagridc ontrol:5898
> | X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet.datagridc ontrol
> |
> | 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;
> |
> |
> |
> |
>
>

[Steven Cheng[MSFT]]

You're welcome Charts,

Good luck!

Steven Cheng
Microsoft Online Support

Get Secure! [url]www.microsoft.com/security[/url]
(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?= <Acharts@newsgroup.nospam>
| References: <5E2351E9-40A1-4345-91D6-F0CED439013A@microsoft.com>
<C36dCwF4FHA.1144@TK2MSFTNGXA01.phx.gbl>
| Subject: RE: output parameters not working in if return resultset
| Date: Thu, 3 Nov 2005 06:40:03 -0800
| Lines: 153
| Message-ID: <7D70814D-F4FD-4031-9D42-6C450F1B62C4@microsoft.com>
| 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.datagridc ontrol
| 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.datagridc ontrol:5904
| X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet.datagridc ontrol
|
| 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! [url]www.microsoft.com/security[/url]
| > (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@newsgroup.nospam>
| > | 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@microsoft.com>
| > | 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.datagridc ontrol
| > | 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.datagridc ontrol:5898
| > | X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet.datagridc ontrol
| > |
| > | 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;
| > |
| > |
| > |
| > |
| >
| >
|