Click here to Skip to main content
14,579,188 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have to import or copy the records from one database to another. My problem is it continuously insert records even if they are already existing. I want to control inserting duplicate records. I want them to be ignored and only those records that are not existing in database will be inserted. Any suggestion or modification in my code is highly appreciated. Thank you.

What I have tried:

Dim con1 As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= database1.accdb")
Dim con2 As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= database2.accdb")

con2.Open()
Dim CompQuery As String = "SELECT COUNT(*) FROM sampletable WHERE FirstName = @FirstName AND LastName = @LastName"
Dim compCommand As OleDbCommand = New OleDbCommand(CompQuery, con2)
compCommand.Parameters.AddWithValue("@FirstName", "FirstName")
compCommand.Parameters.AddWithValue("@LastName", "LastName")

If Convert.ToInt32(compCommand.ExecuteScalar()) > 0 Then
    'ignoring duplicate entries msgbox("Some records fail to save because it would create duplicate entries!")
    Exit Sub
Else

    'Create the data adapter with a SelectCommand using the first connection.
    Dim da As New OleDb.OleDbDataAdapter("SELECT Fullname, FirstName FROM table1 ", con1)
    'Add the InsertCommand with the second connection. 
    da.InsertCommand = New OleDb.OleDbCommand("INSERT INTO sampletable (Fullname, FirstName) VALUES (@FirstName, @LastName)", con2)
    'Add the insert parameters.                                                                                                 
    da.InsertCommand.Parameters.Add("@Fullname", OleDb.OleDbType.VarChar, 50, "FirstName")
    da.InsertCommand.Parameters.Add("@FirstName", OleDb.OleDbType.VarChar, 50, "LastName")
               'Keep the records in a state where they can be inserted into the destination table.
    da.AcceptChangesDuringFill = False
    Dim dt As New DataTable
    'Get the data from the source database.
    da.Fill(dt)
    'Save the data to the destination database.
    da.Update(dt)
    MsgBox("Data Added!")

    con1.Dispose()
    con2.Dispose()

End If
Posted
Updated 25-Jun-20 1:12am
v3
Comments
Richard MacCutchan 24-Jun-20 1:06am
   
Use the debugger to find out what value is returned by the SELECT clause.
Rate this:
Please Sign up or sign in to vote.

Solution 1

The standard way of achieving this would be to create an index on the database table with unique constraints - that way you cannot enter duplicates let alone have to filter them out somehow
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

To put some flesh around the comments and previous solution ..

There are two ways to do this, both involve making sure you don't create duplicates rather than dealing with them afterwards

1. Check to see if the item already exists before attempting to insert it.
This does not lend itself to bulk operations but would work with your code. This is the technique that you appear to be trying to apply, see below.

2. As suggested in Solution 1 - Constrain the database so that the data cannot be entered in the first place. There are links to how to do that on this Codeproject post - Stop insert duplicated records in database in C#[^] - the information applies to SQL so ignore the "C#" in the title.

I said above that you were trying to apply technique #1 with
Dim CompQuery As String = "SELECT COUNT(*) FROM sampletable WHERE FirstName = @FirstName AND LastName = @LastName"
Dim compCommand As OleDbCommand = New OleDbCommand(CompQuery, con2)
compCommand.Parameters.AddWithValue("@FirstName", "FirstName")
compCommand.Parameters.AddWithValue("@LastName", "LastName")
I'll ignore for now the fact that your person has the FirstName "Firstname" and the LastName "LastName" - you are explicitly looking for anyone who has FirstName = "Firstname" AND LastName = "LastName" on the database.

But when you insert with the command
da.InsertCommand = New OleDb.OleDbCommand("INSERT INTO sampletable (Fullname, FirstName) VALUES (@FirstName, @LastName)", con2)
You will end up with a data set that looks like
Fullname     FirstName    LastName
FirstName    NULL         LastName
So, either, your check to see if it already exists needs to change to
Dim CompQuery As String = "SELECT COUNT(*) FROM sampletable WHERE FullName = @FirstName AND LastName = @LastName"
or (more likely) your insert needs to change to
da.InsertCommand = New OleDb.OleDbCommand("INSERT INTO sampletable (FirstName, LastName) VALUES (@FirstName, @LastName)", con2)
or whatever.

tl;dr; - Make sure your column names match up with what you think you are checking.

EDIT after OP Comments

Instead of using the same dataadaptor for both the read and the write, try using 2 separate ones (transferring the data between the two). You appear to be trying to insert everything from table1 into sampletable (no WHERE statement on your select) but then trying to also pass in "FirstName" "LastName"). Separating them out might help you see what is going wrong in the debugger.
Alternatively change
Dim da As New OleDb.OleDbDataAdapter("SELECT FirstName, LastName FROM table1 ", con1)
to include
Dim da As New OleDb.OleDbDataAdapter("SELECT FirstName, LastName FROM table1 ", con1)
to include
WHERE FirstName = @FirstName AND LastName = @LastName"
- remembering to add those parameters to the select command
   
v2
Comments
kyrons 24-Jun-20 5:15am
   
Sorry that in this portion of code was mistaken. It was just typographical error. Sorry that it making you confused.

"But when you insert with the command

da.InsertCommand = New OleDb.OleDbCommand("INSERT INTO sampletable (Fullname, FirstName) VALUES (@FirstName, @LastName)", con2)"

Instead:

da.InsertCommand = New OleDb.OleDbCommand("INSERT INTO sampletable (FirstName, LastName) VALUES (@FirstName, @LastName)", con2)
kyrons 24-Jun-20 5:51am
   
This is the corrected codes:

Code:
Dim con1 As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= database1.accdb")
Dim con2 As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= database2.accdb")

con2.Open()
Dim CompQuery As String = "SELECT COUNT(*) FROM sampletable WHERE FirstName = @FirstName AND LastName = @LastName"
Dim compCommand As OleDbCommand = New OleDbCommand(CompQuery, con2)
compCommand.Parameters.AddWithValue("@FirstName", "FirstName")
compCommand.Parameters.AddWithValue("@LastName", "LastName")

If Convert.ToInt32(compCommand.ExecuteScalar()) > 0 Then
'ignoring duplicate entries msgbox("Some records fail to save because it would create duplicate
entries!")
Exit Sub
Else

'Create the data adapter with a SelectCommand using the first connection.
Dim da As New OleDb.OleDbDataAdapter("SELECT FirstName, LastName FROM table1 ", con1)
'Add the InsertCommand with the second connection.
da.InsertCommand = New OleDb.OleDbCommand("INSERT INTO sampletable (FirstName, LastName) VALUES (@FirstName, @LastName)", con2)
'Add the insert parameters.
da.InsertCommand.Parameters.Add("@FirstName", OleDb.OleDbType.VarChar, 50, "FirstName")
da.InsertCommand.Parameters.Add("@LastName", OleDb.OleDbType.VarChar, 50, "LastName")
'Keep the records in a state where they can be inserted into the destination table.
da.AcceptChangesDuringFill = False
Dim dt As New DataTable
'Get the data from the source database.
da.Fill(dt)
'Save the data to the destination database.
da.Update(dt)
MsgBox("Data Added!")

con1.Dispose()
con2.Dispose()

End If
CHill60 25-Jun-20 4:15am
   
I've updated my solution with some suggestions
Rate this:
Please Sign up or sign in to vote.

Solution 3

Before inserting data into a new table check whether the name already exists or not using reaad query. Hope this is usefull..
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100