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
