how to update and insert in one button in dapper with database MS Access in VB.NET.

When double click datagridview then the button text changes to edit but the problem is that when the update appears a new record should not appear. The "Contactid" is the autonumber and primarykey type data in the access database and another one I want to ask if the "ContactId" is text and primary type data how to treat the code below?.

if I comment the if statement code and Sql "Insert into" and just run sql "update" status messagebox successfully but the database does not change. I'm using dapper version 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

database access datatype

CodePudding user response：

as per the answer from @ZoHas link!

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