4GuysFromRolla.com - User Tips: Populating a Drop-Down List the Easy Way using SQL and VBScript

Windows Systems Administrator
Jupitermedia
US-CT-Darien

User Tips: Populating a Drop-Down List the Easy Way using SQL and VBScript

By Brian B.

As a newbie to ASP I often look to 4guysfromrolla.com for answers. This time was no different however I could not find a good example that would work in my situation. After a viewing a few articles and putting my head to good use I came up with the following solution. I hope it helps to keep your sites easier to maintain.

Table Definition

priority_types
priority_id	priority_name
1	Low
2	Normal
3	High
4	Now!

authors note: I added the last one, just for my boss 8-)

VBScript:
Connect to database.... (see other articles on this site if you need help: I recommend you start with the Database F.A.Q.)

Set oConn = Server.CreateObject("ADODB.Connection") Set oRs = Server.CreateObject("ADODB.Recordset") oConn.Open "DSN=droplist_ex"

Open a Recordset and get the information to use in the drop-down list

strSQL = "SELECT * " strSQL = strSQL & "FROM priority_types" oRs.Open strSQL, oConn, adOpenKeyset, adLockOptimistic

Ok now the recordset oRs is full of the data needed to populate our dynamic dropdown listbox

The next line will look through the recordset until it reaches the EOF (end of file) or the last record in the returned results.

WHILE NOT oRs.EOF

The next few lines are pretty easy to follow except maybe the first. And I only added this after my boss told me he wanted NORMAL to be the default for this particular drop-down list. The first line says if the priority_id field equals 2 then return this option as the default or selected item in the drop-down menu. You will need set this to match your own criteria. 2 = Normal in my table. If you set it to 1 then it will show Low as the default menu item. Change it and see for yourself in the example code.

IF oRs("priority_id") = 2 THEN prioritytypes = prioritytypes & "<option selected value=" & oRs("priority_id") & ">" & oRs("priority_name") & "</option>" ELSE prioritytypes = prioritytypes & "<option value=" & oRs("priority_id") & ">" & oRs("priority_name") & "</option>" END IF oRs.MoveNext WEND oRs.Close oConn.Close

All of the above ASP code would go at the top of the file within the ASP tags <% %>. And below is the HTML code to make it show the menu items.

<html><head><title>Dynamic Drop-Down Menu Example</title></head>
<body>
<form method="POST" action="dropdown_ex.asp">
<p><select size="1" name="dropdown_menu">
<%=prioritytypes%>
</select></p>
</form>
</body>
</html>

A working .asp file, the Access database is included
in the download file below.

Download Example Code

Run the example

Happy Programming!

Return to user tips...