How to check duplicate records before inserting from one database to another ?
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.
Code:
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()
Re: How to check duplicate records before inserting from one database to another ?
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
Code:
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
Re: How to check duplicate records before inserting from one database to another ?
Quote:
Originally Posted by
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
Code:
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?
Re: How to check duplicate records before inserting from one database to another ?
An alternative is to use constraints that when a duplicate is inserted a known exception is thrown.
Code:
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
1 Attachment(s)
Re: How to check duplicate records before inserting from one database to another ?
Quote:
Originally Posted by
ronelpisan
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 ...
Code:
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.....
Code:
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
Attachment 177629
the query also returns in which Table
hth
