Need help: Call server side Stored Procedure and get the return va

Discussion in 'ASP .Net' started by =?Utf-8?B?ZGF2aWQ=?=, Oct 19, 2006.

  1. I have trouble with it.

    I have created a sproc in SQL Server, called SearchClass, which returns a
    searched key from a table. I have tested this procedure in Analyzer and it
    works fine. However, in the client side, when I use ExecuteNonQuery as follows
    ---
    Me.SqlConnection1.Open()
    classKey = Me.SqlCommand2.ExecuteNonQuery()
    Me.SqlConnection1.Close()
    --
    it always returns -1 rather than 5 that should be.
    What is the problem?

    Thank you for any help.

    David
    =?Utf-8?B?ZGF2aWQ=?=, Oct 19, 2006
    #1
    1. Advertising

  2. ExecuteNonQuery does not return the return value of a sp, it returns rows
    affected (addedd, deleted, changed). a query always returns -1

    if you want a return values, you need to added a return value parameter:

    SqlCommand cmd = new SqlCommand(spname,myConnection);
    SqlParameter retparam = new SqlParameter("RV",SqlDbType.Int);
    retparam.Direction= ParameterDirection.ReturnValue;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.ExecuteNonQuery();
    int returnValue = (int) retparam.Value;

    -- bruce (sqlwork.com)


    "david" <> wrote in message
    news:...
    >I have trouble with it.
    >
    > I have created a sproc in SQL Server, called SearchClass, which returns a
    > searched key from a table. I have tested this procedure in Analyzer and it
    > works fine. However, in the client side, when I use ExecuteNonQuery as
    > follows
    > ---
    > Me.SqlConnection1.Open()
    > classKey = Me.SqlCommand2.ExecuteNonQuery()
    > Me.SqlConnection1.Close()
    > --
    > it always returns -1 rather than 5 that should be.
    > What is the problem?
    >
    > Thank you for any help.
    >
    > David
    bruce barker \(sqlwork.com\), Oct 19, 2006
    #2
    1. Advertising

  3. Re: Need help: Call server side Stored Procedure and get the retur

    Thank you very much.
    I will try it tomorrow in my office.

    David

    "bruce barker (sqlwork.com)" wrote:

    > ExecuteNonQuery does not return the return value of a sp, it returns rows
    > affected (addedd, deleted, changed). a query always returns -1
    >
    > if you want a return values, you need to added a return value parameter:
    >
    > SqlCommand cmd = new SqlCommand(spname,myConnection);
    > SqlParameter retparam = new SqlParameter("RV",SqlDbType.Int);
    > retparam.Direction= ParameterDirection.ReturnValue;
    > cmd.CommandType = CommandType.StoredProcedure;
    > cmd.ExecuteNonQuery();
    > int returnValue = (int) retparam.Value;
    >
    > -- bruce (sqlwork.com)
    >
    >
    > "david" <> wrote in message
    > news:...
    > >I have trouble with it.
    > >
    > > I have created a sproc in SQL Server, called SearchClass, which returns a
    > > searched key from a table. I have tested this procedure in Analyzer and it
    > > works fine. However, in the client side, when I use ExecuteNonQuery as
    > > follows
    > > ---
    > > Me.SqlConnection1.Open()
    > > classKey = Me.SqlCommand2.ExecuteNonQuery()
    > > Me.SqlConnection1.Close()
    > > --
    > > it always returns -1 rather than 5 that should be.
    > > What is the problem?
    > >
    > > Thank you for any help.
    > >
    > > David

    >
    >
    >
    =?Utf-8?B?ZGF2aWQ=?=, Oct 20, 2006
    #3
  4. Re: Need help: Call server side Stored Procedure and get the retur

    Hi, Bruce:

    It does not work for me. I try either ExecuteNonQuery or ExecuteScalar, I
    got 0 and -1 for ExecuteNonQuery, and 0 and 0 for ExecuteScalar. Now it
    should be 11.

    My Sproc code and VB.Net and output are in the following.

    1. Stored procedure:
    CREATE PROCEDURE SearchClassRecord2
    (
    @ModalityID int,
    @PurposeID int,
    @TechID int,
    @SubtechID int,
    @ContrastID int
    )
    AS
    SET NOCOUNT ON

    DECLARE @SearchedKey AS int

    Select @SearchedKey = MAX(ClassKey)
    FROM ClassDescription
    WHERE ModalityID=@ModalityID and PurposeID=@PurposeID and TechID=TechID and
    SubtechID=@SubtechID and ContrastAgentID=@ContrastID


    RETURN @SearchedKey
    GO

    output:
    Running dbo."SearchClassRecord2" ( @ModalityID = 1, @PurposeID = 1, @TechID
    = 1, @SubtechID = 1, @ContrastID = 1 ).

    No rows affected.
    No more results.
    (0 row(s) returned)
    @RETURN_VALUE = 11
    Finished running dbo."SearchClassRecord2".

    2. VB.NET code:
    Me.SqlCommand2.CommandText = "dbo.[SearchClassRecord2]"
    Me.SqlCommand2.CommandType = System.Data.CommandType.StoredProcedure
    Me.SqlCommand2.Connection = Me.SqlConnection1
    Me.SqlCommand2.Parameters.Add(New
    System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
    System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
    False, CType(0, Byte), CType(0, Byte), "",
    System.Data.DataRowVersion.Current, Nothing))
    Me.SqlCommand2.Parameters.Add(New
    System.Data.SqlClient.SqlParameter("@ModalityID", System.Data.SqlDbType.Int,
    4))
    Me.SqlCommand2.Parameters.Add(New
    System.Data.SqlClient.SqlParameter("@PurposeID", System.Data.SqlDbType.Int,
    4))
    Me.SqlCommand2.Parameters.Add(New
    System.Data.SqlClient.SqlParameter("@TechID", System.Data.SqlDbType.Int, 4))
    Me.SqlCommand2.Parameters.Add(New
    System.Data.SqlClient.SqlParameter("@SubtechID", System.Data.SqlDbType.Int,
    4))
    Me.SqlCommand2.Parameters.Add(New
    System.Data.SqlClient.SqlParameter("@ContrastID", System.Data.SqlDbType.Int,
    4))

    Dim classKey As Integer
    Dim psStatus As Integer

    Me.SqlCommand2.Parameters("@ModalityID").Value =
    ModalityDS1.Tables(0).Rows(ddlModality.SelectedIndex)("ModalityKey")
    'CStr(ddlModality.SelectedValue)
    Me.SqlCommand2.Parameters("@PurposeID").Value =
    PurposeDS1.Tables(0).Rows(ddlPurpose.SelectedIndex)("ID")
    'ddlPurpose.SelectedValue
    Me.SqlCommand2.Parameters("@TechID").Value =
    TechDS1.Tables(0).Rows(ddlTech.SelectedIndex)("ID") 'ddlTech.SelectedValue
    Me.SqlCommand2.Parameters("@SubtechID").Value =
    SubtechDS1.Tables(0).Rows(ddlSubtech.SelectedIndex)("ID")
    'ddlSubtech.SelectedValue
    Me.SqlCommand2.Parameters("@ContrastID").Value =
    ContrastDS1.Tables(0).Rows(ddlContrast.SelectedIndex)("ID")
    'ddlContrast.SelectedValue

    Dim ClassKeyReturn As New
    System.Data.SqlClient.SqlParameter("@RETURN_VALUE", SqlDbType.Int)

    ClassKeyReturn.Direction = ParameterDirection.ReturnValue


    'test
    lblInfo.Text &=
    ModalityDS1.Tables(0).Rows(ddlModality.SelectedIndex)("ModalityKey") & ", " &
    PurposeDS1.Tables(0).Rows(ddlPurpose.SelectedIndex)("ID") & ", " &
    TechDS1.Tables(0).Rows(ddlTech.SelectedIndex)("ID") & ", " &
    SubtechDS1.Tables(0).Rows(ddlSubtech.SelectedIndex)("ID") & ", " &
    ContrastDS1.Tables(0).Rows(ddlContrast.SelectedIndex)("ID")

    Me.SqlConnection1.Open()
    'psStatus = Me.SqlCommand2.ExecuteNonQuery()
    'classKey = CType(ClassKeyReturn.Value, Integer)
    psStatus = CType(Me.SqlCommand2.ExecuteScalar(), Integer)
    classKey = CType(ClassKeyReturn.Value, Integer)
    Me.SqlConnection1.Close()


    lblInfo.Text &= " classKey: " & classKey & " status: " & psStatus

    output: 1, 1, 1, 1, 1 classKey: 0 status: 0


    --------------------------
    "bruce barker (sqlwork.com)" wrote:

    > ExecuteNonQuery does not return the return value of a sp, it returns rows
    > affected (addedd, deleted, changed). a query always returns -1
    >
    > if you want a return values, you need to added a return value parameter:
    >
    > SqlCommand cmd = new SqlCommand(spname,myConnection);
    > SqlParameter retparam = new SqlParameter("RV",SqlDbType.Int);
    > retparam.Direction= ParameterDirection.ReturnValue;
    > cmd.CommandType = CommandType.StoredProcedure;
    > cmd.ExecuteNonQuery();
    > int returnValue = (int) retparam.Value;
    >
    > -- bruce (sqlwork.com)
    >
    >
    > "david" <> wrote in message
    > news:...
    > >I have trouble with it.
    > >
    > > I have created a sproc in SQL Server, called SearchClass, which returns a
    > > searched key from a table. I have tested this procedure in Analyzer and it
    > > works fine. However, in the client side, when I use ExecuteNonQuery as
    > > follows
    > > ---
    > > Me.SqlConnection1.Open()
    > > classKey = Me.SqlCommand2.ExecuteNonQuery()
    > > Me.SqlConnection1.Close()
    > > --
    > > it always returns -1 rather than 5 that should be.
    > > What is the problem?
    > >
    > > Thank you for any help.
    > >
    > > David

    >
    >
    >
    =?Utf-8?B?ZGF2aWQ=?=, Oct 20, 2006
    #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.

Share This Page