Dynamic Dropdown Population

Populating dropdown menus with multiple options can be achieved in two ways; either by hard-coding the <OPTION> tag in HTML or by retrieving the options from a lookup table in a database. The latter option involves VBScript code and uses active server pages (ASP), but the resultant web page can easily be amended simply by updating the lookup table. In addition the default option can also be dynamically selected. The following ASP code populates the cboExample dropdown menu from the Lut_Example and Lut_Default tables:

<HTML>
<HEAD>
<TITLE>Dynamic Dropdown Menu Population</TITLE>
</HEAD>
<BODY>
<SELECT name="cboExample">

<%
'open DB
set conn=createobject("ADODB.Connection")
conn.ConnectionString = "Provider=SQLOLEDB; Data Source=server;
Initial Catalog=database; User ID=username; Password=password"
conn.open

'retrieve default option
sql = "SELECT default FROM lut_default"
set rs = conn.execute(sql)
If Not rs.EOF then
   strDefault = rs("default")
End If

'retrieve dropdown menu contents
sql = "SELECT example FROM lut_example"
set rs = conn.execute(sql)

'populate dropdown
Do Until rs.EOF
    strExample = rs("Example")
    If strExample = strDefault then
        response.write "<OPTION SELECTED>" & strExample & "</OPTION>"
    Else
        response.write "<OPTION>" & strExample & "</OPTION>"
    End If
    rs.MoveNext
Loop
'kill off recordset object
Set rs = Nothing
%>
</SELECT>
</BODY>
</HTML>

The ASP initially opens a DSNless connection to the database, TEST. Then the Lut_Default table is opened to retrieve the default value of the dropdown menu and the variable strDefault is set to this option. Next a recordset is opened containing the contents of the lookup table for populating the dropdown menu. The recordset is looped through and a condition is tested to determine the default dropdown option, which when found is written using the <OPTION SELECTED> tag. The result of this code is a dropdown menu containing all the records in the Lut_Example table and the default dropdown option set the the initial record in the Lut_Default table. Adding extra menu options or changing the default value is done through amending the database, which could be done via ASP itself. For example, whichever option is chosen each time the page is visited can be written to the DEFAULT table and therefore recalled each time the page is revisited, reminding the user of their previous choice.