Merging 2 data tables in vb.net

Question

I have 2 DataTables in vb.net. Each is populated from it's own stored procedure. Table A contains a project number in the first column. Table B also contains the project number in the first column. Table A could have many records that have the same project number, but Table B will always have just one record in it per project number. I would like to append the data from Table B to every matching record in Table A. How would I do this?

Table A could look like this:

PROJECT#, QUANTITY

12345, 100

12345, 200

12345, 300

Table B could look like this:

PROJECT#, CUSTOMER

12345, ABC Inc.

I would like to merge the two to create something like this:

PROJECT#, QUANTITY, CUSTOMER

12345, 100, ABC Inc.

12345, 200, ABC Inc.

12345, 300, ABC Inc.

Please help!

KeeperOfTheSoul: Thanks for the response. I need to know how to join the tables in vb.net though. — stewdaddy5000, Aug 11 '09 at 19:34

score 4 · Accepted Answer · answered Aug 14 '09 at 14:29

This may help you, and may be semi-generic enough to be applied to other situations.

It's a function that will merge the data (as per your example) by passing in the two tables, two arrays containing the column names you require from each table, and the key used to join the tables.

There is an assumption that tblA is the driving table, with a lookup into tblB.

   Sub Main()

      Dim tbl As DataTable

      Dim colsA() As String = {"ProjectNo", "Quantity"}
      Dim colsB() As String = {"Customer"}
      Dim sKey As String = "ProjectNo"

      tbl = MergeData(tblA, tblB, colsA, colsB, sKey)

   End Sub

   Private Function MergeData(ByVal tblA As DataTable, ByVal tblB As DataTable, _
                              ByVal colsA() As String, ByVal colsB() As String, _
                              ByVal sKey As String) As DataTable

      Dim tbl As DataTable
      Dim col As DataColumn
      Dim sColumnName As String
      Dim row As DataRow
      Dim newRow As DataRow
      Dim dv As DataView

      tbl = New DataTable
      dv = tblB.DefaultView

      For Each sColumnName In colsA
         col = tblA.Columns(sColumnName)
         tbl.Columns.Add(New DataColumn(col.ColumnName, col.DataType))
      Next
      For Each sColumnName In colsB
         col = tblB.Columns(sColumnName)
         tbl.Columns.Add(New DataColumn(col.ColumnName, col.DataType))
      Next

      For Each row In tblA.Rows
         newRow = tbl.NewRow
         For Each sColumnName In colsA
            newRow(sColumnName) = row(sColumnName)
         Next

         dv.RowFilter = (sKey & " = " & row(sKey).ToString)
         If dv.Count = 1 Then
            For Each sColumnName In colsB
               newRow(sColumnName) = dv(0).Item(sColumnName)
            Next
         End If
         tbl.Rows.Add(newRow)
      Next

      Return tbl

   End Function

Just a small addition the RowFilter parameter needs to be in single quotes: dv.RowFilter = (sKey & " = '" & row(sKey).ToString() & "'") — Paxsentry, Feb 02 '16 at 14:09
@Paxsentry After 10 years, a recent upvote caused me to see this post again, and saw your comment. You would be right if the key field was a string, but in the OP's data he had numeric values. However, I did notice that the filter had a potential problem if the key column name had a space in it, so if I was doing this again today I would rewrite the line - dv.RowFilter = $"[{sKey}] = {row(sKey)}" — Bill, Oct 01 '19 at 23:08

score 3 · Answer 2 · answered Aug 11 '09 at 19:26

3

I'd advise using the LINQ methods, LINQ has a join operator that can handle this.

From q In quantities _
Join c In customers On q.project Equals c.project _
Select Quantity = q, Customer = c

answered Aug 11 '09 at 19:26

Chris Chilvers

6,429
3
32
53

KeeperOfTheSoul: Thanks for the response. I need to know how to join the tables in vb.net though. – stewdaddy5000 Aug 11 '09 at 19:35
LINQ can be used with plain old net objects and collection classes, it actually makes for a very powerful way to work with anything that implements IEnumerable(Of T) – Chris Chilvers Aug 11 '09 at 20:03

score 0 · Answer 3 · answered Aug 08 '16 at 17:08

anything AsEnumerable can be used in LINQ

var quantities = DataTable1().AsEnumerable(); var customers = DataTable2().AsEnumerable();

var result = from dtquanRow in quantities
        join dtcustomers in customers
        on dtquanRow.Field<string>("project") equals dtcustomers.Field<string>("project")

        select new
        {
            project = dtquanRow.Field<string>("project"),
            Quantity = dtquanRow.Field<string>("Quantity"),
           Customer = dtquanRow.Field<string>("Customer")
        };
moredetailed query and table to linq conversion

Its further explanation of Chris Chilvers
From q In quantities _ Join c In customers On q.project Equals c.project _ Select Quantity = q, Customer = c

Merging 2 data tables in vb.net

3 Answers3

Linked