如何使用VB.NET中的数据库MS Access在dapper中一键更新和插入

Question

如何在VB.NET.中使用数据库MS Access在dapper中一键更新和插入

双击datagridview时，按钮文本将更改为编辑，但问题是，当更新出现时，不应出现新记录。"；Contactid"；是访问数据库中的autonumber和primarykey类型数据，我想问的另一个数据是"；ContactId"；是文本和主类型数据如何处理下面的代码？。

如果我注释if语句代码；插入"；只需运行sql"；"更新"；状态消息框成功，但数据库未更改。我使用的是适配器版本1.50.2

Private contactId As Integer = 0
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 contactId = 0 Then
oledbCon.Execute("INSERT INTO Contact (Nme,Mobile,Address) VALUES (@Nme,@Mobile,@Address)", param, commandType:=CommandType.Text)
MessageBox.Show("Saved Successfully")
Else
oledbCon.Execute("UPDATE Contact SET Nme = @Nme,Mobile = @Mobile,Address = @Address WHERE ContactID = @ContactID", param, commandType:=CommandType.Text)
MessageBox.Show("Updated Successfully")
End If
FillDataGridView()
Clear()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
oledbCon.Close()
End Try
End Sub
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
'contactId = Convert.ToInt32(dgvContact.CurrentRow.Cells(0).Value.ToString())
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
End Sub

'I created one update button to test sql for update running or not but the code below has an error "Data type mismatch in criteria expression"
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")

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", txtcontactid.Text.Trim())
oledbCon.Execute("UPDATE Contact SET Nme = '" & txtName.Text & "',Mobile = '" & txtMobile.Text & "',Address = '" & txtAddress.Text & "' WHERE ContactID = '" & txtcontactid.Text & "'", param, commandType:=CommandType.Text)
MessageBox.Show("Updated Successfully")
FillDataGridView()
End Using
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
oledbCon.Close()
End Try
End Sub

数据库访问数据类型

Answer 1

根据@ZoHas链接的答案！

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", txtcontactid.Text)
If String.IsNullOrEmpty(Me.txtcontactid.Text.Trim()) Then
oledbCon.Execute("INSERT INTO Contact (Nme,Mobile,Address) VALUES (@Nme,@Mobile,@Address)", param, commandType:=CommandType.Text)
MessageBox.Show("Saved Successfully")
Else
oledbCon.Execute("UPDATE Contact set Nme=@param1, Mobile=@param2, Address=@param3 where ContactID=@param4", New With {
Key .param1 = txtName.Text.Trim(),
Key .param2 = txtMobile.Text.Trim(),
Key .param3 = txtAddress.Text.Trim(),
Key .param4 = txtcontactid.Text}, commandType:=CommandType.Text)
MessageBox.Show("Updated Successfully")
End If
FillDataGridView()
Clear()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
oledbCon.Close()
End Try
End Sub