How to check duplicate records before inserting from one database to another ?

[ronelpisan]

I am able to check duplicate entry but using "WHERE name = textbox.text " clause within a windows form. Now, in this case the checking of duplicate entries is from one database to another. I want to check if all of those records from first database/table to be inserted are already exist in second database/table.

Below is the code for inserting records. Any idea for modification of my codes is highly appreciated.

Dim con1 As New OleDb.OleDbConnection("connection string here")
    Dim con2 As New OleDb.OleDbConnection("connection string here")

    'Create the data adapter with a SelectCommand using the first connection.
    Dim da As New OleDb.OleDbDataAdapter("SELECT Fullname FROM sampletable1 ", con1)

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

    da.InsertCommand.Parameters.Add("@Fullname", OleDb.OleDbType.VarChar, 50, "Fullname")

    '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()

[ChrisE]

well you will have to check Record by Record, or add the search to a List

something like this will check if a Record exists

 Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        Dim sDB As String = "E:\Adressen.mdb"
        Dim sCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                       "Data Source=" & sDB & ";"
        Dim Cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCon)
        Cn.Open()


        Dim sSql As String = "select count(*) from tbl_Student" & _
         " Where StudentName = @StudentName"
        ' MsgBox(sSql)

        Using cmd As New OleDbCommand(sSql, Cn)
            cmd.Parameters.AddWithValue("@StudentName", TextBox1.Text)

            Dim Result As String = CStr(cmd.ExecuteScalar)

            If CDbl(Result) = 0 Then
                'not found in Database, so Insert
                Using cmd1 As OleDbCommand = New OleDbCommand("INSERT INTO [tbl_Student] ([StudentName]) VALUES (@StudentName)", Cn)
                    cmd1.Parameters.AddWithValue("@StudentName", TextBox1.Text)
                    'Parameterize the query
                    With cmd.Parameters
                        .AddWithValue("@StudentName", TextBox1.Text)
                    End With

                    'is anything in Textbox to check ?
                    If TextBox1.Text = Nothing Then
                        MessageBox.Show("no Value to check !", "Error", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Error)
                        'nothing there so Exit
                        Exit Sub
                    Else
                        'Use ExecuteNonQuery to execute the command
                        cmd1.ExecuteNonQuery()
                        'Close the connection
                        'just show you Inserted the Record
                        MessageBox.Show("User :" & TextBox1.Text & " added", "Add User..", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Information)
                    End If
                    Cn.Close()
                End Using
            Else
                'show User is already in Database Table
                MessageBox.Show("Username -> " & TextBox1.Text & " <-  is already in Database", "Error", _
                                            MessageBoxButtons.OK, _
                                            MessageBoxIcon.Exclamation)
            End If
        End Using
        Cn.Close()
    End Sub

hth

[ronelpisan]

well you will have to check Record by Record, or add the search to a List

something like this will check if a Record exists

 Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        Dim sDB As String = "E:\Adressen.mdb"
        Dim sCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                       "Data Source=" & sDB & ";"
        Dim Cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCon)
        Cn.Open()


        Dim sSql As String = "select count(*) from tbl_Student" & _
         " Where StudentName = @StudentName"
        ' MsgBox(sSql)

        Using cmd As New OleDbCommand(sSql, Cn)
            cmd.Parameters.AddWithValue("@StudentName", TextBox1.Text)

            Dim Result As String = CStr(cmd.ExecuteScalar)

            If CDbl(Result) = 0 Then
                'not found in Database, so Insert
                Using cmd1 As OleDbCommand = New OleDbCommand("INSERT INTO [tbl_Student] ([StudentName]) VALUES (@StudentName)", Cn)
                    cmd1.Parameters.AddWithValue("@StudentName", TextBox1.Text)
                    'Parameterize the query
                    With cmd.Parameters
                        .AddWithValue("@StudentName", TextBox1.Text)
                    End With

                    'is anything in Textbox to check ?
                    If TextBox1.Text = Nothing Then
                        MessageBox.Show("no Value to check !", "Error", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Error)
                        'nothing there so Exit
                        Exit Sub
                    Else
                        'Use ExecuteNonQuery to execute the command
                        cmd1.ExecuteNonQuery()
                        'Close the connection
                        'just show you Inserted the Record
                        MessageBox.Show("User :" & TextBox1.Text & " added", "Add User..", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Information)
                    End If
                    Cn.Close()
                End Using
            Else
                'show User is already in Database Table
                MessageBox.Show("Username -> " & TextBox1.Text & " <-  is already in Database", "Error", _
                                            MessageBoxButtons.OK, _
                                            MessageBoxIcon.Exclamation)
            End If
        End Using
        Cn.Close()
    End Sub

hth

Thank you for that Sir but I do not want to use textbox 1.text since the value to be checked is not from textbox1 but from a database field/column. So how to replace textbox1 with column/field "fullname"? Do I have simply change textbox1.text with "fullname" or need to have add another line of code?

[kareninstructor]

An alternative is to use constraints that when a duplicate is inserted a known exception is thrown.

Dim dt = New DataTable()
dt.Columns.Add(New DataColumn() With {
                      .ColumnName = "Id",
                      .DataType = GetType(Integer),
                      .AutoIncrement = True,
                      .AutoIncrementSeed = 1
                  })
dt.Columns.Add(New DataColumn() With {
                      .ColumnName = "FirstName",
                      .DataType = GetType(String)
                  })
dt.Columns.Add(New DataColumn() With {
                      .ColumnName = "LastName",
                      .DataType = GetType(String)
                  })

dt.Constraints.Add(New UniqueConstraint({dt.Columns("FirstName"), dt.Columns("LastName")}))

dt.Rows.Add(Nothing, "Karen", "Payne")
dt.Rows.Add(Nothing, "Mary", "Payne")
dt.Rows.Add(Nothing, "Jim", "Payne")

Try
    dt.Rows.Add(Nothing, "Karen", "Payne")
Catch e1 As ConstraintException
    MessageBox.Show("Add denied, would create a duplication record")
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

[ChrisE]

Thank you for that Sir but I do not want to use textbox 1.text since the value to be checked is not from textbox1 but from a database field/column. So how to replace textbox1 with column/field "fullname"? Do I have simply change textbox1.text with "fullname" or need to have add another line of code?

I said ...

well you will have to check Record by Record, or add the search to a List

to create such a List execute a Query todo the Search in both Tables
like this to search for dupplicte 'ContactName' in both Tables.....

SELECT v.[CompanyName], v.[ContactName], Max(v.xtabloc) AS TableDiff
FROM (select [CompanyName], [ContactName], 'Table1' as xtabloc from Customers
  union all
  select [CompanyName], [ContactName], 'Table2' as xtabloc from Customers2
)  AS v
GROUP BY v.[CompanyName], v.[ContactName]
HAVING (((Count(1))=1));

and the result image


the query also returns in which Table

hth