none
How can I prevent saving a duplicate record in a Access Table.

    Question

  •  How can I prevent saving a duplicate record in a Access Table. The table is filled from 12 textboxes. The records can have all the same data for all the columns except for the date. if the date and the PN column are the same then the record would be duplicate. The windows form contains 12 textboxes with a binding navigator. I would like when I press the save button on the binding navigator a message box would pop up and give a warning that the record exist and cancel the save.

    Tuesday, April 4, 2017 12:30 PM

Answers

  • If you are saving one record at a time, consider writing a SELECT statement that returns a integer where the integer in this case is the count of rows matching records that match two fields (below I'm using CompanyName and ContactName)

    Then change your save method (click for the BindingNavigator) to first see if we have data then run the above query to check for duplicates, if duplicates back out, no duplicates add the record.

    Private Sub CustomersBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) _
        Handles CustomersBindingNavigatorSaveItem.Click
    
        If Not String.IsNullOrWhiteSpace(CompanyNameTextBox.Text) AndAlso Not String.IsNullOrWhiteSpace(ContactNameTextBox.Text) Then
            If CustomersTableAdapter.CheckForDuplicateRecordsByTwoColumns(CompanyNameTextBox.Text, ContactNameTextBox.Text) > 0 Then
                MessageBox.Show("Would create a duplicate")
            Else
                Me.Validate()
                Me.CustomersBindingSource.EndEdit()
                Me.TableAdapterManager.UpdateAll(Me.CustSmallDataSet)
            End If
        End If
    End Sub


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by VBShaper Wednesday, April 5, 2017 12:03 PM
    Wednesday, April 5, 2017 11:11 AM
    Moderator
  • Thank you all for answers. I got what I need now
     
    Try
                Dim f = From Database3 In Me.Database3DataSet.Table1
                        Where Database3.PN = PNTextBox.Text And Database3.Date_ = Date_DateTimePicker.Text
                If Not f.count = 0 Then
                    MsgBox("Record" & " " & PNTextBox.Text & " " & Date_DateTimePicker.Text & " " & "already exist")
                Else
                    Me.Validate()
                    Me.Table1BindingSource.EndEdit()
                    Me.Table1TableAdapter.Update(Me.Database3DataSet.Table1)
                    MsgBox("Record Stored")
                End If

    • Edited by VBShaper Wednesday, April 5, 2017 12:06 PM
    • Marked as answer by VBShaper Wednesday, April 5, 2017 12:06 PM
    Wednesday, April 5, 2017 12:03 PM

All replies

  • Make the PN and Date column a Primary Key in the Access database table. In your VB code you can use a Try...Catch block to handle the duplicate record error.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, April 4, 2017 12:35 PM
  • Make the PN and Date column a Primary Key in the Access database table. In your VB code you can use a Try...Catch block to handle the duplicate record error.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Can you please direct me to an example of a try catch code block that addresses the duplicate records.

    Tuesday, April 4, 2017 12:45 PM
  • Make the PN and Date column a Primary Key in the Access database table. In your VB code you can use a Try...Catch block to handle the duplicate record error.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Can you please direct me to an example of a try catch code block that addresses the duplicate records.

    The below code is in the BindingNavigatorSaveItem_Click Event. You can customize the message if you don't want to use the built in exception message:

    Private Sub TestTableBindingNavigatorSaveItem_Click(sender As System.Object, e As System.EventArgs) Handles TestTableBindingNavigatorSaveItem.Click Try Me.Validate() Me.TestTableBindingSource.EndEdit() Me.TableAdapterManager.UpdateAll(Me.TestTableDataSet1) Catch exDB As System.Data.OleDb.OleDbException MsgBox(exDB.Message) Catch ex As Exception MsgBox(ex.Message) End Try

    End Sub



    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, April 4, 2017 1:29 PM
  • Make the PN and Date column a Primary Key in the Access database table. In your VB code you can use a Try...Catch block to handle the duplicate record error.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Can you please direct me to an example of a try catch code block that addresses the duplicate records.


    I tried to make the P and Date_ column a primary key, but I receive an indexing error. The PN does not have to be unique. It only has to be unique per date. There is an ID column that is the primary at the moment, so there would be three Primary keys.
    Tuesday, April 4, 2017 2:45 PM
  • Make the PN and Date column a Primary Key in the Access database table. In your VB code you can use a Try...Catch block to handle the duplicate record error.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Can you please direct me to an example of a try catch code block that addresses the duplicate records.


    I tried to make the P and Date_ column a primary key, but I receive an indexing error. The PN does not have to be unique. It only has to be unique per date. There is an ID column that is the primary at the moment, so there would be three Primary keys.

    So instead of a Primary Key create an Index with the two columns that cannot be duplicated.

    From Microsoft Access (Table Design Mode):


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Tuesday, April 4, 2017 2:57 PM
  • In the same way as if you test if there is a table free in a restaurant. 

    Call (in this way simply do an execute scalar and see of it exist)

    If you want to reserve it, than make it and update the values as you are going (write)

    http://www.vb-tips.com/ExecuteScalarText.ASPX


    Success
    Cor

    Tuesday, April 4, 2017 4:15 PM
  • Hi VBShaper,

    According to your description, you want to detect if the data has already existed in the database when you insert data into database, I do a simple sample that you can refer to. If the saleorder are the same, then the record will be duplicate.

    Code sample:

    Public Class Form16
    
        Private Sub loaddate()
            Dim dt As New DataTable()
            Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\Database2.accdb; Persist Security Info=False;"
            Using conn As New OleDbConnection(strConn)
                Dim selectSql = "select * from OrderTable"
                Using com As New OleDbCommand(selectSql, conn)
                    Try
                        conn.Open()
                        Dim adapter As New OleDbDataAdapter(com)
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
    
                        conn.Close()
                    Catch ex As Exception
                        MessageBox.Show(ex.ToString())
                    End Try
                End Using
            End Using
        End Sub
    
        Private Sub Add_Click(sender As Object, e As EventArgs) Handles Add.Click
            If txtSale.Text = "" Then
                MessageBox.Show("Please enter all field", "error", MessageBoxButtons.YesNo, MessageBoxIcon.Error)
            Else
                Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\Database2.accdb; Persist Security Info=False;"
                Dim sql As String = "select count(*) from ordertable where saleorder='" & txtSale.Text & "'"
                Dim conn As OleDbConnection = New OleDbConnection(strConn)
                conn.Open()
    
                Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
                Using reader As OleDbDataReader = cmd.ExecuteReader()
                    If reader.HasRows Then
                        MessageBox.Show("This ordersale has already exist!", "Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)
                    Else
                        Dim cmd1 As OleDbCommand = New OleDbCommand("Insert into ordertable ( saleorder, productcode, qty,unitprice, totalprice) value ('" + txtSale.Text + "','" + txtPro.Text + "','" + txtqty.Text + "','" + txtunitPrice.Text + "','" + txttotalprice.Text + "')", conn)
                    End If
                End Using
                conn.Close()
            End If
        End Sub
        Private Sub Form16_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            loaddate()
            IsUserlandConnected()
        End Sub
    

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 5, 2017 6:37 AM
    Moderator
  • If you are saving one record at a time, consider writing a SELECT statement that returns a integer where the integer in this case is the count of rows matching records that match two fields (below I'm using CompanyName and ContactName)

    Then change your save method (click for the BindingNavigator) to first see if we have data then run the above query to check for duplicates, if duplicates back out, no duplicates add the record.

    Private Sub CustomersBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) _
        Handles CustomersBindingNavigatorSaveItem.Click
    
        If Not String.IsNullOrWhiteSpace(CompanyNameTextBox.Text) AndAlso Not String.IsNullOrWhiteSpace(ContactNameTextBox.Text) Then
            If CustomersTableAdapter.CheckForDuplicateRecordsByTwoColumns(CompanyNameTextBox.Text, ContactNameTextBox.Text) > 0 Then
                MessageBox.Show("Would create a duplicate")
            Else
                Me.Validate()
                Me.CustomersBindingSource.EndEdit()
                Me.TableAdapterManager.UpdateAll(Me.CustSmallDataSet)
            End If
        End If
    End Sub


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by VBShaper Wednesday, April 5, 2017 12:03 PM
    Wednesday, April 5, 2017 11:11 AM
    Moderator
  • Thank you all for answers. I got what I need now
     
    Try
                Dim f = From Database3 In Me.Database3DataSet.Table1
                        Where Database3.PN = PNTextBox.Text And Database3.Date_ = Date_DateTimePicker.Text
                If Not f.count = 0 Then
                    MsgBox("Record" & " " & PNTextBox.Text & " " & Date_DateTimePicker.Text & " " & "already exist")
                Else
                    Me.Validate()
                    Me.Table1BindingSource.EndEdit()
                    Me.Table1TableAdapter.Update(Me.Database3DataSet.Table1)
                    MsgBox("Record Stored")
                End If

    • Edited by VBShaper Wednesday, April 5, 2017 12:06 PM
    • Marked as answer by VBShaper Wednesday, April 5, 2017 12:06 PM
    Wednesday, April 5, 2017 12:03 PM
  • I hope you are not working in a multi-user environment, because other users could potentially add a new row and your code will still generate a duplicate row exception. The code you have is only checking the data you have returned to the app and not what is in the underlying database.

    Like I said, you need to handle any errors that occur at the time of actual update to the database because sooner or later it's going to happen.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, April 5, 2017 12:33 PM
  • @Karen,

    Nice, Would it not be better if you use this sample next time again to set a Transaction around it?

    And then the rollback before the messagebox show.

    (Also for those who want to use this in future)



    Success
    Cor


    Wednesday, April 5, 2017 3:43 PM
  • Hi Cor,

    The TableAdapter Update method has a transaction within it so no need for my code to have a transaction.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, April 5, 2017 4:59 PM
    Moderator
  • Hello Karen,

    I think you don't understand what I mean. 

    Private Sub CustomersBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) _ Handles CustomersBindingNavigatorSaveItem.Click Transaction.Start If Not String.IsNullOrWhiteSpace(CompanyNameTextBox.Text) AndAlso Not String.IsNullOrWhiteSpace(ContactNameTextBox.Text) Then If CustomersTableAdapter.CheckForDuplicateRecordsByTwoColumns(CompanyNameTextBox.Text, ContactNameTextBox.Text) > 0 Then Transaction.RollBack MessageBox.Show("Would create a duplicate") Else Me.Validate() Me.CustomersBindingSource.EndEdit() Me.TableAdapterManager.UpdateAll(Me.CustSmallDataSet) Transaction.Commit End If End If End Sub

     

    If now somebody else wants to add a record while the complete transaction is busy, the part which is in use is locked.

    That can never been done within a single update. 

    I made it in this message because I don't know what kind of database is in use and therefore it is incomplete.


    Success
    Cor




    • Edited by Cor LigthertMVP Wednesday, April 5, 2017 5:15 PM stupidity from me
    Wednesday, April 5, 2017 5:08 PM
  • @Karen,

    Nice, Would it not be better if you use this sample next time again to set a Transaction around it?

    And then the rollback before the messagebox show.

    (Also for those who want to use this in future)



    Success
    Cor


    There is only one table being updated Cor. A transaction is unnecessary.

    Also, the Access Database Engine performs its own page level (or row level) locking so you don't need to do anything else.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, April 5, 2017 5:37 PM
  • Paul,

    You wrote it yourself, "I hope you are not working in a multi-user environment, because other users could potentially add a new row and your code will still generate a duplicate row exception."

    That was meant for the OP, but that is also the case with the code from Karen.

    There is first investigated if the row is free and then it is added. In Karen's way it is only probably a chance from 1 of 10000 but there is a chance.

    The solution for that, make a transaction. That is not only for updates for more relates tables.

    (And now I understand why Karen wrote that the tableadapter has transactions in it. But that is for complete datasets)

    However, in the code from Karen are two complete different roundtrips.  


    Success
    Cor


    Wednesday, April 5, 2017 9:03 PM
  • Hi Cor,

    The Update method has it's own transaction if you were to look at the source in the generated code under the xsd.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, April 5, 2017 9:27 PM
    Moderator
  • Hi Cor,

    The Update method has it's own transaction if you were to look at the source in the generated code under the xsd.



    Karen,

    That I wrote, but your code exist not only from an update. It first looks if something is free.

    and does then a tableadapter update.

    For the later is the transaction not even needed, it exist from one table. 

    However, in the momentum that is looked if something is free and before the update somebody else on a complete other place in the world can do the same and then there is a conflict. 

    Therefore this has in my perception to be encapsulated in a transaction. (Which is in fact a pessimistic locking). 


    Success
    Cor



    Thursday, April 6, 2017 12:30 AM
  • Paul,

    You wrote it yourself, "I hope you are not working in a multi-user environment, because other users could potentially add a new row and your code will still generate a duplicate row exception."

    That was meant for the OP, but that is also the case with the code from Karen.

    There is first investigated if the row is free and then it is added. In Karen's way it is only probably a chance from 1 of 10000 but there is a chance.

    The solution for that, make a transaction. That is not only for updates for more relates tables.

    (And now I understand why Karen wrote that the tableadapter has transactions in it. But that is for complete datasets)

    However, in the code from Karen are two complete different roundtrips.  


    Success
    Cor



    Yeah, I'm afraid I don't agree with any of these ideas. It's simply adding additional overhead to the whole process. The only time it really matters is when the update occurs. Checking to see to see if the information already exists first or starting a transaction to create a lock condition is completely unnecessary.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, April 6, 2017 2:30 AM