如何在vb.net中从主数量中减去一种形式的数量

Question

我的vb.net项目中有两个表格，一个是Medform，我在其中添加我的药品名称、数量、药品价格、生产日期和有效期，另一个表格是Billing form，我在这里销售药品，所以每次我在Billing form上销售药品时，我希望从当前售出的数量中减去可用数量或库存，并显示在称为可用库存的账单表格标签中，同时也自动从药品表格中的主要数量中减去。两种表单都有不同的数据库表。以下是Medform Add按钮的代码

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Using create As New OleDbCommand("INSERT INTO tblMed([Med_ID], [MedName], [MedPrice], [MedQuantity], [MfgDate], [ExpDate], [Company])VALUES(@Med_ID, @MedName, @MedPrice, @MedQuantity, @MfgDate, @ExpDate, @Company)", conn)
create.Parameters.AddWithValue("@Med_ID", OleDbType.VarChar).Value = Med_ID_TextBox.Text.Trim
create.Parameters.AddWithValue("@MedName", OleDbType.VarChar).Value = MedNameTextBox.Text.Trim
create.Parameters.AddWithValue("@MedPrice", OleDbType.Currency).Value = MedPriceTextBox.Text
create.Parameters.AddWithValue("@MedQuantity", OleDbType.VarChar).Value = MedQtyTextBox.Text.Trim
create.Parameters.AddWithValue("@MfgDate", OleDbType.VarChar).Value = MfgDate.Text.Trim
create.Parameters.AddWithValue("@ExpDate", OleDbType.VarChar).Value = ExpDate.Text.Trim
create.Parameters.AddWithValue("@Company", OleDbType.VarChar).Value = CompCb.SelectedValue.ToString()


If create.ExecuteNonQuery Then
Med_ID_TextBox.Clear()
MedNameTextBox.Clear()
MedPriceTextBox.Clear()
MedQtyTextBox.Clear()
bind_data()
End If

End Using
Catch ex As Exception
End Try

End Sub

下面是账单表单添加到账单按钮的代码

Private Sub btnBill_Click(sender As Object, e As EventArgs) Handles btnBill.Click

Try
If txtQty.Text > stock Then
MsgBox("Not Enough in Stock")
ElseIf txtQty.Text = "" Then
MsgBox("Enter a quantity")
Else
'Let's Add Medicine To The Bill
Dim rnum As Integer = BillGridView.Rows.Add()
i = i + 1
BillGridView.Rows.Item(rnum).Cells("Column1").Value = i
BillGridView.Rows.Item(rnum).Cells("Column2").Value = MediComboCb.SelectedValue.ToString()
BillGridView.Rows.Item(rnum).Cells("Column3").Value = txtQty.Text
BillGridView.Rows.Item(rnum).Cells("Column4").Value = AgentNameCb.Text
BillGridView.Rows.Item(rnum).Cells("Column5").Value = txtBillDate.Text
BillGridView.Rows.Item(rnum).Cells("Column6").Value = MedPrice
BillGridView.Rows.Item(rnum).Cells("Column7").Value = txtQty.Text * MedPrice + GrTot

Dim subtotal = txtQty.Text * MedPrice
GrTot = GrTot + subtotal
TotalAmountlb.Text = GrTot
If con.State = ConnectionState.Closed Then
con.Open()
End If
Using create As New OleDbCommand("INSERT INTO tblBill([AgentName], [DrugName], [Quantity], [BillDate], [UnitPrice], [TotalAmount])VALUES(@AgentName, @DrugName, @Quantity, @BillDate, @UnitPrice, TotalAmount)", con)
create.Parameters.AddWithValue("@AgentName", OleDbType.VarChar).Value = AgentNameCb.Text.Trim
create.Parameters.AddWithValue("@DrugName", OleDbType.VarChar).Value = MediComboCb.SelectedValue.ToString()
create.Parameters.AddWithValue("@Quantity", OleDbType.Integer).Value = txtQty.Text.Trim
create.Parameters.AddWithValue("@BillDate", OleDbType.VarChar).Value = txtBillDate.Text.Trim
create.Parameters.AddWithValue("@UnitPrice", OleDbType.Currency).Value = MedPrice
create.Parameters.AddWithValue("@TotalAmount", OleDbType.Currency).Value = TotalAmountlb.Text

If create.ExecuteNonQuery Then
txtQty.Clear()
AgentNameCb.Text = ""
End If
End Using

End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

Answer 1

最简单的方法是运行一个计时器，每隔几秒钟查询一次数据库并刷新标签。

一个更好的方法是创建一个类，从数据库中加载药物数据：

Public Class Drug
Private _quantity As Integer
Public Property Name As String
Public Property BillDate As DateTime
Public Property UnitPrice As Single
Public Event QuantityChanged(sender As Object, e As EventArgs)
Public Property Quantity As Integer
Get
Return _quantity
End Get
Set(value As Integer)
If value <> _quantity Then
_quantity = value
RaiseEvent QuantityChanged(Me, EventArgs.Empty)
End If
End Set
End Property
End Class

您可以为数据库中的所有药物创建此类的实例，并将这些实例添加到列表(药物(中(请参见列表类(。然后，将此列表传递给两个表单，以便它们处理相同的数据。

在您的类中，您可以检测更改并将事件引发到：

1. 通知表单数据已更改并更新标签
2. 使用新数据更新数据库