VB.NET操作Access：隐藏ID列后用EMPID删数据报类型不匹配错误

错误原因 #

EMPID是文本类型主键，但你编写的SQL删除语句直接拼接字符串值，没有给文本值添加单引号，导致Access将其解析为数值类型，与字段的文本类型不匹配，触发「条件表达式中数据类型不匹配」错误。

比如你的代码生成的错误SQL是：
DELETE FROM tblemployee WHERE EMPID = ABC123
而符合文本类型要求的正确SQL应该是：
DELETE FROM tblemployee WHERE EMPID = 'ABC123'

快速修复方案 #

修改Button1_Click中的查询语句，给iRow值添加单引号：

Dim query = " DELETE FROM tblemployee WHERE EMPID = '" & iRow & "'"

推荐的安全方案（避免SQL注入） #

直接拼接字符串存在SQL注入风险，建议使用参数化查询，同时彻底规避数据类型匹配问题：

1. 改造DoSQL方法，支持传入参数：

Private Sub DoSQL(ByVal Sql As String, Optional ByVal parameters As List(Of OleDbParameter) = Nothing)
    Using Cmd As New OleDb.OleDbCommand(Sql, Con)
        If Con.State = ConnectionState.Closed Then Con.Open()
        Try
            If parameters IsNot Nothing Then
                Cmd.Parameters.AddRange(parameters.ToArray())
            End If
            Cmd.ExecuteNonQuery()
            MessageBox.Show("Records Updated Completed.", "Update Status", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MsgBox("Error Update: " & ex.Message)
        Finally
            Con.Close()
        End Try
    End Using
End Sub

2. 修改Button1_Click中的调用逻辑：

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    If DataGridView1.RowCount = 0 Then Exit Sub
    Dim empId As String = DataGridView1.Item(1, DataGridView1.CurrentRow.Index).Value.ToString()
    Dim FName As String = DataGridView1.Item(2, DataGridView1.CurrentRow.Index).Value.ToString()
    Dim Result As DialogResult = MessageBox.Show("Are you sure you want to delete the data?" & vbCrLf & "Full Name: " & FName, "Confirm Deletion", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
    If Result = DialogResult.Yes Then
        Dim query = " DELETE FROM tblemployee WHERE EMPID = @EmpId"
        Dim parameters As New List(Of OleDbParameter)()
        parameters.Add(New OleDbParameter("@EmpId", OleDbType.VarChar) With {.Value = empId})
        DoSQL(query, parameters)
        Load_Data()
    End If
End Sub

额外注意事项 #

• 确认DataGridView1.Item(1, ...)确实对应EMPID列，避免列索引错误导致取到无效值
• 重新调用Load_Data()会自动同步数据库数据到DataGridView，可删除DataGridView1.Rows.Remove(DataGridView1.CurrentRow)这行代码，避免重复操作

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