Second drop down list box value connecting to first list in ASP

We have seen how to populate a drop down list box by taking data from a table. Now we will design two drop down list boxes and try to learn how to manage options of the second drop down list box based on the selection of the first drop down list box. There are many such requirements where user select the value in first list and then second list changes to show available options under the selected data of first list. For example if we have a list of states of USA in the first list and the cities list in the second list, then on selection of a state from first list the second list should change to show only the cities of selected state in USA.

Another example is our employee table. In first drop down list box the name of the departments will appear. As we select a department from the first list the second list should show the employees of the selected department only. You can see how the first department list is populated by taking data from the Access table in our dynamic population of list box tutorial . Such requirements are very common and often used in different web sites.

This particular script reloads the browser on selection of the first drop down list box and sends the data back to server. From server the options of the second list box are managed. So we will be using server side scripting languages to manage the operation.

Here we will be using VBScript under ASP environment to develop this script. The same functionality of managing two dropdowns is also developed by using PHP and is available with demo in our PHP section. This can be managed by using client side JavaScript also. The tutorial on managing list boxes at client side using JavaScript is available at JavaScript section. Here the list boxes are managed without sending back the data to server or reloading the page.

Read how Ajax is used to manage two drop down lists in PHP section.

We will not discuss on how to connect to db, how to display data or how to populate list box with table data here. ( It is advisable to learn all these before reading this tutorial ).

We will keep one JavaScript function within the head tag of our page. The main job of this javascript function is to reload the page with the selected data of the first drop down box. Note that as we are using JavaScript to reload the page so we can add a section to check the status of JavaScript ( enabled or disabled ) of the client browser.

Here is the JavaScript function within our head tag.

<SCRIPT language=JavaScript>
function reload(form){
var val=form.dept.options[form.dept.options.selectedIndex].value;
self.location='emp.asp?dept=' + val ;
}
</script>

The above code gets executed once the first drop down list box is selected, it collects the selected value of the first drop down list box ( here it is dept ) and then redirects the page with the value of department in the query string. Note that our file name is emp.asp, so once the department name is selected, then the url at the address bar should look like this.

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

Now the selected value of the dept is to be collected from the query string by using Request object.

dept=Request.QueryString("dept")

So this way we can get the value of the department and store it in the variable dept.

Now let us create object connection and record set. We will not discuss much on this as the tutorials are available here.

Set objconn = Server.CreateObject("ADODB.Connection")

objconn.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("db/emp.mdb")

objconn.Open


Set objRs = Server.CreateObject("ADODB.Recordset")

Now for first drop down list box, we will start here. Let us first write the query to collect all single department names from the table by using distinct sql.

strSQL = "SELECT distinct dept from emp_m"

Now let us populate the drop down list box by taking data from the table.

objRS.Open strSQL, objconn
Response.Write "<form method=post name=f1 action=''><select name=dept onchange='reload(this.form)'><option value=''>Select dept</option>"
Do While Not objRS.EOF
    Response.Write "<option value=" & objRs("dept") & ">" & objRs("dept") & "</option>"
     objRS.MoveNext
 Loop
objRs.Close
Response.Write "</select>"

Note that we have used onchange event handler for the first drop down list box to execute the JavaScript function reload() once an option is selected. This drop down list ( first list ) always shows same elements. Now let us move to second drop down list, where the elements changes based on the value of first list. We will do a check for the variable dept before adding it to the query for second list. We will check the length of the dept variable by using len function of ASP. If the length of the variable is more than 0 that mean there is a department name stored inside. Here is the code for the second drop down list.

If len(dept) > 1 Then

strSQL = "SELECT  * FROM emp_m where dept='" & dept &"'"
objRS.Open strSQL, objconn

Do While Not objRS.EOF
 Response.Write objRs("emp_no")&" "&objRs("name")&" " &objRs("dept")&"<br>"
     objRS.MoveNext
 Loop
Response.Write "</form>"
objRs.Close

objconn.Close
end if

Note that The second list is populated once the value for dept is stored by selection of the first list.

Download the asp code with access db here

Changing second list components based on first list value