Multiple Related Select Lists

(Return to Main Code Corner Page)

One question that frequently appears in help forums has to do with linking multiple select lists on a web form, for example, when a user selects a particular state, the select list comprising cities would only display the relevant values for that particular state.(Try it!)

One approach entails using client-side JavaScript arrays (please see article under the JavaScript tutorials) but this approach may not work well when there are many records or when the data is dynamic. In these cases, using server-side code to control the data flow most likely is the best approach.

This article describes how to use server-side coding to populate the select lists from the database and to form a relationship among the select lists so that only the relevant data is displayed. To allow you to follow along, we'll use the Northwind.mdb database as the data source.

In this example, we will have three select lists: Country, Region, and City. When the user selects a particular country, only those regions belonging to the country should appear. Similarly, when the user selects a region, only the corresponding cities should be in the City select list.

The Server-Side Code

The first step is to declare some variables and to create a our connection object:

<%
dim strDataPath, strConnectString, objConnection
dim strCountry, strRegion, strCity, objRS, strSelected, strSQL
strCountry = Request.Form("Country")
strRegion = Request.Form("Region")
strCity = Request.Form("City")
'set connection strings for entire application
strDataPath = server.MapPath("NorthWind.mdb")
strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
            + " Data Source= " & strDataPath & ";"_
            + " Mode=Share Deny None;User Id=admin;PASSWORD=;"
            
if not IsObject("ojbConnection") then
    set objConnection=Server.CreateObject("ADODB.Connection")
    objConnection.ConnectionTimeout = 15
    objConnection.CommandTimeout = 10
    objConnection.Mode = 3 'adModeReadWrite
    if objConnection.state = 0 then
        objConnection.Open strConnectString
    end if
end if
%>

Next, we need to create a subroutine to create the Country select list dynamically from the database:

<%
sub makeCountry()
    if not isObject("objRS") then
        set objRS=Server.CreateObject("ADODB.RecordSet")
    end if
    if objRS.state <> 0 then
        objRS.close
    end if
    strSQL = "SELECT DISTINCT Country FROM Customers ORDER BY Country"
    objRS.Open strSQL,objConnection,3,3
    Response.Write("<option></option>" & VBCRLF )
    do while not objRS.EOF
        if objRS("Country") = strCountry then
            strSelected = " Selected "
        else
            strSelected = ""
        end if
        Response.Write("<option" & strSelected & ">" &_
                 objRS("Country") & "</option>" & VBCRLF )
        objRS.MoveNext
    loop
    objRS.Close
    set objRS=Nothing
end sub
%>

This code loops through the table and ensures that the previously selected values remain when the page refreshes.

Once we have the values for the country, we can use the request object to obtain the selected country and use it as a parameter for the SQL select to populate the Region select. Similarly, once the user selects a value from the Region select, we can use that value to populate the SQL for the City select.

<%
sub makeRegion()
    if strCountry <> "" then
        if not isObject("objRS") then
            set objRS=Server.CreateObject("ADODB.RecordSet")
        end if
        if objRS.state <> 0 then
            objRS.close
        end if
        strSQL ="SELECT DISTINCT Region FROM Customers WHERE Country = '" &_
                 strCountry & "' ORDER BY Region"
        objRS.Open strSQL,objConnection,3,3
        if objRS.eof then
            Response.Write("<option>No Regions Found</option>")
        else
            Response.Write("<option>Select Region Now</option>" & VBCRLF )
            do while not objRS.EOF
                if objRS("Region") = strRegion then
                    strSelected = " Selected "
                else
                    strSelected = ""
                end if
                Response.Write("<option" & strSelected & ">" &_
                         objRS("Region") & "</option>" & VBCRLF )
                objRS.MoveNext
            loop
        end if
        objRS.Close
        set objRS=Nothing
    else
        Response.Write("<option>Select a Country First</option>")
    end if
end sub
sub makeCity()
    if strRegion <> "Select a Country First" AND _
     strRegion <> "Select Region Now" AND strRegion <>"" then
        if not isObject("objRS") then
            set objRS=Server.CreateObject("ADODB.RecordSet")
        end if
        if objRS.state <> 0 then
            objRS.close
        end if
        strSQL = "SELECT DISTINCT City FROM Customers WHERE Region = '" &_
                 strRegion & "' ORDER BY City"
        objRS.Open strSQL,objConnection,3,3
        if objRS.eof then
            Response.Write("<option>No Cities Found</option>")
        else
            Response.Write("<option>Select City Now</option>" & VBCRLF )
            do while not objRS.EOF
                if objRS("City") = strCity then
                    strSelected = " Selected "
                else
                    strSelected = ""
                end if
                Response.Write("<option" & strSelected & ">" &_
                         objRS("City") & "</option>" & VBCRLF )
                objRS.MoveNext
            loop
        end if
        objRS.Close
        set objRS=Nothing
    else
        Response.Write("<option>Select a Region First</option>")
    end if
end sub
%>

 

The Client-Side Code

Since the Region and City selects depend upon the value of the Country select, it is necessary to reset those select lists each time the user changes the Country selection. So, we will use the onChange() method of the Country select list to clear out the values for the Region and the City. Similarly, if the user modifies the Region select, we must refresh the City select:

<SCRIPT LANGUAGE=javascript>
<!--
function submitCountry(){
    var objForm = document.forms[0];
    objForm.elements['Region'].selectedIndex=0;
    objForm.elements['City'].selectedIndex = 0;
    objForm.submit();
}
function submitRegion(){
    var objForm = document.forms[0];
    objForm.elements['City'].selectedIndex = 0;
    objForm.submit();
}
//-->
</SCRIPT>

Only one issue remains: thus far, we have always submitted the page to itself so that the request object could use the select values as parameters for the sql select. However, the page would not be of much use if it could only submit to itself. So we need to be able to set the action for the form dynamically:

<SCRIPT LANGUAGE=javascript>
<!--
function submitForm(){
    var objForm = document.forms[0];
    objForm.action = "http://www.FairfieldConsulting.com/processform.asp"
    return true;
}
//-->
</SCRIPT>

 

Putting it All Together

Now that we have the server-side and client-side code we just need to drop it into the html; nothing very fancy here:

<%@ Language = VBScript %>
<%Option Explicit%>
<%Response.Buffer = True%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
<%
dim strDataPath, strConnectString, objConnection
dim strCountry, strRegion, strCity, objRS, strSelected, strSQL
strCountry = Request.Form("Country")
strRegion = Request.Form("Region")
strCity = Request.Form("City")
'set connection strings for entire application
strDataPath = server.MapPath("NorthWind.mdb")
strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
            + " Data Source= " & strDataPath & ";"_
            + " Mode=Share Deny None;User Id=admin;PASSWORD=;"
            
if not IsObject("ojbConnection") then
    set objConnection=Server.CreateObject("ADODB.Connection")
    objConnection.ConnectionTimeout = 15
    objConnection.CommandTimeout = 10
    objConnection.Mode = 3 'adModeReadWrite
    if objConnection.state = 0 then
        objConnection.Open strConnectString
    end if
end if
sub makeCountry()
    if not isObject("objRS") then
        set objRS=Server.CreateObject("ADODB.RecordSet")
    end if
    if objRS.state <> 0 then
        objRS.close
    end if
    strSQL = "SELECT DISTINCT Country FROM Customers ORDER BY Country"
    objRS.Open strSQL,objConnection,3,3
    Response.Write("<option></option>" & VBCRLF )
    do while not objRS.EOF
        if objRS("Country") = strCountry then
            strSelected = " Selected "
        else
            strSelected = ""
        end if
        Response.Write("<option" & strSelected & ">" &_
                 objRS("Country") & "</option>" & VBCRLF )
        objRS.MoveNext
    loop
    objRS.Close
    set objRS=Nothing
end sub
sub makeRegion()
    if strCountry <> "" then
        if not isObject("objRS") then
            set objRS=Server.CreateObject("ADODB.RecordSet")
        end if
        if objRS.state <> 0 then
            objRS.close
        end if
        strSQL ="SELECT DISTINCT Region FROM Customers WHERE Country = '" &_
                 strCountry & "' ORDER BY Region"
        objRS.Open strSQL,objConnection,3,3
        if objRS.eof then
            Response.Write("<option>No Regions Found</option>")
        else
            Response.Write("<option>Select Region Now</option>" & VBCRLF )
            do while not objRS.EOF
                if objRS("Region") = strRegion then
                    strSelected = " Selected "
                else
                    strSelected = ""
                end if
                Response.Write("<option" & strSelected & ">" &_
                         objRS("Region") & "</option>" & VBCRLF )
                objRS.MoveNext
            loop
        end if
        objRS.Close
        set objRS=Nothing
    else
        Response.Write("<option>Select a Country First</option>")
    end if
end sub
sub makeCity()
    if strRegion <> "Select a Country First" AND _
     strRegion <> "Select Region Now" AND strRegion <>"" then
        if not isObject("objRS") then
            set objRS=Server.CreateObject("ADODB.RecordSet")
        end if
        if objRS.state <> 0 then
            objRS.close
        end if
        strSQL = "SELECT DISTINCT City FROM Customers WHERE Region = '" &_
                 strRegion & "' ORDER BY City"
        objRS.Open strSQL,objConnection,3,3
        if objRS.eof then
            Response.Write("<option>No Cities Found</option>")
        else
            Response.Write("<option>Select City Now</option>" & VBCRLF )
            do while not objRS.EOF
                if objRS("City") = strCity then
                    strSelected = " Selected "
                else
                    strSelected = ""
                end if
                Response.Write("<option" & strSelected & ">" &_
                         objRS("City") & "</option>" & VBCRLF )
                objRS.MoveNext
            loop
        end if
        objRS.Close
        set objRS=Nothing
    else
        Response.Write("<option>Select a Region First</option>")
    end if
end sub
%>
<SCRIPT LANGUAGE=javascript>
<!--
function submitCountry(){
    var objForm = document.forms[0];
    objForm.elements['Region'].selectedIndex=0;
    objForm.elements['City'].selectedIndex = 0;
    objForm.submit();
}
function submitRegion(){
    var objForm = document.forms[0];
    objForm.elements['City'].selectedIndex = 0;
    objForm.submit();
}
function submitForm(){
    var objForm = document.forms[0];
    objForm.action = "http://www.FairfieldConsulting.com/processform.asp"
    return true;
}
//-->
</SCRIPT>
</HEAD>
<BODY onLoad="window.moveTo(0,0);window.resizeTo(400,400);">
<div style="align: center; text-align: center;">
<a href=# onClick="window.close(); return false;">Close Window</a>
</div>
<FORM action="" method=POST id=form1 name=form1 onSubmit="return submitForm()">
<SELECT name="Country" onChange="submitCountry()">
    <%call makeCountry%>
</SELECT><br>
<SELECT name="Region" onChange="submitRegion()">
    <%call makeRegion%>
</SELECT><br>
<SELECT name="City">
    <%call makeCity%>
</SELECT><br>
<p><INPUT type="submit" value="Submit" id=submit1 name=submit1></p>
</FORM>
</BODY>
<%
objConnection.Close
set objConnection = Nothing
%>
</HTML>

So there you have it--three related, dependant select lists, driven by dynamic content. If you would like to play with this further, you can download the code and the database here.

(Return to Main Code Corner Page)

If you found this article helpful and you would like to make a donation, please click on the PayPal button below.