How can i subtract a number coming from database?

[jez9]

ive got a table: code, stocksretail, stocksws and numexpected
for example code: 0001, stocksretail : 24, stocksws: 1, numexpected :24 (numexpected = 1wholesale that contains 24pcs in retail)
code: 0002, stocksretail : 24, stocksws: 1, numexpected :24
my problem is when i buy the two items above, the stocksretail should now become 0 [(but it didnt happen, it becomes -24)],and stocksws=0(ive got a code in this stocksws and its working) .
What i really want is when the stocksws is decreasing, the stocksretail should decrease too depending on numexpected

 Sub wsrt()
        For a = 0 To LVCart.Items.Count - 1
            sql = "select * from ItemProduct  WHERE CODE = '" & LVCart.Items(a).Text & ""
            Dim cmd = New OleDb.OleDbCommand(sql, conn)


            acsdr = cmd.ExecuteReader()
            While acsdr.Read()
                Dim STOCKSWSRt As Integer = acsdr("numexpected")

                sql = "UPDATE ItemProduct SET " & _
                                       "stocksretail = stocksretail - ?" & _
                                       "WHERE CODE = ?"

                cmd = New OleDbCommand(sql.ToString, conn)

                cmd.Parameters.AddWithValue("@stocksretail", STOCKSWSRt)
                cmd.Parameters.AddWithValue("@CODE", LVCart.Items(a).Text)
                cmd.ExecuteNonQuery()

            End While
        Next

        acsdr.Close()
        cmd.Dispose()
    End Sub

sorry if my code is kinda messy but atleast i really try this one, please help me.im stuck in this code.

[Teme64]

Your code is not messy :)

You are using positional parameters (?) so I would change cmd as follows:

Dim STOCKSWSRt As Integer = 24 ' Hardcoded value ("numexpected" from the db)

Sql = "UPDATE ItemProduct SET stocksretail = stocksretail - ? WHERE CODE = ?" ' Positional params
cmd = New OleDbCommand(Sql, conn)

cmd.Parameters.Add(STOCKSWSRt)
cmd.Parameters.Add("1002") ' Hardcoded value here too, should be LVCart.Items(a).Text
cmd.ExecuteNonQuery()

Or with named parameters:

Sql2 = "UPDATE ItemProduct SET stocksretail = stocksretail - @pstockretail WHERE CODE = @pCODE" ' Named params
cmd2 = New OleDbCommand(Sql2, conn)

cmd2.Parameters.AddWithValue("@pstockretail", STOCKSWSRt)
cmd2.Parameters.AddWithValue("@pCODE", "1002")
cmd2.ExecuteNonQuery()

HTH

[JerrimePatient]

Try this.

Sub wsrt()
    For a = 0 To LVCart.Items.Count - 1
        sql = "UPDATE ItemProduct SET stocksretail = (stocksretail - " & STOCKSWSRt & ") WHERE CODE = '" & LVCart.Items(a).Text & "'"
        cmd = New OleDbCommand(sql.ToString, conn)
        cmd.ExecuteNonQuery()
    Next
    acsdr.Close()
    cmd.Dispose()
End Sub

[jez9]

@Teme64 , thanks for cheering me up :) can i ask what is "1002"? and where did you get that?

actually its not only 24 is the value of numexpected, it depends upon on how many did the owner's input.

[jez9]

@JerrimePatient, i tried your code but it says 'Syntax error in string in query expression 'CODE='0002'

[JerrimePatient]

Try this.

Sub wsrt()
    For a = 0 To LVCart.Items.Count - 1
        sql = "UPDATE ItemProduct SET stocksretail = (stocksretail - " & STOCKSWSRt & ") WHERE CODE = '" & LVCart.Items(a).ToString() & "'"
        cmd = New OleDbCommand(sql.ToString, conn)
        cmd.ExecuteNonQuery()
    Next
    acsdr.Close()
    cmd.Dispose()
End Sub

[jez9]

@JerrimePatient, its still the same

[JerrimePatient]

give me the data on LVCart.Items

[jez9]

i order the two items: 0001 and 0002, i still use te code above , i just change some of it

Sub wsrt()
        For a = 0 To LVCart.Items.Count - 1
            sql = "select * from ItemProduct  WHERE CODE = '" & LVCart.Items(a).Text & ""
            Dim cmd = New OleDb.OleDbCommand(sql, conn)
            acsdr = cmd.ExecuteReader()
            While acsdr.Read()
                Dim STOCKSWSRt As Integer = acsdr("numexpected")
                sql = "UPDATE ItemProduct SET stocksretail = (stocksretail - " & STOCKSWSRt & ") WHERE CODE = '" & LVCart.Items(a).ToString() & "'"
    cmd = New OleDbCommand(sql.ToString, conn)
    cmd.ExecuteNonQuery()
Next
acsdr.Close()
cmd.Dispose()
 End Sub

[JerrimePatient]

The error is in line 3
sql = "select * from ItemProduct WHERE CODE = '" & LVCart.Items(a).Text & "" change in to
sql = "select * from ItemProduct WHERE CODE = '" & LVCart.Items(a).ToString() & "'"

You've miss the ' in "" so make it "'" in line 3

[jez9]

thanks but the problem now is when i used the ToString , the stocksretail is not decreasing and when i change it back to .Text it decreased but i still got a problem, i mean ive got 24 pcs in each item in 0001 and 0002, and the output now is -24. that supposed to be 0.

[Teme64]

Hi Jez! That "1001" was a hardcoded id to db-table. In your example it was "0001" and I copied it wrong :)

Your code updates (decrases) stocksretail twice. First update is caused by For-Next loop which loops Items.Count times i.e. through all the records. Second loop is While acsdr.Read() which also loops all the records and contains the actual update.

If LVCart.Items contains the db id-values you want to update, drop the While acsdr.Read() part from your code. Array variable LVCart.Items(a) selects a single record and you update that single record.

So the code goes something like this:

    For a = 0 To LVCart.Items.Count - 1
      sql = "select * from ItemProduct  WHERE CODE = '" & LVCart.Items(a).ToString() & "'"
      Dim cmd = New OleDb.OleDbCommand(sql, conn)
      acsdr = cmd.ExecuteReader() ' Get a single record
      acsdr.Read() ' Get a single record
      Dim STOCKSWSRt As Integer = CInt(acsdr("numexpected")) ' Cast Object to Integer 

      sql = "UPDATE ItemProduct SET stocksretail = stocksretail - @pnumExpected WHERE CODE = '" & LVCart.Items(a).ToString() & "'" ' Named params
        cmd = New OleDbCommand(sql, conn)
        cmd.Parameters.AddWithValue("@pnumExpected", STOCKSWSRt)
        cmd.ExecuteNonQuery()
    Next 

HTH

[jez9]

@Teme64 , oh thank you, i understand now, but when i used Tostring in lvcart.items it says that "no data exist for the row/column" then i change it back to text(both lvcart in select and update) but it decrease twice again , but when i change back to text the select only and not the update its not decreasing.

i tried another value in numexpected instead of both 24, the other one is 48. the result is

-24 and the other one is 0 (numexpected = 48), in my conclusion i think the problem here is when i order two items for example, it added both numexpected value then it will subtract the sum of two numexpected to the items that i'd buy.