Stack Exchange
How to fix my looping 2 datasets in nested for loops
I need help with this nested forloop to display 2 database in each column of html table.
see code below that i have tried to implement
Protected Sub imgPaperDiary_Click(ByVal sender As Object, ByVal args As EventArgs) Handles imgPaperDiary.Click
    HttpContext.Current.Response.Clear()
    HttpContext.Current.Response.ContentType = "application/msword"
    HttpContext.Current.Response.ContentEncoding = System.Text.UnicodeEncoding.UTF8
    HttpContext.Current.Response.Charset = "utf-8"
    Response.AddHeader("content-disposition", "attachment; filename=paperdiary.doc")
    Response.Write("<html>")
    Response.Write("<head>")

    Response.Write("<div align='center'>")

    ' start building monday table
    Response.Write("<p><strong>Monday</strong></p>")
    Response.Write("<table style='width:100%' border='1' bordercolor='black' cellspacing='0'>")
    Response.Write("<tr>")
    Response.Write("<td width='50%'><strong>Morning</strong></td>")
    Response.Write("<td width='50%'><strong>Afternoon</strong></td>")
    Response.Write("</tr>")

    Dim mondaydsAM As DataSet
    mondaydsAM = Conn.dataSet("SELECT MWFSlotNo + ' ' + Firstname + ' ' + Surname AS Patient FROM tbl_Patients WHERE Days LIKE '%Mon%' AND Centre LIKE 'JCUH' AND Shift LIKE 'AM' ORDER BY CAST(MWFSlotNo AS int)", "Ruds")

    Dim mondaydsPM As DataSet
    mondaydsPM = Conn.dataSet("SELECT MWFSlotNo + ' ' + Firstname + ' ' + Surname AS Patient FROM tbl_Patients WHERE Days LIKE '%Mon%' AND Centre LIKE 'JCUH' AND Shift LIKE 'PM' ORDER BY CAST(MWFSlotNo AS int)", "Ruds")

    For Each dr As DataRow In mondaydsAM.Tables(0).Rows
        Response.Write("<tr>")
        Response.Write("<td width='50%'>" & dr.Item(0).ToString & "</td>")
        For Each dp As DataRow In mondaydsPM.Tables(0).Rows

            Response.Write("<td width='50%'>" & dp.Item(0).ToString & "</td>")

        Next

    Next
    Response.Write("</tr>")

    Response.Write("</table>")

    ' start building tuesday table

    Response.Write("</div>")
    Response.Write("</body>")
    Response.Write("</html>")
    HttpContext.Current.Response.End()
    HttpContext.Current.Response.Flush()

End Sub


Can you give an example of what your output should be. Also, your query uses like quite a few times without providing for any form of wildcard search. In those instances you should simply use = (example: AND Shift = 'PM')

I would guess that you only need a single Select statement. You just need to decide the Order By clause for your needs. No nested For.

Thank you @Mary for your comment, I have tried to use one select statement but it is not having the desired effect I have tried this SQL SELECT MWFSlotNo + ' ' + Firstname + ' ' + Surname AS Patient FROM tbl_Patients WHERE Days LIKE 'Mon%' AND Centre LIKE 'JCUH' AND Shift in('AM','PM') ORDER BY CAST(MWFSlotNo AS int)

I have managed to crack it, please code below 
HttpContext.Current.Response.Clear()
HttpContext.Current.Response.ContentType = "application/msword"
HttpContext.Current.Response.ContentEncoding = System.Text.UnicodeEncoding.UTF8
HttpContext.Current.Response.Charset = "utf-8"
Response.AddHeader("content-disposition", "attachment; filename=paperdiary.doc")
Response.Write("<html>")
Response.Write("<head>")

Response.Write("<div align='center'>")

' start building monday table
Response.Write("<p><strong>Monday</strong></p>")
Response.Write("<table style='width:100%' border='1' bordercolor='black' 
cellspacing='0'>")
Response.Write("<tr>")        
Response.Write("<th width='50%'><strong>Morning</strong></th>")
Response.Write("<th width='50%'><strong>Afternoon</strong></th>")
Response.Write("</tr>")

Dim mondaydsAM As Dataset
mondaydsAM = Conn.dataSet("SELECT MWFSlotNo + ' ' + Firstname + ' ' + Surname AS 
Patient FROM tbl_Patients WHERE Days LIKE '%Mon%' AND Centre = 'JCUH' AND Shift ='AM' 
ORDER BY CAST(MWFSlotNo AS int)", "Ruds")

Dim mondaydsPM As DataSet
mondaydsPM = Conn.dataSet("SELECT MWFSlotNo + ' ' + Firstname + ' ' + Surname AS 
Patient FROM tbl_Patients WHERE Days LIKE 'Mon%' AND Centre = 'JCUH' AND Shift = 'PM' 
ORDER BY CAST(MWFSlotNo AS int)", "Ruds")

Dim AMCount As Integer = mondaydsAM.Tables(0).Rows.Count
Dim PMCount As Integer = mondaydsPM.Tables(0).Rows.Count

    For i As Integer = 0 To 20
        Response.Write("<tr>")

        If i < AMCount Then


            ' If mondaydsAM.Tables(0).Rows(i).Item(0).ToString <> "" Then
            Response.Write("<td width='50%'>")
            Response.Write(mondaydsAM.Tables(0).Rows(i).Item(0).ToString)
            Response.Write("</td>")
        Else
            Response.Write("<td width='50%'>")
            Response.Write(i + 1)
            Response.Write("</td>")
        End If

        If i < PMCount Then
            'If mondaydsPM.Tables(0).Rows(i).Item(0).ToString <> "" Then

            Response.Write("<td width='50%'>")
            Response.Write(mondaydsPM.Tables(0).Rows(i).Item(0).ToString)
            Response.Write("</td>")
        Else
            Response.Write("<td width='50%'>")
            Response.Write(i + 1)
            Response.Write("</td>")
        End If

        Response.Write("</tr>")

    Next

    Response.Write("</table>")

 Response.Write("</div>")
 Response.Write("</body>")
 Response.Write("</html>")

 HttpContext.Current.Response.End()
 HttpContext.Current.Response.Flush()


With 50% widths, I'm assuming you want Am on the Left and Pm on the right...
Change this part
    For Each dr As DataRow In mondaydsAM.Tables(0).Rows
        Response.Write("<tr>")
        Response.Write("<td width='50%'>" & dr.Item(0).ToString & "</td>")
        For Each dp As DataRow In mondaydsPM.Tables(0).Rows

            Response.Write("<td width='50%'>" & dp.Item(0).ToString & "</td>")

        Next

    Next
    Response.Write("</tr>")

to this...
    Response.Write("<tr>")
    Response.Write("<td width='50%'>")
    For Each dr As DataRow In mondaydsAM.Tables(0).Rows
        Response.Write(dr.Item(0).ToString)
    Next
    Response.Write("</td>")

    Response.Write("<td width='50%'>")
    For Each dp As DataRow In mondaydsPM.Tables(0).Rows
        Response.Write(dp.Item(0).ToString)
    Next
    Response.Write("</td>")
    Response.Write("</tr>")


Your code is outputting all database records into 2 td cells, I need to display all records in each row, it will be AM patients on one side and PM patients on the other side, you code display 21 patients in on td cell for AM and 21 patients on the other cell for PM

I know, I wasn't sure exactly what output you expected, but hoped it would nudge you in the right direction.
