如何编写SQL语法实现数据转移并同步删除原数据（VB.NET代码求助）

先说说你原代码里的几个关键问题：

• INSERT语句根本没执行：你定义了sql的INSERT语句，但直接把cmd.CommandText改成了DELETE的语句，导致数据根本没转移就直接删了（或者删错了）
• DELETE条件错误：用Me.Text存ID非常不规范，而且容易出错，应该用专门的变量/控件存储选中的ID
• 缺少事务：如果INSERT成功但DELETE失败，会导致数据不一致，必须用事务保证两个操作要么都成功要么都失败
• SQL注入风险：直接拼接字符串到SQL里，不仅容易出错，还存在安全隐患，要用参数化查询
• 未处理无效行点击：DataGridView点击表头时会触发CellClick，导致索引越界

下面是修正后的完整代码：

Imports System.Data.OleDb

Public Class Payment
    ' 数据库连接字符串（建议放到配置文件，这里暂时保留）
    Private ReadOnly connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\VetSystemDatabase.accdb"
    ' 存储选中的记录ID，避免用Me.Text这种不规范的方式
    Private selectedRecordId As Integer? = Nothing

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ' 先判断是否选中了有效记录
        If Not selectedRecordId.HasValue Then
            MsgBox("请先选中一条记录再操作！", MsgBoxStyle.Exclamation)
            Return
        End If

        Dim paymentAmount As Decimal
        ' 验证输入的金额是否有效
        If Not Decimal.TryParse(PricePay.Text, paymentAmount) Then
            MsgBox("请输入有效的金额！", MsgBoxStyle.Exclamation)
            PricePay.Focus()
            Return
        End If

        ' 使用事务保证数据一致性
        Using con As New OleDbConnection(connectionString)
            Try
                con.Open()
                Dim transaction As OleDbTransaction = con.BeginTransaction()

                Try
                    ' 1. 执行INSERT：将选中的记录转移到tbl_RegisteredRecords
                    Dim insertSql As String = "INSERT INTO [tbl_RegisteredRecords] SELECT * FROM [Register] WHERE [ID] = @RecordId;"
                    Using insertCmd As New OleDbCommand(insertSql, con, transaction)
                        insertCmd.Parameters.AddWithValue("@RecordId", selectedRecordId.Value)
                        insertCmd.ExecuteNonQuery()
                    End Using

                    ' 2. 执行DELETE：删除Register表中对应的记录
                    Dim deleteSql As String = "DELETE FROM [Register] WHERE [ID] = @RecordId;"
                    Using deleteCmd As New OleDbCommand(deleteSql, con, transaction)
                        deleteCmd.Parameters.AddWithValue("@RecordId", selectedRecordId.Value)
                        Dim affectedRows As Integer = deleteCmd.ExecuteNonQuery()

                        If affectedRows > 0 Then
                            transaction.Commit() ' 事务提交，两个操作生效
                            MsgBox("客户已完成支付，记录已转移！")
                            loadrecord() ' 刷新数据
                            ' 清空选中状态和输入框
                            selectedRecordId = Nothing
                            PricePay.Clear()
                        Else
                            transaction.Rollback() ' 回滚，没有记录被删除
                            MsgBox("未找到对应记录，操作失败！")
                        End If
                    End Using
                Catch ex As Exception
                    transaction.Rollback() ' 出错则回滚所有操作
                    MsgBox($"操作失败：{ex.Message}")
                End Try
            Catch ex As Exception
                MsgBox($"数据库连接失败：{ex.Message}")
            End Try
        End Using
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        loadrecord()
        Search.Clear()
    End Sub

    Sub loadrecord()
        Using con As New OleDbConnection(connectionString)
            Try
                con.Open()
                Dim sql As String = "SELECT * FROM [Register]"
                Using cmd As New OleDbCommand(sql, con)
                    Using da As New OleDbDataAdapter(cmd)
                        Dim dt As New DataTable()
                        da.Fill(dt)
                        DataGridView1.DataSource = dt
                    End Using
                End Using
            Catch ex As Exception
                MsgBox($"加载数据失败：{ex.Message}")
            End Try
        End Using
    End Sub

    Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        ' 排除点击表头的情况（行索引>=0才是有效数据行）
        If e.RowIndex >= 0 Then
            Dim row As DataGridViewRow = DataGridView1.Rows(e.RowIndex)
            ' 验证ID是否为有效整数
            If Integer.TryParse(row.Cells(0).Value.ToString(), selectedRecordId) Then
                PricePay.Text = row.Cells(16).Value.ToString()
            Else
                selectedRecordId = Nothing
                PricePay.Clear()
                MsgBox("选中的记录ID无效！", MsgBoxStyle.Exclamation)
            End If
        End If
    End Sub

    Private Sub Search_TextChanged(sender As Object, e As EventArgs) Handles Search.TextChanged
        Using con As New OleDbConnection(connectionString)
            Try
                con.Open()
                ' 参数化查询避免SQL注入
                Dim sql As String = "SELECT * FROM [Register] WHERE [CellphoneNo] LIKE @SearchText OR [PetName] LIKE @SearchText OR [TelephoneNO] LIKE @SearchText;"
                Using cmd As New OleDbCommand(sql, con)
                    ' 通配符放在参数值里，而不是SQL语句中
                    cmd.Parameters.AddWithValue("@SearchText", $"%{Search.Text}%")
                    Using da As New OleDbDataAdapter(cmd)
                        Dim dt As New DataTable()
                        da.Fill(dt)
                        DataGridView1.DataSource = dt
                    End Using
                End Using
            Catch ex As Exception
                MsgBox($"搜索失败：{ex.Message}", MsgBoxStyle.Information)
            End Try
        End Using
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Me.Hide()
        DashboardAdmin.Show()
    End Sub
End Class

关键修改点说明： #

1. 添加事务处理：用OleDbTransaction保证INSERT和DELETE要么都成功，要么都回滚，避免数据不一致
2. 修复INSERT执行问题：现在会先执行INSERT再执行DELETE，并且都使用参数化查询
3. 规范ID存储：用类级变量selectedRecordId存储选中的记录ID，替代原来的Me.Text，避免窗体标题被修改导致的错误
4. 参数化查询：所有SQL语句都使用参数，避免SQL注入，同时解决数据类型匹配问题
5. 添加输入验证：验证金额是否有效，验证选中的ID是否为有效整数，避免无效操作
6. 使用Using语句：自动释放数据库连接、命令、适配器等资源，避免内存泄漏
7. 处理无效行点击：判断e.RowIndex >= 0，避免点击表头触发错误

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