VB.NET提交DataGridView更改报错及增删改存盘解决方案

错误原因与问题解答 #

错误原因 #

抛出System.InvalidOperationException: 'Update requires a valid UpdateCommand when passed DataRow collection with modified rows.'错误的核心原因是：OleDbDataAdapter仅配置了SelectCommand用于查询数据，但未设置对应的UpdateCommand、InsertCommand、DeleteCommand来处理数据的修改、新增和删除操作。直接调用Update()方法时，适配器没有可用命令同步DataTable的更改到数据库。

关于新增/删除行的提交 #

只要配置正确，DataGridView中的新增、删除行更改可以同步到磁盘数据库，具体实现分为两种思路。

解决方案 #

方案1：使用OleDbCommandBuilder自动生成命令（简洁高效） #

利用OleDbCommandBuilder基于已有的SelectCommand自动生成更新、插入、删除命令，适合单表简单操作：

' 在btnTest_Click方法中，设置ResultsAdapter.SelectCommand后添加以下代码
Dim cmdBuilder As New OleDbCommandBuilder(ResultsAdapter)
' 自动生成所需命令，无需手动编写SQL
cmdBuilder.GetUpdateCommand()
cmdBuilder.GetInsertCommand()
cmdBuilder.GetDeleteCommand()

修改后的btnSave_Click方法只需一行代码即可同步所有更改：

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
    Try
        ResultsAdapter.Update(ResultsTable)
        MsgBox("数据已保存", vbOKOnly, "操作成功")
    Catch ex As Exception
        MsgBox("保存失败：" & ex.Message, vbOKOnly, "错误")
    End Try
End Sub

注意：使用该方案要求查询的表必须有主键，否则CommandBuilder无法自动生成命令。

方案2：手动编写SQL命令处理更改（精细控制） #

如果需要自定义业务逻辑或处理特殊场景，可以手动遍历DataGridView行，分别处理新增、更新、删除操作，代码实现如下：

Imports System.Data
Imports System.Data.OleDb
Imports System.Collections.Generic

Public Class Form1
    Dim BooksConnection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; " _
    + "Data Source = D:\My Docs\BooksDb.accdb")
    Dim ResultsCommand As OleDbCommand = Nothing
    Dim ResultsAdapter As New OleDbDataAdapter
    Dim ResultsTable As New DataTable
    Dim strTableName As String = "Authors"
    Dim lstDeletedIDs As New List(Of Long) ' 记录被删除行的AU_ID

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        BooksConnection.Open()
        txtSQLtester.Text = "SELECT * FROM " + strTableName
    End Sub

    Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
        BooksConnection.Close()
    End Sub

    Private Sub btnTest_Click(sender As Object, e As EventArgs) Handles btnTest.Click
        Try
            ResultsCommand = New OleDbCommand(txtSQLtester.Text, BooksConnection)
            ResultsAdapter.SelectCommand = ResultsCommand
            ResultsTable.Clear() ' 清空旧数据，避免重复加载
            ResultsAdapter.Fill(ResultsTable)
            grdSQLtester.DataSource = ResultsTable
            ResultsCommand.Dispose()
            lstDeletedIDs.Clear() ' 清空删除记录列表，避免重复删除
        Catch ex As Exception
            MsgBox("SQL语句执行失败", vbOKOnly, "SQL错误")
        End Try
    End Sub

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        Dim strCmd As String, row As Integer
        ' 处理修改和新增行
        For row = 0 To grdSQLtester.Rows.Count() - 2 ' 忽略末尾自动生成的空行
            With grdSQLtester.Rows(row).Cells
                If .Item("Au_ID").Value Is Nothing OrElse .Item("Au_ID").Value.ToString.Trim() = "" Then
                    ' 新增记录：检查必填字段是否有效
                    If Not .Item("Author").Value Is Nothing AndAlso .Item("Author").Value.ToString.Trim().Length > 0 Then
                        ' 注意：生产环境请使用参数化查询替代字符串拼接
                        strCmd = $"INSERT INTO {strTableName} (Author,Year_Born) VALUES ('{.Item("Author").Value.ToString.Trim()}',{.Item("Year_Born").Value.ToString})"
                        ExecuteSQL(strCmd)
                    End If
                Else
                    ' 更新现有记录
                    strCmd = $"UPDATE {strTableName} SET Author = '{.Item("Author").Value.ToString.Trim()}', Year_Born = {.Item("Year_Born").Value.ToString} WHERE Au_ID = {.Item("Au_ID").Value.ToString}"
                    ExecuteSQL(strCmd)
                End If
            End With
        Next row
        ' 处理删除行
        For Each ID In lstDeletedIDs
            strCmd = $"DELETE FROM {strTableName} WHERE Au_ID = {ID.ToString}"
            ExecuteSQL(strCmd)
        Next
        ' 刷新DataGridView显示最新数据
        btnTest.PerformClick()
        MsgBox("数据已保存", vbOKOnly, "操作成功")
    End Sub

    Sub ExecuteSQL(strCmd As String)
        ResultsCommand = New OleDbCommand(strCmd, BooksConnection)
        ResultsCommand.ExecuteNonQuery()
        ResultsCommand.Dispose()
    End Sub

    Private Sub grdSQLtester_UserDeletingRow(sender As Object, e As DataGridViewRowCancelEventArgs) Handles grdSQLtester.UserDeletingRow
        ' 记录要删除的行主键ID
        If Not e.Row.Cells.Item("au_ID").Value Is Nothing Then
            Dim ID As Long = CLng(e.Row.Cells.Item("au_ID").Value)
            lstDeletedIDs.Add(ID)
        End If
    End Sub
End Class

关键注意事项 #

• SQL注入风险：代码中使用字符串拼接SQL仅用于学习，生产环境必须使用参数化查询，示例如下：

  ' 参数化插入示例
  strCmd = "INSERT INTO Authors (Author,Year_Born) VALUES (@Author,@Year_Born)"
  ResultsCommand = New OleDbCommand(strCmd, BooksConnection)
  ResultsCommand.Parameters.AddWithValue("@Author", .Item("Author").Value)
  ResultsCommand.Parameters.AddWithValue("@Year_Born", .Item("Year_Born").Value)
  ResultsCommand.ExecuteNonQuery()
  

• 空值处理：必须判断字段值是否为空，避免因空值导致SQL语法错误或类型转换异常。
• 资源释放：使用Dispose()释放OleDbCommand等资源，避免内存泄漏。

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