Getting Return Value From Stored Proceedure.

Discussion in 'ASP .Net' started by Mick Walker, Jun 20, 2007.

  1. Mick Walker

    Mick Walker Guest

    Hi Everyone,

    I am stumped here. I have the following stored proceedure:p

    CREATE PROCEDURE [dbo].[ImportLinesProductExists]
    @SupplierSKU varchar(50),
    @RetVal int
    AS
    Select @Retval = count(*) from dbo.ImportLines
    Where [SupplierSKUCode] = @SupplierSKU
    if @Retval > 0
    BEGIN
    Return 0
    END
    -- It wasn't found so we can now return -1
    Return -1

    Which works perfectly when I execute it with SQL Server Management Studio.

    I call the stored proceedure with the following code:

    Public Sub CheckProduct(ByVal _ConnString As String, ByVal
    supplierSKUCode as Integer)
    Dim ReturnValue As Integer = 0 ' Our Return Value
    Dim conn As New SqlConnection
    Dim cmd As New SqlCommand
    conn.ConnectionString = _ConnString
    cmd.Connection = conn
    cmd.CommandType = Data.CommandType.StoredProcedure
    cmd.CommandText = "dbo.ImportLinesProductExists"

    With cmd.Parameters
    .AddWithValue("@SupplierSKU", supplierSKUCode)
    .AddWithValue("@RetVal", System.DBNull.Value)
    End With
    Try
    conn.Open()
    ReturnValue = cmd.ExecuteScalar()
    Catch ex As SqlException
    Throw ex
    Finally
    conn.Close()
    cmd.Parameters.Clear()
    End Try
    If ReturnValue = 0 Then
    InsertTempProducts(_ConnString, Item)
    End If
    End Sub

    But no matter what, the sub always returns 0, even when I manually add a
    entry which should conflict, into the database.

    Does anyone know whay this is happening?

    Kind Regards
    Mick Walker
     
    Mick Walker, Jun 20, 2007
    #1
    1. Advertising

  2. You have to set the parameter direction to ParameterDirection.ReturnValue
    explicitly for the return parameter..

    "Mick Walker" wrote:

    > Hi Everyone,
    >
    > I am stumped here. I have the following stored proceedure:p
    >
    > CREATE PROCEDURE [dbo].[ImportLinesProductExists]
    > @SupplierSKU varchar(50),
    > @RetVal int
    > AS
    > Select @Retval = count(*) from dbo.ImportLines
    > Where [SupplierSKUCode] = @SupplierSKU
    > if @Retval > 0
    > BEGIN
    > Return 0
    > END
    > -- It wasn't found so we can now return -1
    > Return -1
    >
    > Which works perfectly when I execute it with SQL Server Management Studio.
    >
    > I call the stored proceedure with the following code:
    >
    > Public Sub CheckProduct(ByVal _ConnString As String, ByVal
    > supplierSKUCode as Integer)
    > Dim ReturnValue As Integer = 0 ' Our Return Value
    > Dim conn As New SqlConnection
    > Dim cmd As New SqlCommand
    > conn.ConnectionString = _ConnString
    > cmd.Connection = conn
    > cmd.CommandType = Data.CommandType.StoredProcedure
    > cmd.CommandText = "dbo.ImportLinesProductExists"
    >
    > With cmd.Parameters
    > .AddWithValue("@SupplierSKU", supplierSKUCode)
    > .AddWithValue("@RetVal", System.DBNull.Value)
    > End With
    > Try
    > conn.Open()
    > ReturnValue = cmd.ExecuteScalar()
    > Catch ex As SqlException
    > Throw ex
    > Finally
    > conn.Close()
    > cmd.Parameters.Clear()
    > End Try
    > If ReturnValue = 0 Then
    > InsertTempProducts(_ConnString, Item)
    > End If
    > End Sub
    >
    > But no matter what, the sub always returns 0, even when I manually add a
    > entry which should conflict, into the database.
    >
    > Does anyone know whay this is happening?
    >
    > Kind Regards
    > Mick Walker
    >
     
    =?Utf-8?B?U2l2YSBN?=, Jun 20, 2007
    #2
    1. Advertising

  3. Mick Walker

    Hans Kesting Guest

    > Hi Everyone,
    >
    > I am stumped here. I have the following stored proceedure:p
    >
    > CREATE PROCEDURE [dbo].[ImportLinesProductExists]
    > @SupplierSKU varchar(50),
    > @RetVal int
    > AS
    > Select @Retval = count(*) from dbo.ImportLines
    > Where [SupplierSKUCode] = @SupplierSKU
    > if @Retval > 0
    > BEGIN
    > Return 0
    > END
    > Which works perfectly when I execute it with SQL Server Management
    > Studio.
    >
    > I call the stored proceedure with the following code:
    >
    > Public Sub CheckProduct(ByVal _ConnString As String, ByVal
    > supplierSKUCode as Integer)
    > Dim ReturnValue As Integer = 0 ' Our Return Value
    > Dim conn As New SqlConnection
    > Dim cmd As New SqlCommand
    > conn.ConnectionString = _ConnString
    > cmd.Connection = conn
    > cmd.CommandType = Data.CommandType.StoredProcedure
    > cmd.CommandText = "dbo.ImportLinesProductExists"
    > With cmd.Parameters
    > .AddWithValue("@SupplierSKU", supplierSKUCode)
    > .AddWithValue("@RetVal", System.DBNull.Value)


    as mentioned before, you need to set the Direction to ParameterDirection.ReturnValue

    > End With
    > Try
    > conn.Open()
    > ReturnValue = cmd.ExecuteScalar()


    you don't need ExecuteScalar here: that will return the first value in the
    first column
    in the first table returned, and you don't return any tables at all.
    An ExecuteNonQuery should do.

    > Catch ex As SqlException
    > Throw ex
    > Finally
    > conn.Close()
    > cmd.Parameters.Clear()


    before you clear the parameters, read the value of @RetVal - this is the
    value you want.

    > End Try
    > If ReturnValue = 0 Then
    > InsertTempProducts(_ConnString, Item)
    > End If
    > End Sub
    > But no matter what, the sub always returns 0, even when I manually add
    > a entry which should conflict, into the database.
    >
    > Does anyone know whay this is happening?
    >
    > Kind Regards
    > Mick Walke
     
    Hans Kesting, Jun 20, 2007
    #3
  4. Mick Walker

    bruce barker Guest

    you are confused.

    executescaler returns the first column value of the first row of the
    first resultset. your proc does not return a resultset, so it should be
    throwing an error. you should be calling ExecuteNonquery().

    after processing the resultsets (or call ExecuteNonquery which does
    this) you can access output parameters (though you need to set the
    parameter direction to output before making the query).

    to get the actual return value of a proc, you add an int parameter with
    the direction of ReturnValue. after all resultsets have been returned,
    you can accesses this parameter's value to get the return value.


    -- bruce (sqlwork.com)





    Mick Walker wrote:
    > Hi Everyone,
    >
    > I am stumped here. I have the following stored proceedure:p
    >
    > CREATE PROCEDURE [dbo].[ImportLinesProductExists]
    > @SupplierSKU varchar(50),
    > @RetVal int
    > AS
    > Select @Retval = count(*) from dbo.ImportLines
    > Where [SupplierSKUCode] = @SupplierSKU
    > if @Retval > 0
    > BEGIN
    > Return 0
    > END
    > -- It wasn't found so we can now return -1
    > Return -1
    >
    > Which works perfectly when I execute it with SQL Server Management Studio.
    >
    > I call the stored proceedure with the following code:
    >
    > Public Sub CheckProduct(ByVal _ConnString As String, ByVal
    > supplierSKUCode as Integer)
    > Dim ReturnValue As Integer = 0 ' Our Return Value
    > Dim conn As New SqlConnection
    > Dim cmd As New SqlCommand
    > conn.ConnectionString = _ConnString
    > cmd.Connection = conn
    > cmd.CommandType = Data.CommandType.StoredProcedure
    > cmd.CommandText = "dbo.ImportLinesProductExists"
    >
    > With cmd.Parameters
    > .AddWithValue("@SupplierSKU", supplierSKUCode)
    > .AddWithValue("@RetVal", System.DBNull.Value)
    > End With
    > Try
    > conn.Open()
    > ReturnValue = cmd.ExecuteScalar()
    > Catch ex As SqlException
    > Throw ex
    > Finally
    > conn.Close()
    > cmd.Parameters.Clear()
    > End Try
    > If ReturnValue = 0 Then
    > InsertTempProducts(_ConnString, Item)
    > End If
    > End Sub
    >
    > But no matter what, the sub always returns 0, even when I manually add a
    > entry which should conflict, into the database.
    >
    > Does anyone know whay this is happening?
    >
    > Kind Regards
    > Mick Walker
     
    bruce barker, Jun 20, 2007
    #4
  5. Mick Walker

    Lloyd Sheen Guest

    "Mick Walker" <> wrote in message
    news:...
    > Hi Everyone,
    >
    > I am stumped here. I have the following stored proceedure:p
    >
    > CREATE PROCEDURE [dbo].[ImportLinesProductExists]
    > @SupplierSKU varchar(50),
    > @RetVal int
    > AS
    > Select @Retval = count(*) from dbo.ImportLines
    > Where [SupplierSKUCode] = @SupplierSKU
    > if @Retval > 0
    > BEGIN
    > Return 0
    > END
    > -- It wasn't found so we can now return -1
    > Return -1
    >
    > Which works perfectly when I execute it with SQL Server Management Studio.
    >
    > I call the stored proceedure with the following code:
    >
    > Public Sub CheckProduct(ByVal _ConnString As String, ByVal
    > supplierSKUCode as Integer)
    > Dim ReturnValue As Integer = 0 ' Our Return Value
    > Dim conn As New SqlConnection
    > Dim cmd As New SqlCommand
    > conn.ConnectionString = _ConnString
    > cmd.Connection = conn
    > cmd.CommandType = Data.CommandType.StoredProcedure
    > cmd.CommandText = "dbo.ImportLinesProductExists"
    >
    > With cmd.Parameters
    > .AddWithValue("@SupplierSKU", supplierSKUCode)
    > .AddWithValue("@RetVal", System.DBNull.Value)
    > End With
    > Try
    > conn.Open()
    > ReturnValue = cmd.ExecuteScalar()
    > Catch ex As SqlException
    > Throw ex
    > Finally
    > conn.Close()
    > cmd.Parameters.Clear()
    > End Try
    > If ReturnValue = 0 Then
    > InsertTempProducts(_ConnString, Item)
    > End If
    > End Sub
    >
    > But no matter what, the sub always returns 0, even when I manually add a
    > entry which should conflict, into the database.
    >
    > Does anyone know whay this is happening?
    >
    > Kind Regards
    > Mick Walker


    This looks like a function rather than a procedure. Make this a Stored
    Function and return the count as the return value for the function.

    LS
     
    Lloyd Sheen, Jun 20, 2007
    #5
    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. Replies:
    0
    Views:
    302
  2. Problematic coder

    Call Oracle Stored Proceedure VB.NET

    Problematic coder, Mar 8, 2007, in forum: ASP .Net
    Replies:
    2
    Views:
    451
    Problematic coder
    Mar 9, 2007
  3. Problematic coder

    Call Oracle Stored Proceedure VB.NET

    Problematic coder, Mar 8, 2007, in forum: ASP .Net
    Replies:
    0
    Views:
    327
    Problematic coder
    Mar 8, 2007
  4. Problematic coder

    Erro calling stored proceedure

    Problematic coder, Mar 13, 2007, in forum: ASP .Net
    Replies:
    9
    Views:
    438
    Problematic coder
    Mar 13, 2007
  5. calling a proceedure

    , Mar 23, 2007, in forum: ASP General
    Replies:
    0
    Views:
    112
Loading...

Share This Page