[RESOLVED] How to join two or more DataTables together to display in DataGridView

[_cerberus_]

I am trying to find the correct way to join two datatables, or more, into a single datatable and use it as the datasource for a DataGridView. I have read through all sort of examples and posts, but nothing I've tried seems to be working. I'm sure I'm missing something, but not sure what.

I have three tables: dt_Record (25 columns), dt_ENDP (35 columns) and dt_ITSM (12 columns) and each of these come from different database servers. Because I'm not admin of these servers, I cannot join them in any way. For now I'm just trying to get the first two tables to join so I have a basis of HOW to do it. This joined table/DGV will be used for a report where the user can select which columns they want visible and exported to Excel.

Here is what I have tried per a solution here:

Private Sub Open_Report(sender As Object, e As EventArgs) Handles PrintToolStripButton.Click
    Try
        ds_Record = New DataSet
        ds_Record.Tables.Add(dt_Record)
        ds_Record.Tables.Add(dt_ENDP)
        Dim dr As DataRelation = New DataRelation("ENDP", ds_Record.Tables(0).Columns("Endpoint_Tag"), ds_Record.Tables(1).Columns("Computer_Name"))
        ds_Record.Relations.Add(dr)

        frmIT_Report.ShowDialog()

    Catch ex As Exception
        CustExErrorMsg(Me.Name, System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message)
    Finally
        If Not IsNothing(ds_Record) Then ds_Record.Dispose()
    End Try
End Sub

The other form that is opening, and has the DGV, has the following code:

Private Sub frmIT_Report_Load(sender As Object, e As EventArgs) Handles Me.Load
    Try
        With dgvResults
            .RowHeadersWidth = 12
            .DataSource = frmIT_Equip.ds_Record.Tables(0)
        End With
        
        txtResults.Text = CStr(dgvResults.Rows.GetRowCount(DataGridViewElementStates.None))
        
    Catch ex As Exception
        CustExErrorMsg(Name, System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message)
    End Try
End Sub

When the form opens, the DGV is only filled with the data from the dt_Records table, and nothing from the second table. OK, fine. Per another solution I found, I added the following, after adding the relation to the dataset, to see if that would bring in the data from the second table:

For Each row As DataRow In ds_Record.Tables(0).Rows
    Row.GetChildRows(dr)
Next

Nope, that still only produces the first table. So then I found another solution that required adding a column at the end of the first table:

ds_Record.Tables(0).Columns.Add("Build_Date", GetType(String), "Parent.Build_Date")

But that produces an error of "Cannot find relation 0" So then I went out on a limb with another solution using LINQ to join the tables: (I am not very familiar with LINQ, yet.)

Dim qry = From t1 In ds_Record.Tables(0).AsEnumerable() Join
    t2 In ds_Record.Tables(1).AsEnumerable On
    t1.Item("Endpoint_Tag") Equals t2.Item("Computer_Name")
          Select New With {
                Key .A = t1.Field(Of String)("Endpoint_Tag"),
                .B = t1.Field(Of Integer)("Class_ID"),
                .C = t2.Field(Of String)("Computer_Name"),
                .D = t2.Field(Of String)("Build_Date")}

Dim dt As New DataTable
For Each item In qry.ToList
    dt.Rows.Add(item.A, item.B, item.C, item.D)
Next
ds_Record.Tables.Add(dt)

But that produces error "Specified Cast in not allowed" But even if that did work, or the precious code, I don't want to have to cast almost 70 columns in my code - that just seem ludicrous.

On the surface this seems like it should be easy to solve. So what am I missing?

[_cerberus_]

I was able to get something worked out to "join" my three tables into a single DataTable. Granted it's probably not the most efficient, but it does work.

The code first creates a clone and copy of the "parent" DataTable. It then loops through the columns of each "child" DataTable and adds those columns. (I had to add a prefix for each table because the tables had identical names.)

It then loops through the rows of the new DataTable and uses a SELECT statement to capture the data and place it in it's appropriate column.

I'm sure this could get wrapped up into a function as well.

    Private Sub Open_Report(sender As Object, e As EventArgs) Handles PrintToolStripButton.Click
        Try
            Dim dt As New DataTable
            dt = dt_Record.Clone
            dt = dt_Record.Copy

            '** ITSM
            For Each col As DataColumn In dt_ITSM.Columns
                dt.Columns.Add(New DataColumn("ITSM_" + col.ColumnName, col.DataType))
            Next
            For Each row As DataRow In dt.Rows
                Dim val As DataRow = dt_ITSM.Select("asset_tag = '" + row.Item("Endpoint_Tag").ToString + "'").FirstOrDefault
                If Not val Is Nothing Then
                    For Each col As DataColumn In val.Table.Columns
                        row("ITSM_" + col.ColumnName) = val.Item(col.ColumnName)
                    Next
                End If
            Next

            '** Endpoint
            For Each col As DataColumn In dt_ENDP.Columns
                dt.Columns.Add(New DataColumn("ENDP_" + col.ColumnName, col.DataType))
            Next
            For Each row As DataRow In dt.Rows
                Dim val As DataRow = dt_ENDP.Select("Computer_Name = '" + row.Item("Endpoint_Tag").ToString + "'").FirstOrDefault
                If Not val Is Nothing Then
                    For Each col As DataColumn In val.Table.Columns
                        row("ENDP_" + col.ColumnName) = val.Item(col.ColumnName)
                    Next
                End If
            Next

            ds_Record = New DataSet
            ds_Record.Tables.Add(dt)

            frmIT_Report.ShowDialog()

        Catch ex As Exception
            CustExErrorMsg(Me.Name, System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message)
        Finally
            If Not IsNothing(ds_Record) Then ds_Record.Dispose()
        End Try
    End Sub