Click here to Skip to main content
11,479,951 members (60,363 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
Hi,

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)
           ds.Relations.Add(r1)
 
           Dim r2 As New DataRelation(String.Empty, secondColumns, firstColumns, False)
           ds.Relations.Add(r2)
 
           '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.
           ResultDataTable.BeginLoadData()
           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
 
                                           _newRow(_colsource)=
 
                                       Next
 
                                   Next
 
                                   Dim thisRow As DataRow = ResultDataTable.LoadDataRow(a, True)
                                   'thisRow("Action") = "Update"

 
                                   ' ResultDataTable.LoadDataRow(parentrow.ItemArray, True)

                               End If
                           Next
                       End If
                   Next
               End If
 

           Next
 
           '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))
           'Next

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

 
           'If SecondDataTable Row not in FirstDataTable, Add to ResultDataTable.
           For Each parentrow As DataRow In ds.Tables(1).Rows
               'parentrow(0)
               Dim _found As Boolean = False
               For Each _row As DataRow In FirstDataTable.Rows
                   If parentrow(0) = _row(0) Then
                       _found = True
                   End If
               Next
 
               If Not _found Then
                   ResultDataTable.LoadDataRow(parentrow.ItemArray, True)
               End If
 
           Next
           ResultDataTable.EndLoadData()
       End Using
 
       Return ResultDataTable
   End Function
Posted 30-Jan-13 15:11pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Comments
jesureno at 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 at 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.
jesureno at 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 at 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.
jesureno at 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 at 31-Jan-13 12:49pm
   
You seriously want me to show you? Dim myList(numColumns) As Object
jesureno at 31-Jan-13 20:35pm
   
Thank you Sir Dave. Apology for my late reply.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi Jess,

Please try this

Dim a() As Object = {"Update"}
a.CopyTo(parentrow.ItemArray,0)
  Permalink  
Comments
jesureno at 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)

  Print Answers RSS
0 OriginalGriff 235
1 Sergey Alexandrovich Kryukov 215
2 Richard MacCutchan 100
3 F-ES Sitecore 85
4 DamithSL 65
0 Sergey Alexandrovich Kryukov 7,890
1 OriginalGriff 7,556
2 Sascha Lefèvre 3,064
3 Maciej Los 2,491
4 Richard Deeming 2,335


Advertise | Privacy | Mobile
Web03 | 2.8.150520.1 | Last Updated 30 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100