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

Question

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

Have you step-debugged code? What happens when you expect code to branch to UPDATE? How do you expect contactID to get a value from existing record? I see nothing in code to accomplish that. — June7, Oct 02 '22 at 16:13
I would almost have a variable boolean flag I set when entering edit mode, then base my Insert/Update condition off that — Hursey, Oct 02 '22 at 19:37
one possibility is Name is a reserved word in access. try either @[Name] or change the name in the database to ContactName — mazoula, Oct 03 '22 at 05:01
@mazoula , I've tried changing "name" to "nme" still a problem for sql update — roy, Oct 03 '22 at 13:33
@June7 , thanks for the reply from you. so if I double click datagridview then automatically the button becomes edit and it should be the sql update command but after I learned the sql command that used to run into insert with the message `MessageBox.Show("Saved Successfully")` I've also updated the code in the post — roy, Oct 03 '22 at 13:41
@Hursey , how and please give the example you are referring to? — roy, Oct 03 '22 at 13:42
Yeah, not 100% sure what you asking for. From the code you've posted you already know how to declare a variable, how to set it's value, how to check a variable value and write an if statement. What more do you need? — Hursey, Oct 03 '22 at 19:30
@Hursey , if I comment the if statement code and Sql "Insert into" and just run sql "update" status messagebox Updated successfully but the database does not change.so here is a difference in the implementation of dapper code for access databases — roy, Oct 04 '22 at 02:42
Assuming your insert code works, perhaps the idea is to strip this issue back to the core issue of the update not working. Get rid of all this double click, if this insert else update. Have a button for debugging, all it does is execute an update command. Get that working first, then should be able to apply to you actual code above — Hursey, Oct 04 '22 at 03:21
That said, if the above is running without exception, that would suggest to me that the value contactId is not what you expect and no records are being matched for update — Hursey, Oct 04 '22 at 03:23
And as an aside, this was mentioned in your previous question. Implement your DB connection with Using clauses. DB Connections implement IDisposable, so as a general rule (there are exceptions) should should have a comparatively short lifespan and be created, used and destroyed on demand. Not a global connection for the life of the app, this will cause you other issues — Hursey, Oct 04 '22 at 03:27
@Hursey, `Assuming your insert code works, perhaps the idea is to strip this issue back to the core issue of the update not working. Get rid of all this double click, if this insert else update. Have a button for debugging, all it does is execute an update command. Get that working first, then should be able to apply to you actual code above` and `And as an aside, this was mentioned in your previous question. Implement your DB connection with Using clauses`. thank you for your advice and support. I created one update button to test sql for update running or not — roy, Oct 04 '22 at 09:41
@Hursey , the code above has an error "Data type mismatch in criteria expression" — roy, Oct 04 '22 at 09:42

score 1 · Accepted Answer · answered Oct 04 '22 at 13:52

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

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

1 Answers1