Populate a dropdown from Access data

This legacy Access-backed example shows the general ADO pattern for building a dropdown from database rows. The same SELECT/loop/output pattern applies when the data source is moved to a supported server database. The query selects each department once with DISTINCT, then writes one HTML <option> for each row.

Write the dropdown options

Response.Write "<select name=""dept"">"
Response.Write "<option value="""">Departments</option>"
Do While Not objRS.EOF
  deptText = Server.HTMLEncode(CStr(objRS("dept")))
  Response.Write "<option value=""" & deptText & """>" & deptText & "</option>"
  objRS.MoveNext
Loop
Response.Write "</select>"

Complete Access dropdown example

<%@ Language="VBScript" %>
<% Option Explicit %>
<!-- #include virtual="/adovbs.inc" -->
<%
Dim objconn, objRS, strSQL, deptText
Set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
  Server.MapPath("db/emp.mdb")
objconn.Open

Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT DISTINCT dept FROM emp_m ORDER BY dept"
objRS.Open strSQL, objconn

Response.Write "<select name=""dept"">"
Response.Write "<option value="""">Departments</option>"
Do While Not objRS.EOF
  deptText = Server.HTMLEncode(CStr(objRS("dept")))
  Response.Write "<option value=""" & deptText & """>" & deptText & "</option>"
  objRS.MoveNext
Loop
Response.Write "</select>"

objRS.Close
Set objRS = Nothing
objconn.Close
Set objconn = Nothing
%>

The displayed/attribute value is HTML-encoded before being written. If an option value must later be used in a query, validate it and pass it as a database parameter rather than concatenating it into SQL.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: