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.

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

    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

Answer 1

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

Answer 2

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:

1. 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")

1. 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!!