RE: output parameters not working in if return resultset
- From: stcheng@xxxxxxxxxxxxxxxxxxxx (Steven Cheng[MSFT])
- Date: Fri, 04 Nov 2005 00:59:31 GMT
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?= <Acharts@xxxxxxxxxxxxxxxx>
| References: <5E2351E9-40A1-4345-91D6-F0CED439013A@xxxxxxxxxxxxx>
<C36dCwF4FHA.1144@xxxxxxxxxxxxxxxxxxxxx>
| 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@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: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?= <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;
| > |
| > |
| > |
| > |
| >
| >
|
.
- References:
- RE: output parameters not working in if return resultset
- From: Steven Cheng[MSFT]
- RE: output parameters not working in if return resultset
- From: Charts
- RE: output parameters not working in if return resultset
- Prev by Date: Re: Checkbox value in datagrid does not reflect appearance
- Next by Date: Customizing pager/header/footer
- Previous by thread: RE: output parameters not working in if return resultset
- Next by thread: datagrid : change the string formatting expression
- Index(es):
Relevant Pages
|
