Bulk update mysql table and calculate value from datagridview. VB.NET-CodePudding

I want to do bulk update my database table using value from datagridview, but also I need to sum the datagridview and mysql table value fisrt. how do I do that?

here is my current code of my update button

attached image

        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

CodePudding user response：

I've found my solution

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

This code will looping through datagridview and sum value from datagridview and mysql table for each ID(primary key) and then it will update the table with new value which is from summarizing datagridview table and mysql table for each ID