The source code and the MS Access 2000 database for this example may be downloaded by clicking here.

Creating a combo list with ASP ....

A drop down combo list box can be created dynamically using ASP to read elements from a table. The user can then make selections from the drop down list.

If the underlying table data should change, the combo list will always pick up the latest changes in the underlying table.

Combining this with the onchange Javascript event means that a submit button does not have to be included.

The following code illustrates an example which can also be downloaded from here.

set conn=server.CreateObject ("adodb.connection")
connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
      Server.MapPath("courses.mdb") & ";Persist Security Info=False"
conn.Open connect
<html>
<head>
<title>Example combo box</title>

<script language="javascript">
<!--
function dept_onchange(frmSelect) {
   frmSelect.submit();
}
//-->
</script>
</head>
<body>

The following was selected : <%=Request.Form ("courses")%>

<form name="frmSelect" method="Post" action="select.asp">
<SELECT name=courses
     LANGUAGE=javascript onchange="return dept_onchange(frmSelect)">
<%
Set oRs=Server.CreateObject("adodb.recordset")
strSQL = "SELECT DISTINCT CourseName FROM tblCourses "
strSQL = strSQL & " ORDER BY CourseName"
oRs.Open strSQL, conn

Do while not oRs.EOF
   if Request.Form("courses") = oRs("CourseName") then
      Response.Write "<OPTION VALUE = '" & oRS ("CourseName") & "' SELECTED>"
      Response.Write oRs("CourseName") & "</Option>"
      oRs.MoveNext
   else
      Response.Write "<OPTION VALUE = '" & oRs ("CourseName") & "'>"
      Response.Write oRs("CourseName") & "</Option>"
      oRs.MoveNext
   end if
loop

%>
</SELECT>
</form>
</body>
</html>

Code explanation

Set cmd = Server.CreateObject ("ADODB.Connection")
connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
      Server.MapPath("courses.mdb") & ";Persist Security Info=False"
conn.Open connect

This starts with creating a connection object to the access database 'courses.mdb'. The connection string uses Server.MapPath to map itself to the correct directory.

<html>
<head>
<title>Example combo box</title>

<script language="javascript">
<!--
function dept_onchange(frmSelect) {
   frmSelect.submit();
}
//-->
</script>
</head>
<body>

The Javascript code in the header of the html page provides the submit function.

The following was selected : <%=Request.Form ("courses")%>

This line simply provides a way of identifying what has been selected from the drop down list.

<form name="frmSelect" method="Post" action="select.asp">

The start of the form shows that we are going to post the result back onto the same file in this case 'select.asp'

<SELECT name=courses
     LANGUAGE=javascript onchange="return dept_onchange(frmSelect)">
<%
Set oRs=Server.CreateObject("adodb.recordset")
strSQL = "SELECT DISTINCT CourseName FROM tblCourses "
strSQL = strSQL & " ORDER BY CourseName"
oRs.Open strSQL, conn

Do while not oRs.EOF
   if Request.Form("courses") = oRs("CourseName") then
      Response.Write "<OPTION VALUE = '" & oRS ("CourseName") & "' SELECTED>"
      Response.Write oRs("CourseName") & "</Option>"
      oRs.MoveNext
   else
      Response.Write "<OPTION VALUE = '" & oRs ("CourseName") & "'>"
      Response.Write oRs("CourseName") & "</Option>"
      oRs.MoveNext
   end if
loop

%>
</SELECT>

The drop down list uses the standard html SELECT command. We first have to extract all CourseNames from the table using a SQL select statement. To actually display the results we must loop around and write out each line.

When you make a selection from the drop down list, the onchange event will be initiated which will then be submitted using the Javascript function in the header

</form>
</body>
</html>

The last lines close the form and the html for the page.

The source code and the MS Access 2000 database for this example may be downloaded by clicking here.