grouping optgroup items

[Richey]

hi

i'm using optgroup in my asp page like below. objRS2 is taken from a table called BusinessCategories with 4 columns ID, BroadItem, Item, RelatedItem.
The values in the list should be drawn from column Item. Each item will have a broaditem associated so the table may look like this

1, Agriculture and animal welfare, Agricultural colleges,""
2, Agriculture and animal welfare, Animal feed mills, ""
3, Agriculture and animal welfare, Animal grooming, ""
4, Business Services, Auctioneering, ""
5, Business Services, Consulting, ""

I am breaking up the optgroup labels using a filter, which although works I'm worried might end up being quite slow and repetitive (I will end up with about 25 distinct BroadItems and 1000 Item's, so I'll have to repeat the code below 25 times !

any ideas how i can improve this ?

many thanks
rich

b = b & " <div>"
b = b & "<label for=""cat"">Business Type<br />(Use Ctrl key for multiple selection(s))</label>" 
b = b & "<SELECT class=""a"" id=""cat"" name=""cat"" SIZE=""8"" tabindex=""18"" MULTIPLE>"

Dim arrValues, j
If ErrorMsg1 = "" Then
arrValues = split(Cat, ",")
else
arrValues = split(request.form("Cat"), ",")
end if
do while not objRS2.eof
b = b & "<optgroup label=""Business Services"">"
objRS2.Filter = ("BroadItem = 'Business services'")
  for j = 0 to UBound(arrValues)
    if trim(arrValues(j)) = objRS2("Item") then
      b = b & "<OPTION selected value=""" & objRS2("Item") & """>" & objRS2("Item") & "</OPTION>"
      exit for
    end if
next
  if j > UBound(arrValues) then
    b = b & "<OPTION value=""" & objRS2("Item") & """>" & objRS2("Item") & "</OPTION>"
  end if
  objRS2.movenext
Loop
objRS2.Filter = adFilterNone

b = b & "</OPTGROUP>"
b = b & "</SELECT>"
b = b & " </div>"

etc etc....................

 

[vbkris]

u have to use Order By clause of the query...

Known is handfull, Unknown is worldfull

 

[DotNetGnat]

yes i also suggest you to do at the query level...

show us sample data and the output you are looking for...

we can come up with a query

-DNG

 

[Richey]

here is sample data as .txt

the output i'd like would split the Items (Item column) between the BroadItem column, so fitting into my code above, the optgroup labels would be as below.
does that make sense ?

thanks

b = b & "<optgroup label=""Agriculture and animal welfare"">"
b = b & "<optgroup label=""Business Services"">"
b = b & "<optgroup label=""Entertainment leisure and tourism"">"
etc etc etc


ID,BroadItem,Item,RelatedItem
1,Agriculture and animal welfare,Agricultural colleges,
2,Agriculture and animal welfare,Animal boarding premises,
3,Agriculture and animal welfare,Animal feed mills,
43,Agriculture and animal welfare,Abattoir,
44,Business services,Advertising, public relations and marketing agencies,
45,Business services,Livestock auctioneering,
120,Entertainment leisure and tourism,Nightclubs,
121,Entertainment leisure and tourism,Public houses and bars,
122,Entertainment leisure and tourism,Sports grounds,
190,Manufacturing - food,Fish and fish products,
191,Manufacturing - food,Other foods,
192,Manufacturing - food,Meat and meat products,
193,Manufacturing - food,Soups / condiments / seasonings,
243,Personal services,Wedding and party services,

 

[Richey]

sorted using grouping = thank you