在 VB.NET 上使用 Dapper 编辑交易明细中的数量时如何更新股票

问：

在编辑交易中的数量（数量）时，如何通过添加、减去和删除数量来更新正确的库存。 请指导。下面我尝试了，但结果不匹配，也许我的代码有问题。

谢谢

Public Class Form1
    Dim itrservice As New StocksoutdetailService()
    Private Invno As String
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DataGridView1.DataSource = itrservice.GetLoadStocksMaster()
        DataGridView1.ReadOnly = True
        DataGridView2.DataSource = itrservice.GetLoadStocksoutdetailMaster()
    End Sub
  Private Sub BtnRefreshall_Click(sender As Object, e As EventArgs) Handles BtnRefreshall.Click
        DataGridView1.DataSource = itrservice.GetLoadStocksMaster()
        DataGridView1.ReadOnly = True
        DataGridView2.DataSource = itrservice.GetLoadStocksoutdetailMaster()
    End Sub
 Private Sub BtnUpdateAll_Click(sender As Object, e As EventArgs) Handles BtnUpdateAll.Click
        Try
            If DataGridView2.RowCount = 0 Then
                Throw New Exception("no data")
            End If

            For Each item As DataGridViewRow In DataGridView2.Rows
                Dim detail = New Stocksoutdetail With {
                            .Qty = CInt(item.Cells(3).Value),
                            .Invno = CStr(item.Cells(1).Value),
                             .No = CInt(item.Cells(0).Value),
                             .CodeProduct = CStr(item.Cells(2).Value)
                }
                Dim getstocks = New Stocks With {
                           .CodeProduct = CStr(item.Cells(2).Value)
                           }
                Dim GetStock = itrservice.GetStock(getstocks)
                Dim stocks = New Stocks With {
                            .Qty_Stock = GetStock.Qty_Stock - (detail.Qty),
                            .Qty_Stockout = GetStock.Qty_Stockout + (detail.Qty),
                            .CodeProduct = CStr(item.Cells(2).Value)
                            }
                itrservice.UpdateStocksoutdetail(detail)
                itrservice.Updatestock(stocks)
            Next item
            MessageBox.Show("successfully")
        Catch ex As Exception
            MessageBox.Show(ex.Message, "POS", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try
    End Sub
End Class
Public Class StocksoutdetailService
    Public Function GetOledbConnectionString() As String
        Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\dapperdemo.accdb;Persist Security Info=False;"
    End Function
    Private ReadOnly _conn As OleDbConnection
    Private _connectionString As String = GetOledbConnectionString()
    Public Sub New()
        _conn = New OleDbConnection(_connectionString)
    End Sub
    Public Function GetLoadStocksoutdetailMaster() As IEnumerable(Of Stocksoutdetail)
        Dim sql = "SELECT * FROM Stocksoutdetail"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of Stocksoutdetail)(sql).ToList()
        End Using
    End Function
  Public Function GetLoadStocksMaster() As IEnumerable(Of Stocks)
        Dim sql = "SELECT * FROM Stocks"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of Stocks)(sql).ToList()
        End Using
    End Function
    Public Sub UpdateStocksoutdetail(ByVal Obj As Stocksoutdetail)
        Dim sql = $"UPDATE `Stocksoutdetail` Set `Qty`={Obj.Qty} WHERE `Invno`='{Obj.Invno}' and `No`={Obj.No} and `CodeProduct`='{Obj.CodeProduct}';"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            _conn.Execute(sql)
        End Using
    End Sub
    Public Sub Updatestock(ByVal Obj As Stocks)
        Dim sql = $"UPDATE `Stocks` Set `QTY_STOCK`= {Obj.Qty_Stock},`QTY_STOCKOUT`= {Obj.Qty_Stockout} WHERE `CodeProduct`='{Obj.CodeProduct}';"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            _conn.Execute(sql)
        End Using
    End Sub
End Class
Public Class Stocksoutdetail
    Public Property No() As Integer
    Public Property Invno() As String
    Public Property CodeProduct() As String
    Public Property Qty() As Integer
End Class
Public Class Stocks
    Public Property CodeProduct() As String
    Public Property Qty_Stock() As Integer
    Public Property Qty_Stockout() As Integer

End Class

• 第一个数据

表：股票

表：Stocksoutdetail

• 更新后的数据

我从表Stocksoutdetail中的datagridview进行了更新

和更新后的表格股票

• 期望的结果

“股票”表中的结果应如下所示

更新后以表单形式显示的结果