批量更新MySQL表并从DataGridView计算值。 vb.net

Question

我想使用DataGridView的值进行批量更新数据库表，但我还需要总结DataGridView和MySQL表值FISRT。 我该怎么做？

这是我当前的更新按钮的代码

附加图像

        For Each row As DataGridViewRow In dgvStok.Rows
            If (Not row.IsNewRow) Then
                Dim ID As DataGridViewCell = row.Cells("ID").Value
                Dim Stok As DataGridViewCell = row.Cells("Stok").Value

                conn = New MySqlConnection('myconnstring)
                conn.Open()

                cmd = New MySqlCommand("Select * from tbStok where ID='" & ID & "'", conn)
                dr = cmd.ExecuteReader
                dr.Read()
                If dr.HasRows Then
                    Dim StokInventory, IncomingStok As Integer
                    StokInventory = dr.Item("Stok")
                    TotalStok = StokInventory + IncomingStok

                    Dim updateStok As String = "update tbStok set Stok ='" & TotalStok & "' where ID = '" & ID & "'"
                    cmd = New MySqlCommand(updateStok, conn)
                    cmd.ExecuteNonQuery()
                End If
                conn.Close()
            End If
        Next

Answer 1

我找到了我的解决方案

Private Sub UpdateBTN_Click(sender As Object, e As EventArgs) Handles UpdateBTN.Click
    Dim ID As String
    Dim StartingStok, IncomingStok, TotalStok As Integer

    For Each DGVR As DataGridViewRow In dgvStok.Rows
        ID = (DGVR.Cells("ID").Value)
        IncomingStok= (DGVR.Cells("Stok").Value)

        #open mysql connection string

        cmd = New MySqlCommand("Select * from tbStok where ID='" & ID & "'", conn)
        dr = cmd.ExecuteReader
        dr.Read()
        If dr.HasRows Then
            StartingStok = dr.Item("Stok")
        End If
        conn.Close()

        TotalStok = StartingStok + IncomingStok

        #open mysql connection string

        Dim UpdateTable As String = "update tbStok set Stok ='" & TotalStok & "' where ID = '" & ID & "'"
        cmd = New MySqlCommand(UpdateTable, conn)
        cmd.ExecuteNonQuery()
        conn.Close()
    Next
End Sub

此代码将通过datagridView和每个ID（主键）从datagridview和mySQL表中循环循环，然后它将使用新值更新该表，该表由汇总datagridview表和每个ID的mysql表更新