Merge columns of different types from datatables into one larger datatable

Question

I have two seperate datatables, one of integers and one of strings. Both are 3x3 in size and I want to simply merge the two tables and show them in a datagridview so that I show a 3x6 datagridview.

Two separate datatables

And I want to put the two together to get the image below

Datatables joined

    Dim stringtable As New DataTable
    stringtable.Columns.Add("PK", GetType(Integer))
    stringtable.Columns.Add("Col1", GetType(Integer))
    stringtable.Columns.Add("Col2", GetType(Integer))
    stringtable.Columns.Add("Col3", GetType(Integer))

    stringtable.Rows.Add(1, 1, 1, 1)
    stringtable.Rows.Add(2, 2, 2, 2)
    stringtable.Rows.Add(3, 3, 3, 3)

    Dim primaryKey(1) As DataColumn
    primaryKey(0) = stringtable.Columns("Name")
    stringtable.PrimaryKey = primaryKey

    Dim Inttable As New DataTable
    Inttable.Columns.Add("PK", GetType(Integer))
    Inttable.Columns.Add("ColA", GetType(String))
    Inttable.Columns.Add("ColB", GetType(String))
    Inttable.Columns.Add("ColC", GetType(String))

    Inttable.Rows.Add(1, "A", "A", "A")
    Inttable.Rows.Add(2, "B", "B", "B")
    Inttable.Rows.Add(3, "C", "C", "C")

    primaryKey(0) = Inttable.Columns("Name")
    Inttable.PrimaryKey = primaryKey

    DataGridView2.DataSource = stringtable
    DataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
    DataGridView2.AllowUserToAddRows = False

    DataGridView1.DataSource = Inttable
    DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
    DataGridView1.AllowUserToAddRows = False


    ''This is where I can't figure out what do
    Dim mergedTable As New DataTable
    mergedTable = DataGridView2.DataSource
    mergedTable.Merge(Inttable, False, MissingSchemaAction.Add)

    DataGridView3.DataSource = mergedTable

I have tried a few things but cannot seem to get this right. Sometimes it populates a 6x6 datagridview with 12 blank cells and other times it keeps the values of one datatable but puts blanks for all the values of the second.

EDIT I've edited my code to reflect the addition of a primary key to the datatables but now for some the table populates with blank cells. Can someone tell me why the datatables wont just merge on the primary without leaving blanks

enter image description here

John Bustos · Accepted Answer · 2014-02-21 15:16:33Z

3

In order to merge 2 DataTables, you need a common column - You best / easiest bet, I would say, would be to add a primary key column to both and then you can simply merge them as you did in the code.

UPDATE:

Based upon your updated code, your issue is in how you're defining your primary key...

There are a couple of problems here:

You've defined your primary key column with the name PK, then you have primaryKey(0) = Inttable.Columns("Name")

This should be primaryKey(0) = Inttable.Columns("PK")

You define your primaryKey array as follows:

Dim primaryKey(1) As DataColumn

It should be:

Dim primaryKey(0) As DataColumn

Making it have only one value (Your way actually creates a 2-element array since it's a zero-based language for arrays).

Your changed code should look as follows:

Dim primaryKey(0) As DataColumn
primaryKey(0) = stringtable.Columns("PK")
stringtable.PrimaryKey = primaryKey

My preferred way of writing this, though, (although it will accomplish the exact same thing) would be as follows:

stringtable.PrimaryKey = {stringtable.Columns("PK")}

Just to help you along, here's some code I created to do what you're looking to do (without the DataGridView part)

Dim stringtable As New DataTable
stringtable.Columns.Add("PK", GetType(Integer))
stringtable.Columns.Add("Col1", GetType(Integer))
stringtable.Columns.Add("Col2", GetType(Integer))
stringtable.Columns.Add("Col3", GetType(Integer))

stringtable.Rows.Add(1, 1, 1, 1)
stringtable.Rows.Add(2, 2, 2, 2)
stringtable.Rows.Add(3, 3, 3, 3)

stringtable.PrimaryKey = {stringtable.Columns("PK")}

Dim Inttable As New DataTable
Inttable.Columns.Add("PK", GetType(Integer))
Inttable.Columns.Add("ColA", GetType(String))
Inttable.Columns.Add("ColB", GetType(String))
Inttable.Columns.Add("ColC", GetType(String))

Inttable.Rows.Add(1, "A", "A", "A")
Inttable.Rows.Add(2, "B", "B", "B")
Inttable.Rows.Add(3, "C", "C", "C")

Inttable.PrimaryKey = {Inttable.Columns("PK")}

stringtable.Merge(Inttable, False, MissingSchemaAction.AddWithKey)

Hope this helps and makes sense!!

edited Feb 21, 2014 at 15:16

answered Feb 20, 2014 at 19:45

John Bustos

19.7k18 gold badges102 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3334230 Over a year ago

had read about setting a column to be the primary but since there was no common column I hoped there might an easier way than all that data modification. If i'm right its not that easy to just add a column with looping through all the rows? these are example tables and my SQL queries produce much larger datagridviews

John Bustos Over a year ago

@user3334230, just updated my answer - Hope this explains it better / does the trick for you...

user3334230 Over a year ago

Thanks. I was being an idiot and never realised I was using Name not PK

archangel76 Over a year ago

I knew about merge, but I was missing the primary key part. Thx!

djv · Accepted Answer · 2014-02-21 23:58:41Z

Using LINQ

        Dim intTable As New DataTable
        Dim stringTable As New DataTable

        intTable.Columns.Add("Col1", GetType(Integer))
        intTable.Columns.Add("Col2", GetType(Integer))
        intTable.Columns.Add("Col3", GetType(Integer))

        intTable.Rows.Add(1, 1, 1)
        intTable.Rows.Add(2, 2, 2)
        intTable.Rows.Add(3, 3, 3)

        stringTable.Columns.Add("ColA", GetType(String))
        stringTable.Columns.Add("ColB", GetType(String))
        stringTable.Columns.Add("ColC", GetType(String))

        stringTable.Rows.Add("A", "A", "A")
        stringTable.Rows.Add("B", "B", "B")
        stringTable.Rows.Add("C", "C", "C")

        DataGridView2.DataSource = intTable
        DataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
        DataGridView2.AllowUserToAddRows = False

        DataGridView1.DataSource = stringTable
        DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
        DataGridView1.AllowUserToAddRows = False

        Dim indexColumn1 As New Data.DataColumn("Index", GetType(Integer))
        Dim indexColumn2 As New Data.DataColumn("Index", GetType(Integer))
        intTable.Columns.Add(indexColumn1)
        stringTable.Columns.Add(indexColumn2)
        For i As Integer = 0 To intTable.Rows.Count - 1
            intTable.Rows(i)("Index") = i
            stringTable.Rows(i)("Index") = i
        Next

        Dim resultTable = From i In intTable.AsEnumerable()
                          Join s In stringTable.AsEnumerable()
                          On s("Index") Equals i("Index")
                          Select {i("Col1"), i("Col2"), i("Col3"), s("ColA"), s("ColB"), s("ColC")}

        Dim dt As New DataTable()
        dt.Columns.AddRange({New DataColumn("Col1"), New DataColumn("Col2"), New DataColumn("Col3"),
                             New DataColumn("ColA"), New DataColumn("ColB"), New DataColumn("ColC")})
        For Each result In resultTable
            dt.LoadDataRow(result, True)
        Next

        DataGridView3.DataSource = dt
        DataGridView3.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
        DataGridView3.AllowUserToAddRows = False

Collectives™ on Stack Overflow

Merge columns of different types from datatables into one larger datatable

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related