Changing second list components based on first list value
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.
self.location='emp.asp?dept=' + val ;
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.
Now the selected value of the dept is to be collected from the query string by using Request object.
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.
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>"
Note that The second list is populated once the value for dept is stored by selection of the first list.