How to use CopyToDataTable with two datatable queries in VB.NET

Question

How to use CopyToDataTable with two datatable queries in VB.NET?. should under Employee Information not appear column "GENDER" like the sign I marked warning red and under Gender Information do not appear for column "EMPLOYEE ID" and column "FULL NAME" like the sign I marked warning red . It should be that the total pages are 83 + 1 blank pages, so a total of 84 pages instead of 166+1 blank pages, so a total of 167 pages

Is there something wrong with my code?

Thanks

Private Sub GeneratePayslip()
        Dim dt As New DataTable()
        report.Clear()
        Dim query1 = "SELECT tblemployee.Empid AS [EMPLOYEE ID] ,tblemployee.Name AS [FULL NAME] FROM tblabsent INNER JOIN tblemployee ON tblabsent.EMPID = tblemployee.EMPID"
        Using adapter1 As New OleDbDataAdapter(query1, con)
            adapter1.Fill(dt)
        End Using
        Dim query2 = "SELECT tblemployee.GENDER FROM tblabsent INNER JOIN tblemployee ON tblabsent.EMPID = tblemployee.EMPID"
        Using adapter2 As New OleDbDataAdapter(query2, con)
            adapter2.Fill(dt)
        End Using
        For Each row As DataRow In dt.Rows
            report.AddHorizontalRule()
            report.AddLineBreak()
  report.AddLineBreak()
            report.AddHorizontalRule()
            report.AddLineBreak()
            report.AddString("<h3>Employee Information</h3>")
            Dim table1 = {row}.CopyToDataTable()
            report.AddDataTable(table1)
            report.AddLineBreak()
            report.AddHorizontalRule()
            report.AddLineBreak()
            report.AddString("<h3>Gender Information</h3>")
            table1.Columns.Clear()
            Dim table2 = {row}.CopyToDataTable()
            report.AddDataTable(table2)
            report.NewPage()
        Next row
    End Sub

Answer 1

It seems to me that you should not be populating one DataTable using both queries but rather populating one each. Then, instead of a For Each loop, use a For loop. You can then use the loop counter as an index into both DataTables to get two rows to print on the current page, e.g.

adapter1.Fill(table1)
adapter2.Fill(table2)

For i = 0 To table1.Rows.Count - 1
    Dim row1 = table1.Rows(i)
    Dim row2 = table2.Rows(i)
    Dim tableCopy1 = {row1}.CopyToDataTable()
    Dim tableCopy2 = {row2}.CopyToDataTable()

    '...
Next

This assumes that both tables will contain the same number of rows.