RE: output parameters not working in if return resultset
- From: Charts <Acharts@xxxxxxxxxxxxxxxx>
- Date: Thu, 3 Nov 2005 06:40:03 -0800
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;
> |
> |
> |
> |
>
>
.
- Follow-Ups:
- RE: output parameters not working in if return resultset
- From: Steven Cheng[MSFT]
- RE: output parameters not working in if return resultset
- References:
- RE: output parameters not working in if return resultset
- From: Steven Cheng[MSFT]
- RE: output parameters not working in if return resultset
- Prev by Date: DataGrid Edit TextBox
- Next by Date: RE: Long list of items in my datagrid, editing is a pain!!
- Previous by thread: RE: output parameters not working in if return resultset
- Next by thread: RE: output parameters not working in if return resultset
- Index(es):
Relevant Pages
|
