Member Avatar for jez9

Really need help please
this is my code:

 Sub updteWS()
        Dim sql = "select Count(*) from ItemProduct where Code= ?"
        Dim cmd = New OleDbCommand(sql, conn)
        cmd.Parameters.AddWithValue("@Code", LVCart.Items(0).SubItems(0).Text)

        Dim result As Integer = Convert.ToInt32(cmd.ExecuteScalar())
        cmd.Parameters.Clear()
        cmd.Dispose()

        If result > 0 Then
            For Each x As ListViewItem In LVCart.Items
                Dim Stocks_WS1 As Integer = 1

                sql = "UPDATE ItemProduct SET " & _
                                         "Stocks_WS = Stocks_WS - ?" & _
                                         "WHERE cnt >= numexpected AND CODE = ?"
                cmd = New OleDbCommand(sql.ToString, conn)

                cmd.Parameters.AddWithValue("@Stocks_WS", Stocks_WS1)
                cmd.Parameters.AddWithValue("@CODE", x.SubItems(0).Text)
                cmd.ExecuteNonQuery()

                sql = "UPDATE ItemProduct SET cnt='0'" +
       "WHERE cnt >= numexpected AND CODE=?"
                cmd = New OleDbCommand(sql.ToString, conn)
                cmd.Parameters.AddWithValue("@CODE", x.SubItems(0).Text)
                cmd.ExecuteNonQuery()
            Next
        Else

        End If
        cmd.Parameters.Clear()
        cmd.Dispose()
    End Sub

database field : CODE, Stocks_WS, Stocks_retail, cnt , numexpected
Stocks_WS - stocks for wholesale
Stocks_retail - stocks for retail
cnt - the number of times where the items brought buy the customer (it will only count when the customer buys a retail)
numexpected - it is the estimated number of items per unit (example 100pcs per box)
heres the example (stocks_ws = 1 , stocks_retail = 100 , numexpected = 100)

*My problem is when i buy an 2 or more items, it keeps decreasing the stocks_ws even i bought retail.
i want to decrease the stocks_ws when the [cnt] is greater than or equal to [numexpected]

heres my code for wholesale and retail

connect()
            For w = 0 To LVCart.Items.Count - 1
                If LVProduct.Items(w).SubItems(4).Text = LVProduct.Items(w).SubItems(5).Text Then
                    GoTo Here
                End If
                If LVProduct.Items(w).SubItems(4).Text = LVProduct.Items(w).SubItems(5).Text Then
                    GoTo there
                End If
            Next

            If chkWholesale.Checked = True Then
Here:
                connect()
                sql = "UPDATE ItemProduct SET STOCKS_WS = STOCKS_WS - " & LVCart.Items(a).SubItems(4).Text & " WHERE CODE = '" & LVCart.Items(a).Text & "'"
                cmd = New OleDb.OleDbCommand(sql, conn)
                cmd.ExecuteNonQuery()
                cmd.Dispose()
            End If

            If ckretail.Checked = True Then
there:
                connect()
                sql = "UPDATE ItemProduct SET STOCKS_Retail = STOCKS_Retail  - " & LVCart.Items(a).SubItems(4).Text & " WHERE CODE = '" & LVCart.Items(a).Text & "'"
                cmd = New OleDb.OleDbCommand(sql, conn)
                cmd.ExecuteNonQuery()

                Dim CODE As String = LVCart.Items(a).Text
                Dim cnt As Integer = Convert.ToDecimal(LVCart.Items(a).SubItems(4).Text)
                sql = "UPDATE ItemProduct SET " & _
                                      "cnt = cnt + ? " & _
                                      "WHERE CODE = ?"
                cmd = New OleDbCommand(sql.ToString, conn)

                cmd.Parameters.AddWithValue("@cnt", cnt)
                cmd.Parameters.AddWithValue("@dte", CODE)

                cmd.ExecuteNonQuery()


                 updteWS()
                cmd.Dispose()

            Else
            End If

               cmd.Dispose()
            FillLView()
        Next

thank you in advance :D

  • 3 Contributors
  • 6 Replies
  • 228 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by jez9

Dani AI

Generated

The symptom (retail purchases causing wholesale stock to drop) usually comes from control-flow and counting mistakes rather than the database engine. In this thread already pointed out a duplicated conditional and advised dropping GOTOs — both are important. The reliable pattern is: 1) decrement retail stock by the purchased quantity, 2) add that quantity to cnt, 3) compute how many full wholesale units those retail items form, and only then decrement Stocks_WS by that integer number of boxes while leaving the leftover cnt (remainder) in the row. Do this inside a transaction and use positional parameters with OleDb (order matters).

A compact, safe VB.NET workflow (illustrative) — read current cnt and numexpected, compute boxes, update retail stock and cnt, then subtract wholesale boxes if any:

Using tx = conn.BeginTransaction()
  Using cmd = New OleDbCommand("SELECT cnt, numexpected FROM ItemProduct WHERE CODE = ?", conn, tx)
    cmd.Parameters.AddWithValue("@p1", code)
    Using rdr = cmd.ExecuteReader()
      If rdr.Read() Then
        Dim curCnt = Convert.ToInt32(rdr("cnt"))
        Dim expected = Convert.ToInt32(rdr("numexpected"))
        Dim combined = curCnt + qty
        Dim boxes = combined \ expected
        Dim newCnt = combined Mod expected

        Using upd = New OleDbCommand("UPDATE ItemProduct SET Stocks_Retail = Stocks_Retail - ?, cnt = ? WHERE CODE = ?", conn, tx)
          upd.Parameters.AddWithValue("@p2", qty)
          upd.Parameters.AddWithValue("@p3", newCnt)
          upd.Parameters.AddWithValue("@p4", code)
          upd.ExecuteNonQuery()
        End Using

        If boxes > 0 Then
          Using updWS = New OleDbCommand("UPDATE ItemProduct SET Stocks_WS = Stocks_WS - ? WHERE CODE = ?", conn, tx)
            updWS.Parameters.AddWithValue("@p5", boxes)
            updWS.Parameters.AddWithValue("@p6", code)
            updWS.ExecuteNonQuery()
          End Using
        End If
      End If
    End Using
  End Using
  tx.Commit()
End Using

Troubleshooting checklist: ensure loops index the same list (LVCart vs LVProduct), avoid duplicated IFs and GOTOs, watch parameter order for OleDb, include whitespace when concatenating SQL, guard updates to prevent negative stock (WHERE Stocks_WS >= ?), and test boundary cases where a single purchase converts multiple boxes. reported the duplicated-condition bug fixed; applying the counting/transaction pattern above prevents the wholesale stock from being decremented incorrectly.

Recommended Answers

All 6 Replies

Member Avatar for jez9

please guys help me. i tried another code but the result is the same. It only works when i buy 1 item only but when i try two or more items, it keeps reducing the stocks number for wholesale even though i buy in retail.

Member Avatar for J.C. SolvoTerra

Wow, I didn't know GoTo was still valid, please stop that.

Well first of all, the flow of this entire operation is a little shaky, however looking at this:

                If LVProduct.Items(w).SubItems(4).Text = LVProduct.Items(w).SubItems(5).Text Then
                    GoTo Here
                End If
                If LVProduct.Items(w).SubItems(4).Text = LVProduct.Items(w).SubItems(5).Text Then
                    GoTo there
                End If

Both conditions are exactly the same so if the conditions are correct it will always goto Here, and never there.

in fact GoTo there will never be called, Here seems to be where your Whole sale stuff is... Wholesale is always going to be called if the above conditions are correct.

Also not understanding what your check boxes are doing and when, there is a chance when you GoTo Here, there's code will be executed afterwards.

Edited by J.C. SolvoTerra
Member Avatar for J.C. SolvoTerra

Also, GoTo exits the loop meaning the conditions within that particular loop will only ever be true once. I can see that this is in another loop but it's not clear from here what for.

Member Avatar for jez9

thank you J.C. SolvoTerra, i find the error why it is not working,

If LVProduct.Items(w).SubItems(4).Text = LVProduct.Items(w).SubItems(5).Text Then
                    GoTo Here
                End If
                If LVProduct.Items(w).SubItems(4).Text = LVProduct.Items(w).SubItems(5).Text Then
                    GoTo there
                End If



        `

its just the same. thats why its not working

Member Avatar for Reverend Jim

For what it's worth, GOTOs are evil. Before the advent of structured programming constructs like IF-THEN-ELSE, GOTOs were a necessary evil, but used properly they could be tolerated. I, myself, have (on rare occasions) used GOTOs to escape from nested constructs, but I have never had to resort to that since the introduction of TRY-CATCH. I strongly suggest that you rewrite your code to eliminate your GOTOs.

Member Avatar for jez9

@Reverend Jim, thanks, i tried the try and catch and its more reliable than GOTO
thanks guys for helping, i got it now

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.