I can’t get the stock down

Question

I am trying to decrease the amount of stock at the time of sale and without success, I used the code below, no error appears..

Public Sub UpdateDecreaseQuantity()
    Try
        For Each row As DataGridViewRow In DataGridView1.Rows
            Dim OtherValue As Integer = 0
            Dim cb As String = "Update Dish SET Quantity = Quantity - @OtherValue"
            con.Open()
            cmd = New OleDbCommand(cb, con)
            Integer.TryParse(row.Cells(2).Value, OtherValue)
            cmd.Parameters.Add("@OtherValue", OleDbType.Integer).Value = OtherValue
            cmd.ExecuteNonQuery()
            con.Close()
        Next
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        cmd.Dispose()
        con.Close()
    End Try

End Sub

Answer 1

You need to debug the code to find out which line isn’t doing what you should be doing.

Reading the code from above, there are two points where the problem may be...

First:

Integer.TryParse(row.Cells(2).Value, OtherValue)

You are not checking whether the conversion was successful. The method TryParse returns a boolean indicating if everything went right. You need a If to verify this.

According to:

cmd.Parameters.Add("@OtherValue", OleDbType.Integer).Value = OtherValue

You should check the documentation if this is how it is done to inform a parameter in the query. I never used Oledb so I don’t know if it’s right. You better find out.

Answer 2

It worked using the code below:

con = New OleDbConnection(cs)
        con.Open()
        Dim cb As String = "update Dish set Quantity = Quantity - '" & Val(DataGridView2.CurrentRow.Cells(2).Value) & "' WHERE ItemID = " & Val(DataGridView2.CurrentRow.Cells(0).Value) & ""
        cmd = New OleDbCommand(cb, con)
        cmd.ExecuteNonQuery()
        con.Close()