ASP question 1

[encore0000]

I have a search box and one of the search criteria is Location (eg, NY, Los Angeles etc) Currently, the dropdown box in my search lists the location in an alphabetical order. How do I set my own order in the location drop down so I can arrange the order according to my preference instead on alphabetical. I mean I want NY at the top on the dropdown list.
Heres the code which applies to this issue.

<%strSQL = "SELECT mydb.tblLocations.* FROM mydb.tblLocations WHERE mydb.tblLocations.Active=1 ORDER BY mydb.tblLocations.Location;"

Set rsLocations = dbconn.Execute(strSQL, , adCmdText)
%>
<tr>
<td colspan="2" class="searchboxes">Search By
Location<br>
<select class="textfieldDrop" name="location_id">
<option value="56">All</option>
<%
While (NOT rsLocations.EOF)
%><option value="<%=(CINT(rsLocations("Location_ID")))%
>" <%if (CINT(rsLocations("Location_ID")) = (CINT
(Request("Location_ID")))) then Response.Write
("SELECTED") : Response.Write("")%>><%=(rsLocations
("Location"))%></option> <%
rsLocations.MoveNext()
Wend
rsLocations.close
set rsLocations = nothing
%>
</select>
</td>
</tr>

 

[jmaddone]

You can change the order by clause:
"ORDER BY CASE mydb.tblLocations.Location WHEN 'NY' THEN 1 ELSE 2 END"

It will put NY on top and place the rest of the states below it.

 

[encore0000]

Thanks alot it worked. How do I list other cities following NY. so as to the cities would appear in the following order on the dropdown

NY
Los Angeles
Boston
.
...
etc
Thanks

 

[mwolf00]

ORDER BY CASE mydb.tblLocations.Location
WHEN 'NY' THEN 1
WHEN 'LA' THEN 2
WHEN 'DC' THEN 3
ELSE 4 END"

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)