Dependent dropdowns with Classic ASP and Access

A dependent dropdown changes the second set of choices when the first value changes. This legacy Access-backed Classic ASP example reloads the page with the selected department in the query string; modern applications can also fetch the second list asynchronously. For new server-side applications, use a supported server database rather than choosing Access as the web application's primary database.

Reload with the selected department

<script>
document.getElementById("dept").addEventListener("change", function () {
  var value = encodeURIComponent(this.value);
  window.location.href = "emp.asp?dept=" + value;
});
</script>

Resulting URL

https://sitename.com/emp.asp?dept=sales

Read the selected value

dept = Trim(Request.QueryString("dept"))

Open the Access database

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")

Load the first dropdown

strSQL = "SELECT DISTINCT dept FROM emp_m ORDER BY dept"

objRS.Open strSQL, objconn
Response.Write "<select id=""dept"" name=""dept"">"
Response.Write "<option value="""">Select department</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

Load matching employees safely

If Len(dept) > 0 Then
  Set cmd = Server.CreateObject("ADODB.Command")
  Set cmd.ActiveConnection = objconn
  cmd.CommandText = "SELECT emp_no, name, dept FROM emp_m WHERE dept = ? ORDER BY name"
  cmd.CommandType = adCmdText
  cmd.Parameters.Append cmd.CreateParameter("pDept", adVarWChar, adParamInput, 50, dept)
  Set objRS = cmd.Execute

  Do While Not objRS.EOF
    Response.Write Server.HTMLEncode(CStr(objRS("emp_no"))) & " " & _
      Server.HTMLEncode(CStr(objRS("name"))) & " " & _
      Server.HTMLEncode(CStr(objRS("dept"))) & "<br>"
    objRS.MoveNext
  Loop
  objRS.Close
End If

The selected department is passed to ADO as a parameter instead of being concatenated into the SQL string. For the ACE OLE DB provider, ? placeholders are positional, so append parameters in the same order they appear in the command.

Download the original ASP/Access example package. Treat the download as legacy reference code and apply the parameterization and output-encoding updates shown on this page before production use.

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: