locked
VB Passing Stored Procedure Parameters RRS feed

  • Question

  • Hi,

    I was wondering if anyone can shed some light on a beginner please.  I have read various forum posts but still can't get a stored proc to be executed from within VB.

    Basically, if I execute the following in ssms it works fine

    exec SI_InventoryBatch_UpdateNotUseBeforeDateByProduct 'R99999T','paulw'

    but if I try in VB it fails, but no errors or anything are produced. My code is below, and any help would be gratefully received.

            'command exec SI_InventoryBatch_UpdateNotUseBeforeDateByProduct 'R99999T','paulw'
            'DATASOURCE set to mersop above
            Dim CONNECTIONSTRING As String = DATASOURCE
            'Set up Parametes
            Dim PARAM1 As String = Me.Label3.Text   'R99999T
            Dim PARAM2 As String = Me.Label2.Text   'paulw

            'Set up SQL
            Dim sqlConnection As New SqlConnection(DATASOURCE)
            Dim cmd As New SqlCommand
            Dim returnValueProduct As Object = ""
            'Get Product
            With cmd
                .CommandType = CommandType.StoredProcedure
                .CommandText = "SI_InventoryBatch_UpdateNotUseBeforeDateByProduct"
                .Parameters.Add(New SqlParameter("@Product", SqlDbType.VarChar, 15)).Value = Me.Label3.Text
                .Parameters.Add(New SqlParameter("@updatedBy", SqlDbType.VarChar, 22)).Value = Me.Label2.Text
                .Connection = sqlConnection
            End With

            'Go
            Try
                returnValueProduct = ""
                MsgBox(cmd.CommandText)
                sqlConnection.Open()
                returnValueProduct = cmd.ExecuteScalar()
                Label4.Text = cmd.ExecuteScalar()
            Catch ex As Exception
                MsgBox("Error : " & ex.Message)
            Finally
                sqlConnection.Close()
            End Try

    Monday, March 11, 2013 12:29 PM

Answers

  • Guys, I must firstly apologise for my rookie mistake and secondly thank you for your replies.

    The issue was simply I had the variables the wrong way around, so the sql was trying to update paulw, which doesn't exist.

    Now I have swapped them, my app works fine.

    Thank you

    • Marked as answer by Jips Monday, March 11, 2013 3:18 PM
    Monday, March 11, 2013 3:18 PM

All replies

  • Start with putting on Option Strict On in top of your code.

    I would do then

         returnValueProduct = cmd.ExecuteScalar()
         if Not returnValueProduct Is DBNull.Value andalso Not ReturnValueProduct is Nothing then   
              Label4.Text = Cstr(returnValueProduct)
         end if

    Done in this message so watch typos or other small mistakes


    Success
    Cor

    Monday, March 11, 2013 12:36 PM
  • I would recommend posting the SQL from your sp. Unfortunately we really can't see what it's doing. I'm assuming there is a SELECT statement in the sp that returns a single value?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, March 11, 2013 1:49 PM
  • Hi thanks for the replies.

    I have tried the additional code, and it returns 1 record updated, but still doesn't update the record in question.

    running the command in ssms works fine.

    Here is the SP as requested.  I am beginning to think it may be SP itself :

    ALTER PROCEDURE [dbo].[SI_InventoryBatch_UpdateNotUseBeforeDateByProduct]
          @Product VARCHAR(15) = NULL, @updatedBy VARCHAR(22) = 'si support'
          AS
          UPDATE inventorybatch SET notusebeforedate = CASE WHEN  i.lifespanbasis = ' ' AND i.usebyisrange = 0 THEN ib.packdate
                      WHEN  i.lifespanbasis = ' ' AND i.usebyisrange <> 0 THEN DATEADD(day, notuse_bf_offset, ib.packdate)
                      WHEN  i.lifespanbasis <> ' ' AND i.usebyisrange = 0 THEN ib.killdate
                      WHEN  i.lifespanbasis <> ' ' AND i.usebyisrange <> 0 THEN DATEADD(day, notuse_bf_offset, ib.killdate) END,
                      updatedby = @updatedBy, updatedtimestamp = GETDATE()
          FROM inventorybatch ib (NOLOCK) JOIN inventory i (NOLOCK) ON ib.product = i.product
          WHERE
           ib.onhandqty > 0
          AND
           ib.notusebeforedate <>
                CASE WHEN  i.lifespanbasis = ' ' AND i.usebyisrange = 0 THEN ib.packdate
                      WHEN  i.lifespanbasis = ' ' AND i.usebyisrange <> 0 THEN DATEADD(day, notuse_bf_offset, ib.packdate)
                      WHEN  i.lifespanbasis <> ' ' AND i.usebyisrange = 0 THEN ib.killdate
                      WHEN  i.lifespanbasis <> ' ' AND i.usebyisrange <> 0 THEN DATEADD(day, notuse_bf_offset, ib.killdate) END
                AND ib.product = COALESCE(@Product,ib.product)
    RETURN 0

    Monday, March 11, 2013 2:58 PM
  • When I do this it works fine, so it must be to do with the me.label2.text statement


               .Parameters.Add(New SqlParameter("@Product", SqlDbType.VarChar, 15)).Value = "R99999T"    'Instead of Me.Label3.Text
                .Parameters.Add(New SqlParameter("@updatedBy", SqlDbType.VarChar, 22)).Value = "paulw"        'Instead of Me.Label2.Text

    Monday, March 11, 2013 3:05 PM
  • Guys, I must firstly apologise for my rookie mistake and secondly thank you for your replies.

    The issue was simply I had the variables the wrong way around, so the sql was trying to update paulw, which doesn't exist.

    Now I have swapped them, my app works fine.

    Thank you

    • Marked as answer by Jips Monday, March 11, 2013 3:18 PM
    Monday, March 11, 2013 3:18 PM