When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
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...


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article