Click here to Skip to main content
14,877,782 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:

I would like to add new column to existing datatable, I'm comparing to two datatable and give the result to resultdatatable. I would like to add new column to resultdatatable based on my comparison result in two datatables.
Here's the code I made. I hope and thank your in advance for your help.

Public Function getDifferentRecords(FirstDataTable As DataTable, SecondDataTable As DataTable) As DataTable
       'Create Empty Table
       Dim ResultDataTable As New DataTable("ResultDataTable")
       ResultDataTable.Columns.Add("Action", GetType(String))

       'use a Dataset to make use of a DataRelation object
       Using ds As New DataSet()
           'Add tables
           ds.Tables.AddRange(New DataTable() {FirstDataTable.Copy(), SecondDataTable.Copy()})

           'Get Columns for DataRelation
           Dim firstColumns As DataColumn() = New DataColumn(ds.Tables(0).Columns.Count - 1) {}
           Dim i As Integer = 0
           While i < firstColumns.Length
               firstColumns(i) = ds.Tables(0).Columns(i)
               System.Math.Max(System.Threading.Interlocked.Increment(i), i - 1)
           End While

           Dim secondColumns As DataColumn() = New DataColumn(ds.Tables(1).Columns.Count - 1) {}
           i = 0
           While i < secondColumns.Length
               secondColumns(i) = ds.Tables(1).Columns(i)
               System.Math.Max(System.Threading.Interlocked.Increment(i), i - 1)
           End While

           'Create DataRelation
           Dim r1 As New DataRelation(String.Empty, firstColumns, secondColumns, False)

           Dim r2 As New DataRelation(String.Empty, secondColumns, firstColumns, False)

           'Create columns for return table
           i = 0
           While i < FirstDataTable.Columns.Count
               ResultDataTable.Columns.Add(FirstDataTable.Columns(i).ColumnName, FirstDataTable.Columns(i).DataType)
               System.Math.Max(System.Threading.Interlocked.Increment(i), i - 1)
           End While

           'If FirstDataTable Row not in SecondDataTable, Add to ResultDataTable.
           For Each parentrow As DataRow In ds.Tables(0).Rows
               Dim childrows As DataRow() = parentrow.GetChildRows(r1)
               'If childrows Is Nothing OrElse childrows.Length = 0 Then
               '    ResultDataTable.LoadDataRow(parentrow.ItemArray, True)
               'End If

               If childrows Is Nothing Then
                   ResultDataTable.LoadDataRow(parentrow.ItemArray, True)
               ElseIf childrows.Length = 0 Then
                   '  ResultDataTable.LoadDataRow(parentrow.ItemArray, True)
                   Dim id2 = parentrow

                   For Each _row As DataRow In SecondDataTable.Rows
                       If (_row(0) = id2(0)) Then
                           For Each _col As DataColumn In FirstDataTable.Columns
                               'get specified column
                               If _row(_col.ColumnName) <> parentrow(_col.ColumnName) Then
                                   'display field value in 2nd table
                                   Dim strColumn = parentrow(_col.ColumnName).ToString()

                                   Dim newRow = ResultDataTable.NewRow()

                                   For Each _newRow In newRow.ItemArray

                                       For Each _colSource As DataColumn In ResultDataTable.Columns




                                   Dim thisRow As DataRow = ResultDataTable.LoadDataRow(a, True)
                                   'thisRow("Action") = "Update"

                                   ' ResultDataTable.LoadDataRow(parentrow.ItemArray, True)

                               End If
                       End If
               End If


           'Dim dictTable1ID As New Dictionary(Of Integer, String)
           'Dim dictTable2ID As New Dictionary(Of Integer, String)

           'For Each tablerow1 As DataRow In ds.Tables(0).Rows
           '    dictTable1ID.Add(tablerow1(0), tablerow1(1))

           'For Each tablerow2 As DataRow In ds.Tables(1).Rows
           '    dictTable2ID.Add(tablerow2(0), tablerow2(1))

           'If SecondDataTable Row not in FirstDataTable, Add to ResultDataTable.
           For Each parentrow As DataRow In ds.Tables(1).Rows
               Dim _found As Boolean = False
               For Each _row As DataRow In FirstDataTable.Rows
                   If parentrow(0) = _row(0) Then
                       _found = True
                   End If

               If Not _found Then
                   ResultDataTable.LoadDataRow(parentrow.ItemArray, True)
               End If

       End Using

       Return ResultDataTable
   End Function

Silver Lightning 30-Jan-13 20:49pm
Thank you sir,but that is not correct. I already adding the data to ResultDataTabe, I only wanted is to add another column value in ResultDatableTable which is not existing to firstdatatable and seconddatatable. I mean how can I play around ItemArray to add new column in ResultDataTable?
Dave Kreskowiak 30-Jan-13 21:03pm
OK. That doesn't change anything. All you're asking is how to add a column. The link I posted describes exactly that.

What value you put in each row of that column is entirely up to you and your requirements, not a word of which you mentioned anything about.
Silver Lightning 30-Jan-13 21:25pm
Ok Sir Dave, thank you, I want is to add column value in ResultDataTable which has already a column of ID, NAME and DESCRIPTION which has already a data inside them. I want to add new column name ACTION, where I can tag or indicate a value of EDIT, DELETE or UPDATE.
How could I add a column to ResultDataTAble on this sample code below:

ResultDataTable.LoadDataRow(parentrow.ItemArray, True)

Note: parentrow has a value of ID, NAME and DESCRIPTION.
I want to add new column named ACTION to ResultDataTable.
Thank you in advance sir.
Dave Kreskowiak 30-Jan-13 23:04pm
OK, my brain just exploded looking at that code. It makes no sense at all. For instance, why are you calling Math.Max at all when you don't even use or store its return value?? Those lines do absolutely nothing that way you've written them!

As for adding a value, you've already added the column so, after you call the LoadDataRow method, you just index the row, then column you want to change the value for. They're just arrays in the datatable, and you're already doing something very similar in other parts of your code, so I don't see what you're having a problem with!

OR you could just create your own "itemarray" and populate it with the data from the source row and just add the value for your additional column, then call LoadDataRow using this new array.
Silver Lightning 30-Jan-13 23:44pm
Ok Sir Dave, can you give me a sample code on how could I create my own itemarray based on ResultDataTable and Create it to a new one? Thank you Sir =)
Dave Kreskowiak 31-Jan-13 12:49pm
You seriously want me to show you? Dim myList(numColumns) As Object
Silver Lightning 31-Jan-13 20:35pm
Thank you Sir Dave. Apology for my late reply.
Hi Jess,

Please try this

Dim a() As Object = {"Update"}
Silver Lightning 31-Jan-13 0:04am
Thank you agent sales. but that is not correct.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900