如何从库存中扣除数量？ [VB6] [英] How do I deduct a quantity from stock? [VB6]

问题描述

我需要通过匹配产品的ID来从表中扣除数量。如果我运行以下代码，它会扣除一个数量，但问题是例如...我输入ID号为1，并且必须单击两次扣除按钮才能扣除。第二次将ID号更改为2并单击扣除按钮...首次从ID号1中扣除。但现在，如果我再次点击，它会从ID号2中扣除。我该如何解决这个问题？我正在使用MS Access作为数据库（ADODB）。

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

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

推荐答案

目前尚不清楚adodc1是什么，但看起来您将数据集打开为adodc1.Recordset，并且您正在使用另一个连接（con）更新记录。导致记录锁定/并发问题。



如果您使用的是adodc1.Recordset.Update，我必须假设此记录集是可更新的。在这种情况下，不要使用单独的连接，只需更新记录集，它可能看起来像：

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:

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

注意：

如果IDval控件为空（过滤器上出现语法错误）会发生什么？

如果ID是数字，则删除单引号分隔符并使用Val（）函数（此优惠）使用空白控件，因为它将返回零[假设零无效！]）

如果上述代码不适用于您的Adodc1对象，则需要向我提供确切的类（我假设Recordset成员是ADO记录集。

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

这篇关于如何从库存中扣除数量？ [VB6]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！