output parameters not working in if return resultset

Discussion in 'ASP .Net Datagrid Control' started by Charts, Nov 2, 2005.

  1. Charts

    Charts Guest

    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, Nov 2, 2005
    #1
    1. Advertising

  2. 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?= <>
    | Subject: output parameters not working in if return resultset
    | Date: Wed, 2 Nov 2005 13:21:04 -0800
    | Lines: 51
    | Message-ID: <>
    | 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;
    |
    |
    |
    |
    Steven Cheng[MSFT], Nov 3, 2005
    #2
    1. Advertising

  3. Charts

    Charts Guest

    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?= <>
    > | Subject: output parameters not working in if return resultset
    > | Date: Wed, 2 Nov 2005 13:21:04 -0800
    > | Lines: 51
    > | Message-ID: <>
    > | 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;
    > |
    > |
    > |
    > |
    >
    >
    Charts, Nov 3, 2005
    #3
  4. 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?= <>
    | References: <>
    <>
    | Subject: RE: output parameters not working in if return resultset
    | Date: Thu, 3 Nov 2005 06:40:03 -0800
    | Lines: 153
    | Message-ID: <>
    | 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?= <>
    | > | Subject: output parameters not working in if return resultset
    | > | Date: Wed, 2 Nov 2005 13:21:04 -0800
    | > | Lines: 51
    | > | Message-ID: <>
    | > | 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;
    | > |
    | > |
    | > |
    | > |
    | >
    | >
    |
    Steven Cheng[MSFT], Nov 4, 2005
    #4
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Priyanka AGARWAL
    Replies:
    9
    Views:
    9,944
    Gordon Beaton
    May 25, 2004
  2. Greenhorn
    Replies:
    15
    Views:
    795
    Keith Thompson
    Mar 6, 2005
  3. Mike D
    Replies:
    2
    Views:
    322
    Mike D
    Oct 27, 2008
  4. david.karr
    Replies:
    17
    Views:
    1,129
    Arne Vajhøj
    Aug 23, 2009
  5. Sushmita
    Replies:
    1
    Views:
    128
    Bob Barrows
    Sep 25, 2003
Loading...

Share This Page