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