Datareader - Loop through results in VB

.NET
1

Hi Folks,

I would like to loop through my result set and generate a menu structure like this:

page
page
subpage
subpage
page
page

So far I have the following code but it doesn’t generate the menu, can anyone please help?

While reader.Read()
’ set title
sitetitle = reader.Item(“siteName”)

    mydata &= reader.Item("pagetitle") 
   ' check subtitle exists, if so, build menu
	if not IsDBNull(reader.Item("subtitle")) 
	mydata = "<ul>"
	mydata &= "<li>" & reader.Item("subtitle") & "</li>"
	mydata &= "</ul>"
	end if

End while

Qeury and result set below:

sql:

SELECT subpages.subpageid, pages.pageid, sites.sitename, sites.siteid, pages.siteid,pages.pagetitle, subPages.subtitle FROM pages LEFT JOIN sites ON pages.siteid = sites.siteid LEFT JOIN subpages ON subpages.subpageid= pages.pageid WHERE sites.siteID = 1 ORDER BY sites.siteid, pages.pageid, subpages.subpageid ASC

results:

subpageid | pageid | siteid | siteid | pagetitle | subtitle

NULL 1 SCHS 1 1 Sandwell Community Healthcare Services NULL
NULL 2 SCHS 1 1 About Us NULL
NULL 3 SCHS 1 1 Your Services NULL
4 4 SCHS 1 1 Equality and Diversity Team at SCHS 1111
4 4 SCHS 1 1 Equality and Diversity Team at SCHS 2222
4 4 SCHS 1 1 Equality and Diversity Team at SCHS 333
4 4 SCHS 1 1 Equality and Diversity Team at SCHS 44444
NULL 5 SCHS 1 1 Single Equality Scheme NULL
NULL 6 SCHS 1 1 Diversity Strands NULL
NULL 7 SCHS 1 1 Equality Impact Assessments NULL
NULL 8 SCHS 1 1 Quality and Safety Committee NULL
NULL 9 SCHS 1 1 Contact Us NULL

2

Using the query results, the menu should look like:

Sandwell Community Healthcare Services
About Us
Your Services
Equality and Diversity Team at SCHS
1111
2222
333
44444
Single Equality Scheme
Diversity Strands
Equality Impact Assessments
Quality and Safety Committee
Contact Us

Can anyone help?

3

Using the query results, the menu should look like:

<ul>
<li>Sandwell Community Healthcare Services </li>
<li>About Us</li>
<li>Your Services</li>
<li>Equality and Diversity Team at SCHS</li><ul>
<li>1111</li>
<li> 2222</li>
<li> 333</li>
<li> 44444</li></ul>
<li>Single Equality Scheme</li>
<li>Diversity Strands</li>
<li>Equality Impact Assessments</li>
<li>Quality and Safety Committee</li>
<li>Contact Us</li>
</ul>

Can anyone help?

4

Could I loop through the subtitles that are not null?

Something like this?

mydata = "&lt;ul&gt;"

While reader.Read()
’ set title
sitetitle = reader.Item(“siteName”)

' if subtitle exists then output 
if not IsDBNull(reader.Item("subtitle"))  then

'loop through rows that have subtitle?
mydata &= "&lt;ul&gt;"
While not IsDBNull(reader.Item("subtitle"))
mydata &= "&lt;li&gt;" & reader.Item("subtitle")  & "&lt;/li&gt;"
end while 
mydata &= "&lt;/ul&gt;"

else
'output pagetitle 
mydata &= "&lt;li&gt;" & reader.Item("pagetitle") & "&lt;/li&gt;"

end if

End while
mydata &= “</ul>”

5

anyone?

6

Here is my other attempt, my code below generates the following menu, but the pagetitle is repeated with every subtitle. Can anyone please help?

DO I need to use a loop to loop through each subtitle, and then concatenate it to the appropriate pagetitle?

I’m really stuck, any help much appreciated.

output page-----

Sandwell Community Healthcare Services

About Us

Your Services

Equality and Diversity Team at SCHSTrue
subtitle1

Equality and Diversity Team at SCHSTrue
subtitle2

Equality and Diversity Team at SCHSTrue
subtitle3

Equality and Diversity Team at SCHSTrue
subtitle4

Single Equality Scheme

Diversity Strands

Equality Impact Assessments

Quality and Safety Committee

Contact Us

vb code -------

Dim sitename as String=“”
Dim flag as Boolean=False
Dim sub_temp as String=“”

While reader.Read()
’ set title

sitename = reader.Item(“sitename”)

If IsDBNull(reader.Item("subtitle")) then

     flag=False

Else

     flag=True



End If






' if subtitle exists then output
If not IsDBNull(reader.Item("subtitle")) and ((reader.Item("subpageid").ToString()) = (reader.Item("pageid").ToString())) then

If flag = true Then
mydata &= "&lt;p&gt;" & reader.Item("pagetitle") & flag & "&lt;/p&gt;"

End If

mydata &= "&nbsp;&nbsp;&nbsp;" & reader.Item("subtitle") &  "&lt;br /&gt;"
else
mydata &= "&lt;p&gt;" & reader.Item("pagetitle") & "&lt;/p&gt;"
end if

End while