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.
bwoodenAsked:
Who is Participating?
 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.