Getting a Return Value from Stored Proc

• • Reply

  13 years ago

  by materialised

  Mick Walker

  Northern Ireland, United Kingdom

  Joined 14 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.
  
  

  Report abuse
• • Reply

  13 years ago

  by khari6579

  Hari K

  Chennai, India

  Joined 15 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......
  
  
  
  
  
  
  
  
  


  Report abuse