mysql - 在 VB.NET 中更新 DGV 中的多个选定行

Question

我有一个 DataBound DGV 它有 3 个 Columns 即 ID(not pk), 名称 和状态。我有 2 个按钮 Add 和 Post。添加，将 Data 添加到 DGV 和 DataBase(MySql) 但在 Status 中，它添加 "没有”。我想做的是，例如，我的 DGV 中有 3 个 Rows 并突出显示这 3 个，然后单击 Post， 中的值状态 将更改为"is"。这是我到目前为止所做的，但是我在使用 UPDATE QUERY 的语法时遇到了问题。 此代码现在可用

  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the TestDataSet.testing table. You can move, or remove it, as needed.
        Me.TestingTableAdapter.Fill(Me.TestDataSet.testing)
        Using _conn As New MySqlConnection("Server = localhost; Username= root; Password =; Database = test")

            Using cmd
                With cmd
                    MsgBox("Connection Established")
                    .Connection = _conn
                    .Parameters.Clear()
                    .CommandText = "Select Max(TransactionID) from testing"
                    _conn.Open()
                    Dim dr As MySqlDataReader
                    dr = cmd.ExecuteReader()
                    If dr.Read() Then
                        If IsDBNull(dr.Item(0)) Then
                            txtNumber.Text = "1"
                        Else

                            txtName.Text = dr(0).ToString() + 1
                        End If

                    End If
                End With
            End Using
        End Using

        FillGrid()

    End Sub

    Private Sub FillGrid()



        Using _conn As New MySqlConnection("Server = localhost; Username= root; Password =; Database = test")
            Using _comm As New MySqlCommand
                With _comm
                    .CommandText = " SELECT `ID`, `TransactionID`, `Name`, `Posted` FROM `testing`"
                    .Connection = _conn
                End With
                Using _adapter As New MySqlDataAdapter(_comm)
                    Try
                        _conn.Open()
                        Dim _ds As New DataSet
                        _adapter.Fill(_ds)
                        GVTransaction.DataSource = _ds.Tables(0)
                    Catch ex As Exception

                    End Try
                End Using
            End Using
        End Using
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Using conn As New MySqlConnection("Server = localhost; Username= root; Password =; Database = test")
            Using cmd
                With cmd
                    MsgBox("Connection Established")
                    .Connection = conn
                    .Parameters.Clear()
                    .CommandText = "INSERT INTO testing(TransactionID, Name, Posted) VALUES (@ID, @iName, @iPosted)"
                    .Parameters.Add(New MySqlParameter("@ID", txtNumber.Text))
                    .Parameters.Add(New MySqlParameter("@iName", txtName.Text))
                    .Parameters.Add(New MySqlParameter("@iPosted", "No"))


                End With
                Try
                    conn.Open()
                    cmd.ExecuteNonQuery()
                Catch ex As MySqlException
                    MsgBox(ex.Message.ToString())
                End Try
            End Using
        End Using

        MsgBox("Data Added to the Database")

        Me.TestingTableAdapter.Dispose()

        Me.TestingTableAdapter.Fill(Me.TestDataSet.testing)
    End Sub

    Private Sub btnPost_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPost.Click


        Dim _ID As New List(Of String)
        Dim _Name As New List(Of String)
        For Each _x As DataGridViewRow In TestingDataGridView.SelectedRows
            _ID.Add(_x.Cells("GVTransactionID").Value.ToString())
            _Name.Add("'" & _x.Cells("GVName").Value.ToString() & "'")
        Next
        Dim inClause As String = String.Join(",", _ID.ToArray())
        Dim inClause1 As String = String.Join(",", _Name.ToArray())
        Dim _sqlUpdate As String = String.Format("UPDATE testing SET Posted = @Posted WHERE TransactionID IN ({0}) AND Name IN ({1})", inClause, inClause1)

        Using _conn As New MySqlConnection("Server = localhost; Username= root; Password =; Database = test")
            Using _commm As New MySqlCommand()
                With _commm
                    .CommandText = _sqlUpdate
                    .Connection = _conn
                    .CommandType = CommandType.Text
                    .Parameters.AddWithValue("@Posted", "YES")

                End With
                Try
                    _conn.Open()
                    _commm.ExecuteNonQuery()
                    FillGrid()
                Catch ex As MySqlException
                    MsgBox(ex.StackTrace.ToString)
                End Try

            End Using
        End Using
    End Sub
End Class

非常感谢任何帮助或提示。请谢谢

Answer 1

试试这个，

Dim _ID As New List(Of String) 
Dim _Name As New List(Of String) 
For Each _x As DataGridViewRow In TestingDataGridView.SelectedRows  
   _ID.Add(_x.Cells("TransactionID").Value.ToString())
   _Name.Add("'" & _x.Cells("Name").Value.ToString() & "'")
Next 

Dim inClause As String = String.Join(",", _ID.ToArray()) 
Dim inClause1 As String = String.Join(",", _Name.ToArray()) 
Dim _sqlUpdate As String = String.Format("UPDATE testing SET Posted = @Posted WHERE TransactionID IN ({0}) AND Name IN ({1})", inClause, inClause1)

Using _conn As New MySqlConnection("Server = localhost; Username= root; Password =; Database = test")
    Using _comm As New MySqlCommand()
        With _comm
          .CommandText = _sqlUpdate
          .Connection = _conn
        End With
        Try
            _conn.Open()
            _comm.ExecuteNonQuery()
        Catch ex As MySqlException
            MsgBox(ex.Message.ToString())
        End Try

    End Using
End Using