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

Posted on 2009-04-21
Last Modified: 2013-11-27
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.
Question by:bwooden
    LVL 53

    Expert Comment

    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:

         ds4 = ds3.GetChanges()
    LVL 83

    Accepted Solution

    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 = ""

    Open in new window


    Author Comment


    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
                        dtTest.Rows(i).Item(5) = dtCounty.DefaultView.Item("NewCountyCode").Row(1)
                    End If
                    dtCounty.DefaultView.RowFilter = ""

    Open in new window

    LVL 83

    Expert Comment

    Try this code

    Dim tmpstr As String = dtCounty.DefaultView.Item(0).Item("NewCountyCode")

    Author Comment

    perfect!   Thanks Code Cruiser this helped a ton!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    In my previous two articles we discussed Binary Serialization ( and XML Serialization ( In this article we will try to know more about SOAP (Simple Object Acces…
    Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now