Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I need to deduct a quantity from a table by matching the ID of the product. If I run the below code, it deducts a quantity but the problem is for instance... I Enter ID number as 1 and have to click deduct button twice to deduct. Second time I change ID number to 2 and click deduct button... First time it deducts from ID number 1 itself. But now if I click once more, it deducts from ID number 2. How can I solve this problem? I'm using MS Access as database(ADODB).

VB
Option Explicit
Dim con As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim sql As String

Private Sub Command1_Click()
Set con = New ADODB.Connection
Set rst = New ADODB.Recordset

con.Open (adodc1.ConnectionString)
sql = "UPDATE stock SET QTY = QTY - 1 WHERE ID = '" & IDval.Text & "'"
con.Execute (sql)
Adodc1.Recordset.Update
Adodc1.Refresh
con.Close
End Sub


Thank you in advance
Posted
Updated 11-Mar-18 0:29am
Comments
NewPast 5-Oct-14 11:16am    
Did you try:
"UPDATE stock SET QTY = (QTY - 1) WHERE (ID = '" & IDval.Text & "')"
Maciej Los 5-Oct-14 13:31pm    
Not clear. Please, be more specific and provide more details (sample data).
[no name] 5-Oct-14 14:03pm    
You say "ID number" is if you think that ID is a number but you use it in your query as if it were a string.
Maciej Los 5-Oct-14 15:57pm    
Good point!
Richard Deeming 6-Oct-14 11:50am    
Your code is susceptible to SQL Injection[^].

Parameterized queries aren't quite as simple with ADODB as they are with .NET, but they're still not particularly difficult:
How To Invoke a Parameterized ADO Query Using VBA/C++/Java[^]

1 solution

It's not clear exactly what adodc1 is, but it looks like you have the dataset open as adodc1.Recordset and you are updating the record using another connection (con). Causing a record locking/concurrency issue.

If you are using adodc1.Recordset.Update, I have to assume that this recordset is updateable. In which case, do not use the separate connection, simply update the recordset which probably looks like:

VB
Adodc1.Recordset.Filter = "ID = '" & IDval.Text & "'" 
If Adodc1.Recordset.EOF Then
    msgbox "Product ID " & IDval.Text & " not found"
Else
    Adodc1.Recordset("QTY") = Adodc1.Recordset("QTY")-1
    Adodc1.Recordset.Update
End If


Notes:
What happens if the IDval control is blank (syntax error on the filter)?
If ID is a number remove the single quote delimiters and use the Val() function (this deals with a blank control as it would then return zero [assuming that zero is not valid!])
If the above code does not work with your Adodc1 object, you will need to provide me the exact class (I have assumed that the Recordset member is an ADO recordset).
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900