How do I deduct a quantity from stock? [VB6]

Question

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 5-Oct-14 4:17am

Rodney Mathew

Updated 11-Mar-18 0:29am

Add a Solution

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

Add a Solution

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

PhilLenoir · Answer 1 · 2014-10-06T05:45:00

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).

How do I deduct a quantity from stock? [VB6]

1 solution

Solution 1

Add your solution here

Preview 0

How do I deduct a quantity from stock? [VB6]

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us