title: How to inner join two already filled DataTables in VB.NET
body: <p>I marked Der Golem's answer as correct as it was the right answer for the specific problem I said I was having.</p>
  
  <p>Unfortunately, I failed to detect the fact that the records in datatable2 aren't unique, which obviously doesn't jive well with performing an "inner join" on the two tables for the result I was wanting.</p>
  
  <p>Setting a DataRelation would work if it was.</p>
  
  <hr>
  
  <p>I'm still new to VB.NET, so please bear with me.</p>
  
  <ul>
  <li>I have two data tables, each filled from different database servers.</li>
  <li>They both have three columns (for ease of testing, final program will have 50+).</li>
  <li>They both have a common data column that I want to inner join by ("OrderNum").</li>
  </ul>
  
  <p>There's a question here, but the "answer" doesn't work for me, nor the LINQ option below it:
  <a href="https://stackoverflow.com/questions/1262383/merging-2-data-tables-in-vb-net">Merging 2 data tables in vb.net</a></p>
  
  <p>This is my example code:</p>
  
  <pre><code>DB1 = New DatabaseConnectionSQL1
  DB1.OpenConn()
  DB2 = New DB_DatabaseConnectionSQL2
  DB2.OpenConn()
  
  Dim dtA As DataTable = New DataTable("DataTable1")
  Dim dtB As DataTable = New DataTable("DataTable2")
  Dim dtCombined As DataTable = New DataTable("CombinedDataTable")
  
  dtA.Columns.Add("Order", System.Type.GetType("System.String"))
  dtA.Columns.Add("Account_Number", System.Type.GetType("System.String"))
  dtA.Columns.Add("Account_Name", System.Type.GetType("System.String"))
  
  '"Order" = "Head_Order_Number"
  
  dtB.Columns.Add("Head_Order_Number", System.Type.GetType("System.String"))
  dtB.Columns.Add("Line_Number", System.Type.GetType("System.Int32"))
  dtB.Columns.Add("Model", System.Type.GetType("System.String"))
  
  dtA = DB1.GetDataTable(sQuery1)
  dtB = DB2.GetDataTable(sQuery2)
  
  'This doesn't work as it just appends the table
  'dtA.Merge(dtB, True)
  'I tried creating a DataSet and setting a Relation, but that kept failing
  'I've tried at least 10 different things here. I'm at my wit's end.
  
  dgvDataGrid.DataSource = dtCombined 
  dgvDataGrid.Refresh()
  
  DB1.CloseConn()
  DB2.CloseConn()
  </code></pre>
  
  <p>I noticed people in other places are suggesting using Linq. Even though I'm not familiar with it, I tried my best and kept failing.</p>
  
  <p>Table A (dtA):</p>
  
  <pre><code>Order | Account_Number | Account_Name
  10000 | 10000000000001 | BlahA
  20000 | 10000000000002 | BlahB
  30000 | 10000000000003 | BlahC
  </code></pre>
  
  <p>Table B (dtB):</p>
  
  <pre><code>Head_Order_Number| Line_Number | Model
  10000            | 00000000034 | MD35Z
  15000            | 00000000530 | MX25A
  25000            | 00000024535 | P231Y
  20000            | 00000027735 | A511L
  30000            | 00000000910 | M232C
  </code></pre>
  
  <p>Final table I want combining the two (dtCombined):</p>
  
  <pre><code>Order | Account_Number | Account_Name | Line_Number | Model
  10000 | 10000000000001 | BlahA        | 00000000034 | MD35Z
  20000 | 10000000000002 | BlahB        | 00000027735 | A511L
  30000 | 10000000000003 | BlahC        | 00000000910 | M232C
  </code></pre>
  
  <p>Any help would be greatly appreciated.</p>
  
  <hr>
  
  <p>I tried adding a DataRelation before and kept getting an error, but I wasn't setting something up properly. Now that I fixed that problem, I'm getting another error:</p>
  
  <p><strong>"System.ArgumentException: This constraint cannot be enabled as not all values have corresponding parent values."</strong></p>
  
  <pre><code>dt1 = New DataTable("DataTable1")
  dt1.Columns.Add("order_number", System.Type.GetType("System.String"))
  dt1.Columns.Add("account_name", System.Type.GetType("System.String"))
  
  dt2 = New DataTable("DataTable2")
  dt2.Columns.Add("head_order_number", System.Type.GetType("System.String"))
  dt2.Columns.Add("model", System.Type.GetType("System.String"))
  
  Conn1.ConnectionString = sConnString1
  Dim da1 As SqlDataAdapter = New SqlDataAdapter(sQuery1, Conn1)
  Conn1.Open()
  
  Conn2.ConnectionString = sConnString2
  Dim da2 As SqlDataAdapter = New SqlDataAdapter(sQuery2, Conn2)
  Conn2.Open()
  
  ds = New DataSet
  da1.Fill(ds, "DataTable1")
  da2.Fill(ds, "DataTable2")
  
  Dim dr As DataRelation = New DataRelation("Combined", _
      ds.Tables("DataTable1").Columns("OrderNo"), _
      ds.Tables("DataTable2").Columns("OrderNo"))
  ds.Relations.Add(dr)
  
  dgvDataGrid.DataSource = ds
  dgvDataGrid.Refresh()
  
  Conn1.Close()
  Conn2.Close()
  </code></pre>
  
  <hr>
  
  <p>That error seems to make sense, as DataTable1 has 1950 total rows, while DataTable2 has over 4000, but isn't that the point of the DataRelation? It effectively inner joins the two tables so the end result should be 1950 rows?</p>