Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

VB.NET 2008 - Need help comparing data between two tables

I need help comparing values between two tables in VB.NET 2k8.  Basically I am wanting to write a quick conversion program for a database with over 1 million records.  I grabbed a very small subset of the database for testing purposes and have my code at least reading the data and displaying it on a datagrid. I just need to learn how to compare some values based on info in the two tables.  

I have went down the path of using datasets i am just not sure how to use the datasets to accomplish my task.

Here is what I have:

Table 1:  TestData (original data)
Fields:  First name, last name, city, state, zip, county

Table 2: County Lookup (basically a lookup cross reference table)
fields:  old county code, county name, new county code

Table 3: New Data (output table with new data)
Fields:  First name, last name, city, state, zip, county

basically i am reading a record from table 1, looking at its county code (example: 999888771) and cross referencing this to the County Lookup table 2 and looking up the old county code for a match and figuring out the new county code (example:  1) , then create a new record for that user with a new county code in Table 3

This is in VB.NET 2008 Express if it makes a difference.

Any help would be appreciated.
0
bwooden
Asked:
bwooden
  • 2
  • 2
1 Solution
 
DhaestCommented:
To compare two datasets and also to find out the difference create a third and a fourth dataset.
Merge this third dataset with the first dataset. Do an Acceptchanges(). Then, merge it with the
second dataset. Finally, do a GetChanges() on the third dataset and store it in the fourth dataset.

Following is a code snippet demonstrating the method to do so:

     ds3.Merge(ds1)
     ds3.AcceptChanges()
     ds3.Merge(ds2)
     ds4 = ds3.GetChanges()
0
 
CodeCruiserCommented:
I think you can use a for each loop to go through each record in the table1, then either use another dataset and use the RowFilter or query the db for each row(which may be expensive) and create a record in third table. Here is some example code
Dim dTable1, dTable2 as new DataTable
'populate both datatables
 
for i as integer = 0 to dTable1.Rows.Count - 1
    dTable2.DefaultView.RowFilter = "OldCountyCode=" & dTable1.Rows(i).item(5)
    if dTable2.DefaultView.Count = 1 then
       'insert new record
    end if
    dTable2.DefaultView.RowFilter = ""
next 

Open in new window

0
 
bwoodenAuthor Commented:
CodeCruiser,

So far this is working well.  I have the following code and seem to be having a problem with one piece of this puzzle.

My lookup table seems to be filtering down to 1 record which is good. Now how do I use that one record to make a comparison.  I was trying to go along the lines of using .defaultview to do something but that doesn't seem to be working.  

                    Dim tmpstr As String = dtCounty.DefaultView.Item("NewCountyCode").Row(1)
 
This code generates the following error:

"Conversion from string "NewCountyCode" to type 'Integer' is not valid."


I am not sure what to do at this point but we are getting  closer for sure.





Dim dtTest = dsMain.Tables("testdata")
Dim dtCounty = dsMain.Tables("CountyXWalk")
 
        For i As Integer = 0 To dtTest.Rows.Count - 1
 
                dtCounty.DefaultView.RowFilter = "OldCountyCode=" & CStr(dtTest.Rows(i).Item(5))
                If dtCounty.DefaultView.Count = 1 Then
                    MessageBox.Show(dtTest.Rows(i).Item(5))
                    dtTest.Rows(i).Item(5) = dtCounty.DefaultView.Item("NewCountyCode").Row(1)
                    MessageBox.Show(dtTest.Rows(i).Item(5))
                End If
                dtCounty.DefaultView.RowFilter = ""
 
        Next

Open in new window

0
 
CodeCruiserCommented:
Try this code

Dim tmpstr As String = dtCounty.DefaultView.Item(0).Item("NewCountyCode")
0
 
bwoodenAuthor Commented:
perfect!   Thanks Code Cruiser this helped a ton!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now