Here's a tip sent in by Wilfred Mathews on how to build either a drop-down list or a multi-row list box by using a database query.
While using the Select (Drop-down or List) in a Form with options populated from a Database, one would normally like to display a descriptive field on the Form, but store the corresponding Id of the selection into the database. This can be accomplished with a simple operation as follows:
<Form action=process.asp method=post>
<select name=Country>
<%
Set MyDB = Server.CreateObject("ADODB.Connection")
MyDB.Open "MyDSN"
Sql="Select Country_Code, Country_Name from Countries"
Set MyCountry = MyDB.Execute (Sql)
Do While not MyCountry.EOF
Response.Write("<option value=" & MyCountry("Country_Code") _
& ">" & MyCountry("Country_Name") & "</option>")
MyCountry.MoveNext
Loop
MyCountry.Close
MyDB.Close
%>
</select>
. . . .
</Form>
In this example, the full names of all countries are displayed in the drop-down list, and on submission, you can obtain the Code of the Country chosen simply by doing a Request ("Country"), since we're already storing the Code values within the <Option> tags corresponding to each country.