Getting a Return Value from Stored Proc

  • 10 years ago

    I have the following sp:
    ALTER PROCEDURE [dbo].[ImportLinesProductExists]
          @SupplierSKU varchar(50),
          @RetVal int output
    AS
    Select @Retval = count(*) from dbo.ImportLines
    Where [SupplierSKUCode] = @SupplierSKU
    if @Retval > 0
    BEGIN
    Return 0
    END











    And the following code calls the sp:

    Public Sub CheckProduct(ByVal _ConnString As String, 
                      ByVal ProductList As List(Of Import_ImportLines.Lines)) 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" Dim Item As New Import_ImportLines.Lines For Each Item In ProductList Dim param As New SqlParameter param = cmd.Parameters.Add("@RetVal", SqlDbType.Int) param.Direction = ParameterDirection.ReturnValue param.Value = 0 param = cmd.Parameters.Add("@SupplierSKU", SqlDbType.VarChar, 50) param.Value = Item.supplierSKUCode param.Direction = ParameterDirection.Input Try conn.Open() cmd.ExecuteNonQuery() ReturnValue = Convert.ToInt32(cmd.Parameters("@RetVal").Value) Catch ex As SqlException Throw ex Finally conn.Close() End Try If ReturnValue = 0 Then InsertTempProducts(_ConnString, Item) End If Next conn.Close() End Sub


    However all I get when my code is ran, is a execption telling me that my stored proceedure expects the variable @RetVal. I am really lost here, could someone please tell me where I am going wrong.

    Kind Regards.

  • 10 years ago
    Hi Mike
     I don't know what is avail in PorductList Item.

     In my point of view...
     You just remove that looping statement "For Each Item In ProductList"

     You do the following steps...











    With cmd
     .Parameters.Add(New SqlClient.SqlParameter("@SupplierSKU", SqlDbType.VarChar, 50, ParameterDirection.Input, False, 0, 0, "@SupplierSKU", DataRowVersion.Current, Item.supplierSKUCode))

    .Parameters.Add(New SqlClient.SqlParameter("@RetVal", SqlDbType.Int, 4, ParameterDirection.Output, False, 0, 0, "@RetVal", DataRowVersion.Current, ReturnValue ))

    End With
                    
                    Try
                   
                        conn.Open()
                        cmd.ExecuteNonQuery()
                        ReturnValue = Convert.ToInt32(cmd.Parameters("@RetVal").Value)
                    Catch ex As SqlException
                        Throw ex
                    Finally
                        conn.Close()
                    End Try




















    Good Luck

    I hope this may solve your problem

    I have one doubt, in your SP you are mentioned @RetVal>0 then it return 0 but u are not used any else condition if it return 0 then what it will return...and one more thing at any cost  it will the return the 0 value... then what is purpose of this SP.

    Hari K......










Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Any fool can write code that a computer can understand. Good programmers write code that humans can understand.” - Martin Fowler