vb.net check if record exists in dataset

[icanhazburger]

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.

    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

[manwithnoname]

I know nothing about Delphi, but logic you need to follow is to test for the primary key (assuming it has one) of your current source record within the records of the destination table or to only select the records that do not exist in the destination table. You have a couple options:
1) Execute SQL to test for the primary key in the destination table for each source record
2) Import the primary keys of destination table into an array, then test this array for for each source record.
3) Personally I would link the table 'other' in one of the databases then write the SQL to just append the records that do not exist in the destination table, it is very inefficient to read records into data structures just for them to be discarded. (But I appreciate there could be some good reason to use code)

[ali73]

Hi,

Sorry cant actually post a code solution as I'm not on my machine just yet. But you could try

ds_dest .Tables(0).Select(FILTER_EXPRESSION)

Where FILTER_EXPRESSION is criteria to filter rows .. like where id = 10
you are already have a table to put data (ds_dest) and you know what your putting into it.

So before you perform your rows.add statement, you would have to check "does this already exist?".
If the expression comes back empty then you perform the insert.
If something is returned from the expression.. then you already have this data, skip the insert rows.add statment.

I wouldn't pull back the primary keys for the destination table into and array.. Theres no point holding this information if its already present (and more importantly upto date) in the database. You haven't mentioned how many rows you have, but what if your table grows and grows.. do you realy want to pull several thousand items into an array. Instead of this I would prefer to make small chatty SQL calls to the database such as "select count(*) from tblTable were colColumn = Value" and test if the value exists. "Dirty" reads (without record locking) would be great

There are a number of ways to pump data from one table to another.. I would use SQL myself.. but seeing as you have a progress Component referenced in your code, I assume you want to do this in this manner and give the end user a visual reference.

Also I assume you are catchng exceptions elsewhere in case there is a problem copying the data..and handle this gracefully. in your code when there is an exception you only dispose of the objects.. and not handle errors.

Hope this helps

[Garouda]

Hello, I am new on this forum so let me introduce myself. I've been working and living in Thailand for more than 7 years now. I'm a school administrator and so I started to write a comprehensive school management application in VB6 that i'm now upgrading to VB.net and SQL Server.
I am nort a pro, I got some foundation when I graduated, some years ago I would say...
I started to write some programs in VBA with Excel 5 around 1995...
About this thread, to have the key in your dataset, you have to fill the schema, like this

daAdapter.FillSchema(dsDataSet, SchemaType.Source, "Table")
        daAdapter.Fill(dsDataSet, "Table")

this enables you to find the key like this:

Dim tblToModify As DataTable
        ' Create a new instance of a DataTable
        tblToModify = dsDataSet.Tables("Table")
 Dim drCurrent As DataRow = tblToModify.NewRow()
 drCurrent = tblToModify.Rows.Find(aryData(0))

in my example, I used an array and the first column aryData(0) is the Key
Well, this is SQL Server, I am not sure it works with other databases.
Good Luck