adding a dataset inside a datareader in vb.net

Question

i hope i have asked the question title correctly. let me explain my issue - i am making a table through vb.net code (htmltablecell, htmltablerow..) no this table populates with an sql query and works perfectly. but inside this table in one tablecell, i need to add a dropdownlist which shall require a completely differernt query and shall run on its own on each row. so the code is as follows

Sql = "..." rd = ExecuteReader(SqlCnn, Sql)

        Dim newcounter As Integer = 0
        While rd.Read()
            newcounter += 1
            Dim tr As New HtmlTableRow
            Dim td As New HtmlTableCell
            Dim chkbox As New CheckBox
            Dim id As String = rd("id") & ""
            If id.Length = 0 Then id = "new" & newcounter
            id &= "_" & rd("col1")
            chkbox.Style.Add("width", "20%")
            chkbox.ID = "new_id_" & id
            chkbox.Text = rd("col1")

            tr.Style.Add("width", "100%")
            td.Style.Add("width", "10%")
            td.Style.Add("text-align", "left")
            td.Controls.Add(chkbox)
            tr.Cells.Add(td)

            td = New HtmlTableCell
            td.Style.Add("width", "30%")
            Dim ddl As New dropdownlist
            ddl.Style.Add("width", "80%")
            ddl.ID = "a1_" & id
            --???????
            td.Controls.Add(ddl)
            tr.Cells.Add(td)

the ??? in the code is where the dropdownlist shall get populated each time with its own datareader and while loop and query. how can i achieve this?

Answer 1

I would do this with 2 queries; 1 to get the ddl data and 1 to get the actual records. Get the ddl data (ddlDataReader) before the actual results, create the ddl object and add it to each row.

Something like this should work: (I don't do VB.Net, so you may need to fix things)

    Dim ddl As New dropdownlist
        ddl.Style.Add("width", "80%")
        ddl.ID = "a1_" & id
    While ddlDataReader.Read()
        ---add items to ddl
    end


    Dim newcounter As Integer = 0
    While rd.Read()
        newcounter += 1
        Dim tr As New HtmlTableRow
        Dim td As New HtmlTableCell
        Dim chkbox As New CheckBox
        Dim id As String = rd("id") & ""
        If id.Length = 0 Then id = "new" & newcounter
        id &= "_" & rd("col1")
        chkbox.Style.Add("width", "20%")
        chkbox.ID = "new_id_" & id
        chkbox.Text = rd("col1")

        tr.Style.Add("width", "100%")
        td.Style.Add("width", "10%")
        td.Style.Add("text-align", "left")
        td.Controls.Add(chkbox)
        tr.Cells.Add(td)

        td = New HtmlTableCell
        td.Style.Add("width", "30%")
        td.Controls.Add(ddl)
        tr.Cells.Add(td)

Another approach would be to load the ddl data into a datatable, so that you can loop through it multiple times, and then create the ddl with a unique Id when each row is being created.