VB.NET中如何将Billing表单售药量从MedForm表单主库存中扣减

代码修复与逻辑调整 #

1. 先修正原有代码的错误 #

你现有Billing表单插入账单的SQL语句存在语法问题，TotalAmount 字段前遗漏了参数标识符@，会导致插入失败，先修正该问题。
另外建议将tblMed表中存储库存的MedQuantity字段从VarChar类型改为Integer数值类型，避免后续库存计算出现类型转换异常。

2. 新增库存更新与显示逻辑 #

在Billing表单的btnBill_Click事件中，账单数据插入成功后新增库存更新逻辑即可，修改后的完整代码如下：

Private Sub btnBill_Click(sender As Object, e As EventArgs) Handles btnBill.Click
    Try
        Dim saleQty As Integer
        ' 先校验输入的数量是否为有效数字
        If Not Integer.TryParse(txtQty.Text.Trim(), saleQty) Then
            MsgBox("请输入有效的售卖数量")
            Return
        End If

        If saleQty > CInt(stock) Then
            MsgBox("库存不足")
        ElseIf saleQty <= 0 Then
            MsgBox("请输入大于0的售卖数量")
        Else
            '添加药品到账单表格
            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 = saleQty
            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 = saleQty * MedPrice + GrTot

            Dim subtotal = saleQty * MedPrice
            GrTot = GrTot + subtotal
            TotalAmountlb.Text = GrTot.ToString()

            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 = saleQty
                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 = GrTot

                If create.ExecuteNonQuery > 0 Then
                    ' 计算剩余库存并显示到标签
                    Dim remainingStock As Integer = CInt(stock) - saleQty
                    ' 这里替换成你实际的available stock标签控件名
                    availableStockLabel.Text = $"剩余库存：{remainingStock}"

                    ' 更新主库存表tblMed的对应药品库存
                    Using updateCmd As New OleDbCommand("UPDATE tblMed SET MedQuantity = @RemainingStock WHERE MedName = @DrugName", con)
                        updateCmd.Parameters.AddWithValue("@RemainingStock", OleDbType.Integer).Value = remainingStock
                        updateCmd.Parameters.AddWithValue("@DrugName", OleDbType.VarChar).Value = MediComboCb.SelectedValue.ToString()
                        updateCmd.ExecuteNonQuery()
                    End Using

                    ' 如果MedForm处于打开状态，自动刷新其列表数据
                    If Application.OpenForms("MedForm") IsNot Nothing Then
                        CType(Application.OpenForms("MedForm"), MedForm).bind_data()
                    End If

                    txtQty.Clear()
                    AgentNameCb.Text = ""
                End If
            End Using
        End If
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        ' 操作完成后关闭数据库连接，避免连接泄漏
        If con.State = ConnectionState.Open Then
            con.Close()
        End If
    End Try
End Sub

3. 注意事项 #

• 如果你的药品存在重名情况，建议使用Med_ID作为关联条件更新库存，替换UPDATE语句中的WHERE MedName = @DrugName为WHERE Med_ID = @MedID，同时传入对应药品ID即可，匹配精度更高。
• 新增了输入校验逻辑，避免非数字数量输入导致的计算错误。

内容的提问来源于stack exchange，提问作者Kdmidia