Hi all, struggling with this one a bit and I'm fairly new to vb.net.
Basically the sub routine below is copying records from a table in one access db to another access db, the table structures are assumed to be the same in source and destination.
This works fine apart from it just tries to insert regardless of if a record already exists or not, so before I call the line ds_dest.Tables(0).Rows.Add(dr_2) I need to check if it exists, if it does then I want to skip that line, no update is necessary. I'm sure there is a perfectly simple way of doing it but I'm a Delphi man at heart.
Code:Private Sub CopyData(ByVal source_sql As String, ByVal dest_sql As String, ByVal display_text As String) Dim ds_source, ds_dest As DataSet Dim ole_da As OleDbDataAdapter Dim sql_da As OleDbDataAdapter Dim rows As Integer Dim dr_2 As DataRow Dim i As Integer Try ds_source = New DataSet ds_dest = New DataSet ole_da = New OleDbDataAdapter(dest_sql, strDest) sql_da = New OleDbDataAdapter(source_sql, strSource) sql_da.Fill(ds_source) ds_dest.Merge(ds_source) lblStatus.Text = "Copying " + CStr(ds_source.Tables(0).Rows.Count) + " records...please wait" pbProgress.Minimum = 0 pbProgress.Value = 0 pbProgress.Maximum = ds_source.Tables(0).Rows.Count For Each dr As DataRow In ds_source.Tables(0).Rows dr_2 = ds_dest.Tables(0).NewRow dr_2.ItemArray = dr.ItemArray ds_dest.Tables(0).Rows.Add(dr_2) i = i + 1 iTotal = iTotal + 1 pbProgress.Value = i Next If ds_dest.HasChanges Then rows = ole_da.Update(ds_dest.Tables(0)) lblStatus.Text = CStr(ds_source.Tables(0).Rows.Count) + " records copying completed successfully" If Not ds_source Is Nothing Then ds_source.Dispose() If Not ds_dest Is Nothing Then ds_dest.Dispose() If Not ole_da Is Nothing Then ole_da.Dispose() If Not sql_da Is Nothing Then sql_da.Dispose() Catch ex As Exception If Not ds_source Is Nothing Then ds_source.Dispose() If Not ds_dest Is Nothing Then ds_dest.Dispose() If Not ole_da Is Nothing Then ole_da.Dispose() If Not sql_da Is Nothing Then sql_da.Dispose() End Try End Sub


LinkBack URL
About LinkBacks
Reply With Quote