Populating a select box from database

[scouse]

Hi,

I've managed to get this working, but I just want to check if there is a better, more efficient way of doing this as it seems a little bit long winded for what I'm trying to do.

Basically, I've got a select box for a list of cities which is populated from a database. However, I want the data that is retrieved to be ordered into different sub groups.

For example,

my database has the following fields:

CITY_ID, CITY_VALUE, CITY_OPTION, CITY_AREA

and some example data

1, LIV, LIVERPOOL, ENGLAND
2, CAR, CARDIFF, WALES
3, MAN, MANCHESTER, ENGLAND
4, GLA, GLASGOW, SCOTLAND
5, LON, LONDON, ENGLAND
6, SWA, SWANSEA, WALES

When populating the select box I want it to end up like this:


<select Name='CITY'>
<OPTION value='javascript:void(0);'>--------------</OPTION>
<OPTION value='javascript:void(0);'>ENGLAND</OPTION>
<OPTION value='javascript:void(0);'>--------------</OPTION>
<OPTION value='LIV'>LIVERPOOL</OPTION>
<OPTION value='MAN'>MANCHESTER</OPTION>
<OPTION value='LON'>LONDON</OPTION>
<OPTION value='javascript:void(0);'>--------------</OPTION>
<OPTION value='javascript:void(0);'>WALES</OPTION>
<OPTION value='javascript:void(0);'>--------------</OPTION>
<OPTION value='CAR>CARDIFF</OPTION>
<OPTION value='SWA'>SWANSEA</OPTION>
<OPTION value='javascript:void(0);'>--------------</OPTION>
<OPTION value='javascript:void(0);'>SCOTLAND</OPTION>
<OPTION value='javascript:void(0);'>--------------</OPTION>
<OPTION value='GLA'>GLASGOW</OPTION>
</SELECT>


At the moment I'm creating a different recordset for each area. E.g

strENG ="Select * from city WHERE CITY_AREA='ENGLAND'"
Set engRS = objConn.Execute(strENG)
.
.
put into select box
strWAL ="Select * from city WHERE CITY_AREA='WALES'"
Set walRS = objConn.Execute(strWAL)
.
.
add into select box
.
.
and so on!

This all works fine, but I can't help thinking it isn't the most efficient way to do it. Would there be a simpilar way of putting all the data into one recordset and then putting each record into the relevent sub group as its populating the select box?

Sorry for the long post, but I couldn't explain it any easier :rolleyes:

Thanks

Phil

[schizo]

What i'd probably do is add an ORDER BY to the query and order it by CITY_AREA and CITY_OPTION. This way if the current CITY_AREA is different from the previous (check with a simple if statement), then you know you should have a new section option.

Another thing you might want to look at to make the code cleaner is the <optgroup> tag for your section titles. So you could have <optgroup label="ENGLAND"> and not need to worry about messy javascript and such. I am pretty sure you can even style these tags... not sure why so few people know about them.

Hope this helps.

[scouse]

Hi thanks for the reply,

First of all, I didn't know about the <OPTGROUP> thing. Cheers, thats goin to come in very handy.

Secondly, I tried to do what you suggested, but I think my brain has finally fried and I can't get my head around the problem.

This is the code for populating the select box:



<Select name='CITY'>
<%
cityRS.MoveFirst

Response.write "<optgroup label='ENGLAND'>"&vbcrlf

WHILE NOT cityRS.EOF
strCITY_VALUE=Trim(cityRS.Fields("CITY_VALUE"))
strCITY_OPTION=Trim(cityRS.Fields("CITY_OPTION"))
strCITY_AREA=Trim(cityRS.Fields("CITY_AREA"))


response.write "<OPTION value='"&strCITY_VALUE&"'>"&strCITY_OPTION&"</OPTION>"&vbcrlf

cityRS.MoveNext
Wend
%>
</select>


The problem is I can't figure out where to put an if statement to check whether strCITY_AREA is different from the previous record.

I tried putting:

strCITY_AREA2=Trim(cityRS.Fields("CITY_AREA"))
if strCITY_AREA2 <> strCITY_AREA then
Response.write "<optgroup label='"&strCITY_AREA2&"'>"&vbcrlf
End if

After the cityRS.MoveNext statement and before the Wend statement, but this just created an "Exception occurred Error" so I assume I must be doing something wrong.

Any Ideas?

Cheers

Phil

[schizo]

Try this...


<Select name='CITY'>
<%
cityRS.MoveFirst

strCITY_AREA2 = ""

WHILE NOT cityRS.EOF
strCITY_VALUE=Trim(cityRS.Fields("CITY_VALUE"))
strCITY_OPTION=Trim(cityRS.Fields("CITY_OPTION"))
strCITY_AREA=Trim(cityRS.Fields("CITY_AREA"))

if strCITY_AREA2 <> strCITY_AREA then
Response.write "<optgroup label='"&strCITY_AREA&"'>"&vbcrlf
end if

response.write "<OPTION value='"&strCITY_VALUE&"'>"&strCITY_OPTION&"</OPTION>"&vbcrlf

strCITY_AREA2 = strCITY_AREA

cityRS.MoveNext
Wend
%>
</select>

[scouse]

Thanks so much for your help schizo,

That works perfectly! :)

Cheers

Phil