How to ignore saving duplicate records before inserting to database and continue saving other non-duplicate records using VB.NET?

Question

1.00/5 (1 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:

VB

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 23-Jun-20 23:34pm

kyrons

Updated 25-Jun-20 1:12am

v3

Add a Solution

Comments

Richard MacCutchan 24-Jun-20 1:06am

Use the debugger to find out what value is returned by the SELECT clause.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Garth J Lancaster · Answer 1 · 2020-06-24T02:36:00

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

Posted 24-Jun-20 2:36am

Garth J Lancaster

CHill60 · Answer 2 · 2020-06-24T03:02:00

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

SQL

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

VB

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

VB

Dim CompQuery As String = "SELECT COUNT(*) FROM sampletable WHERE FullName = @FirstName AND LastName = @LastName"

or (more likely) your insert needs to change to

VB

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

VB

Dim da As New OleDb.OleDbDataAdapter("SELECT FirstName, LastName FROM table1 ", con1)

to include

Instead

Dim da As New OleDb.OleDbDataAdapter("SELECT FirstName, LastName FROM table1 ", con1)

to include

VB

WHERE FirstName = @FirstName AND LastName = @LastName"

- remembering to add those parameters to the select command

kgmmurugesh · Answer 3 · 2020-06-25T01:12:00

Solution 3

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

Posted 25-Jun-20 1:12am

kgmmurugesh