title: Join two data tables having common fields
body: <p>I have 2 <code>datatable</code> objects (<code>dt1</code>,<code>dt2</code>), which have a common filed called "File_Name". I want to create a <code>datatable</code> (<code>dtFinal</code>), combining the two datatables to be used in gridview of asp.net</p>
  
  <p>Each of the tables (<code>dt1</code>,<code>dt2</code>) can have overlapping column header names, meaning if dt1 has a header as "plant_code" <code>dt2</code> can also have the header as "plant_Code" but not mandatory.</p>
  
  <p>My objective is to have a common datatable (<code>dtFinal</code>) with all the headers from all the individual datatables.</p>
  
  <p>I tried something like this </p>
  
  <pre><code>  Dt1.PrimaryKey = New DataColumn() {Dt1.Columns(System.AppDomain.CurrentDomain.BaseDirectory &amp; "\resources\files\" &amp; "GRN" &amp; ".csv")}
    Dt2.PrimaryKey = New DataColumn() {Dt1.Columns(System.AppDomain.CurrentDomain.BaseDirectory &amp; "\resources\files\" &amp; "Payment Data" &amp; ".csv")}
    Dt1 = CsvToDataTable(System.AppDomain.CurrentDomain.BaseDirectory &amp; "\resources\files\" &amp; "GRN" &amp; ".csv")
    Dt2 = CsvToDataTable(System.AppDomain.CurrentDomain.BaseDirectory &amp; "\resources\files\" &amp; "Payment Data" &amp; ".csv")
  
  
    ds.Tables.Add(Dt1)
    ds.Tables.Add(Dt2)
  
    Dim drel As New DataRelation("EquiJoin", Dt2.Columns("File_Name"), Dt1.Columns("File_Name"))
  
    ds.Relations.Add(drel)
  
    Dim jt As New DataTable("Joinedtable")
    ds.Tables.Add(jt)
    For Each dr As DataRow In ds.Tables("Table1").Rows
  
        Dim parent As DataRow = dr.GetParentRow("EquiJoin")
        Dim current As DataRow = jt.NewRow()
        ' Just add all the columns' data in "dr" to the New table.
  
        For i As Integer = 0 To ds.Tables("Table1").Columns.Count - 1
            current(i) = dr(i)
        Next
        ' Add the column that is not present in the child, which is present in the parent.
        current("Dname") = parent("Dname")
        jt.Rows.Add(current)
    Next
  
    DtFinal = ds.Tables("Joinedtable")
  </code></pre>
  
  <p>Creating a <code>dataset</code> and a relation between dt1 and dt2 ...But this doesn't seem to work and giving me an error in line 1 <code>instance of object to be declared using New</code>. </p>
  
  <p>Has someone tried something like this in Vb ? Or can my code be edited to make it work. </p>
  
  <p>Further info: each datatable tb1 and tb2 have unique values in the File_Name column which is the first column. and hence can be selected as the primary key.</p>