VB.NET+Dapper操作Access：单按钮增改及相关问题排查

问题1：双击DataGridView后按钮显示为Edit，但执行操作时仍新增记录 #

原因 #

双击DataGridView的事件代码中，contactId的赋值语句被注释，导致contactId始终保持初始值0，触发插入逻辑而非更新逻辑。

修复 #

取消注释contactId的赋值代码，并处理单元格值可能为DBNull的情况：

Private Sub dgvContact_DoubleClick(ByVal sender As Object, ByVal e As EventArgs) Handles dgvContact.DoubleClick
    If Not Me.dgvContact.IsHandleCreated Then Return

    Try
        If dgvContact.CurrentRow.Index <> -1 Then
            ' 修复：取消注释并处理DBNull情况
            If Not IsDBNull(dgvContact.CurrentRow.Cells(0).Value) Then
                contactId = Convert.ToInt32(dgvContact.CurrentRow.Cells(0).Value)
            Else
                contactId = 0
            End If
            txtName.Text = dgvContact.CurrentRow.Cells(1).Value.ToString()
            txtMobile.Text = dgvContact.CurrentRow.Cells(2).Value.ToString()
            txtAddress.Text = dgvContact.CurrentRow.Cells(3).Value.ToString()
            btnDelete.Enabled = True
            btnSave.Text = "Edit"
        End If
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

问题2：自动编号类型主键改为文本类型主键的代码调整 #

步骤 #

1. 将全局变量contactId的类型从Integer改为String：

Private contactId As String = String.Empty

2. 修改双击事件中的赋值逻辑，适配文本类型：

If Not IsDBNull(dgvContact.CurrentRow.Cells(0).Value) Then
    contactId = dgvContact.CurrentRow.Cells(0).Value.ToString().Trim()
Else
    contactId = String.Empty
End If

3. 调整btnSave_Click中的参数和判断逻辑：

Private Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSave.Click
    If Not Me.btnSave.IsHandleCreated Then Return
    Try
        If oledbCon.State = ConnectionState.Closed Then
            oledbCon.Open()
        End If
        Dim param As New DynamicParameters()
        param.Add("@Nme", txtName.Text.Trim())
        param.Add("@Mobile", txtMobile.Text.Trim())
        param.Add("@Address", txtAddress.Text.Trim())
        param.Add("@ContactID", contactId)
        
        ' 文本主键的判断逻辑：空字符串为新增，否则为更新
        If String.IsNullOrEmpty(contactId) Then
            oledbCon.Execute("INSERT INTO Contact (Nme,Mobile,Address) VALUES (@Nme,@Mobile,@Address)", param, commandType:=CommandType.Text)
            MessageBox.Show("Saved Successfully")
        Else
            Dim affectedRows = oledbCon.Execute("UPDATE Contact SET Nme = @Nme,Mobile = @Mobile,Address = @Address WHERE ContactID = @ContactID", param, commandType:=CommandType.Text)
            If affectedRows > 0 Then
                MessageBox.Show("Updated Successfully")
            Else
                MessageBox.Show("No record found to update")
            End If
        End If
        FillDataGridView()
        Clear()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        oledbCon.Close()
    End Try
End Sub

问题3：注释插入语句仅执行更新时，弹窗提示成功但数据库无变化 #

原因 #

contactId仍为0，更新语句的WHERE ContactID = 0无法匹配任何记录，Dapper的Execute方法返回0，但代码未判断受影响行数直接提示成功。

修复 #

在更新逻辑中判断Execute的返回值，只有受影响行数大于0时才提示成功：

Else
    Dim affectedRows = oledbCon.Execute("UPDATE Contact SET Nme = @Nme,Mobile = @Mobile,Address = @Address WHERE ContactID = @ContactID", param, commandType:=CommandType.Text)
    If affectedRows > 0 Then
        MessageBox.Show("Updated Successfully")
    Else
        MessageBox.Show("No matching record found for update")
    End If
End If

问题4：测试更新按钮时出现“Data type mismatch in criteria expression”错误 #

原因 #

1. 混合使用了字符串拼接和参数化查询，导致类型不匹配（如果ContactID是数字类型，字符串拼接加引号会把它转为文本，与数据库字段类型冲突）；
2. Using块会自动释放连接，Finally中的oledbCon.Close()会引发错误；
3. 参数定义后未实际使用，而是用了字符串拼接的SQL。

修复 #

改用纯参数化查询，移除字符串拼接，同时去掉冗余的连接关闭代码：

Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
    If Not Me.btnUpdate.IsHandleCreated Then Return
    Try
        Using oledbCon As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\DapperCRUD.accdb")
            oledbCon.Open()
            Dim param As New DynamicParameters()
            param.Add("@Nme", txtName.Text.Trim())
            param.Add("@Mobile", txtMobile.Text.Trim())
            param.Add("@Address", txtAddress.Text.Trim())
            
            ' 根据ContactID的类型调整参数：如果是数字类型用Integer，文本类型用String
            Dim contactIdParam As Object = If(IsNumeric(txtcontactid.Text.Trim()), Convert.ToInt32(txtcontactid.Text.Trim()), txtcontactid.Text.Trim())
            param.Add("@ContactID", contactIdParam)
            
            ' 使用纯参数化SQL，避免字符串拼接
            Dim affectedRows = oledbCon.Execute("UPDATE Contact SET Nme = @Nme, Mobile = @Mobile, Address = @Address WHERE ContactID = @ContactID", param, commandType:=CommandType.Text)
            If affectedRows > 0 Then
                MessageBox.Show("Updated Successfully")
            Else
                MessageBox.Show("No matching record found")
            End If
            FillDataGridView()
        End Using
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

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