Join 2 tables, add first table as column headers for datagridview then add second table as rows below column headers

Question

I want to add columns for FACILITIES and then use their fac_id to find the their water production from another table to be displayed as a single row below the column headers.

Do While tableRetrieve.Read = True

table.Columns.Add(tableRetrieve("facility"), Type.GetType("System.String"))

Dim newDate As DateTime = DateTime.ParseExact(dtpFrom.Value, "MM/dd/yyyy h:m:s tt",
  System.Globalization.DateTimeFormatInfo.InvariantInfo)
Dim myDate As String = newDate.ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo)

If myDate = tableRetrieve("sentDate") Then

If tableRetrieve("prod_id") = tableRetrieve("fac_id") Then
table.Rows.Add(tableRetrieve("facility_produce"))
End If

End If

Loop

I can add the column headers but failed in rows.

Below is an image of the output. Column headers are displayed but the water production quantities are only displayed in one row. There should be water production quantities for each corresponding columns based on prod_id equals fac_id.

Sample of DataGridView

UPDATE:

This is what my code looks like now. This works well if there's only one row. the errors display if there's another row.

Do While tableRetrieve.Read = True

  table.Columns.Add(tableRetrieve("facility"), Type.GetType("System.String"))

  If tableRetrieve("facility_id") = tableRetrieve("fac_id") Then

    newDate = tableRetrieve("sentDate")

    If prevDate = newDate Then

    table.Rows(row)(col) = tableRetrieve("facility_produce")
    prevDate = newDate
    col += 1

    Else

    If row = 0 Then
    col += 1
    table.Rows.Add(tableRetrieve("sentDate"))
    table.Rows(row)(col) = tableRetrieve("facility_produce")
    prevDate = newDate
    col += 1
    Else
    col = 0
    row += 1
    table.Rows.Add(tableRetrieve("sentDate"))
    table.Rows(row)(col) = tableRetrieve("facility_produce")
    col += 1
    End If

    End If

  End If

Loop

dgvFacility.DataSource = table

Answer 1

For each loop you write tableRetrieve("facility_produce") in new row. You should write it to next column instead.

Dim col As Integer = 0    
Do While tableRetrieve.Read = True

table.Columns.Add(tableRetrieve("facility"), Type.GetType("System.String"))

Dim newDate As DateTime = DateTime.ParseExact(dtpFrom.Value, "MM/dd/yyyy h:m:s tt",
  System.Globalization.DateTimeFormatInfo.InvariantInfo)
Dim myDate As String = newDate.ToString("yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo)

If myDate = tableRetrieve("sentDate") Then

If tableRetrieve("prod_id") = tableRetrieve("fac_id") Then
  If col = 0 Then
     table.Rows.Add(tableRetrieve("facility_produce"))
  Else
     table.Rows(0)(col) = tableRetrieve("facility_produce")
  End If
  col += 1
End If

End If

Loop