Tutorial #173

Dynamically Linked Dropdowns

Level: Beginner
Requirements: DreamweaverMX, Access
Language: ASP, VBScript, SQL

Introduction

With this quick tutorial, you will see how you can use one dropdown to automatically filter the contents of another.

This example will use a querystring in order to pass through what value has been selected from one dropdown so that the second can use it. The drawback of this technique means that the dropdowns should be the first form fields displayed to the user as any other data entered into other form objects would disappear after the first dropdown was changed.

Step 1: Creating the tables

As always, we will use our movie tables for this example. Here we will require two tables, a genre table and the movie table.

tblMovieType

MovieTypeID: Primary Key, Autonumber
MovieType: Text

We will need some data prepopulated in these tables so quickly enter the following information, or you can of course enter whatever types and movies tickle your fancy.

The example data used during the creation of this tutorial is.

tblMovieType

MovieTypeID	MovieType
1	Sci-Fi
2	Horror
3	Drama
4	Comedy
5	Thriller
6	Documentary
7	Fantasy
8	Western
9	War
10	Martial Arts
11	Action/Adventure

tblMovies

MovieTypeID	Movie
10	Kill Bill
1	Star Wars
1	Indiana Jones
11	Reservoir Dogs
7	Willow
2	Frankenstein

Step 2: Creating the recordsets

First start by creating a new page called dropdowns.asp

We will need two recordsets, one for each dropdown. The second will depend on the selected value of the first so to speak, so create the following two recordsets on your page.

Step 3: Creating the dropdown fields

Create a form in the body of your page and in it place two dropdown fields along with some relevant text as a name for the fields. Name the first dropdown field ddMovieType and the second one ddMovie.

What we will do is when the first dropdown has a value selected, we will redirect to the current page using the MovieTypeID of the selected genre. This will then reload the page and use the selected value to filter the second recordset which will in turn be used to populate the ddMovie dropdown field.

First we will need a little bit of Dreamweaver javascript that we will place in the <head> tag of our code.

Again, the line in blue should be entered in as one full line of code.

Now we have our script that will redirect our page, all we have to do now is to code our dropdowns to action this script and to populate themselves with the data from the recordsets.

Scroll back down to our first drop down and amend the code as follows.

<select name="ddMovieType" onchange="MM_jumpMenu('parent',this,0)">
<option value="dropdowns.asp?typeid=0">--- Select Genre</option>
<%
While Not rsMovieType.Eof
%>
<option value="dropdowns.asp?typeid=<%=rsMovieType("MovieTypeID")%>" <%If CInt(Request.QueryString("typeid"))=rsMovieType("MovieTypeID") Then Response.Write "selected"%>><%=rsMovieType("MovieType")%></option>
<%
rsMovieType.MoveNext()
Wend
%>
</select>

Lets have a look at this code in detail.

We call the MM_jumpMenu javascript function with the value of the selected option from the dropdown.

<option value="dropdowns.asp?typeid=0">--- Select Genre</option>

This is our first value in the dropdown field, this is set to 0 so if selected, the movie dropdown field will not be populated.

<% While Not rsMovieType.Eof %>

We start our server side code by creating a WHILE statement that will loop through our recordset.

Our option value is now linked to the values in the recordset. We also do a check to grab the querystring typeid, if we find a value we will select this value as this will be the value selected by the user.

<%
rsMovieType.MoveNext()
Wend
%>

Finally we loop round the recordset until the data runs out.

Now we need to do roughly the same to the second recordset.

<select name="ddMovie" onChange="">
<option value="0">--- Select Movie</option>
<%
While Not rsMovie.Eof
%>
<option value="<%=rsMovie("MovieID")%>"><%=rsMovie("Movie")%></option>
<%
rsMovie.MoveNext()
Wend
%>
</select>

Save your file and test it.

This is by no means a perfect solution and you are limited (at least with this example) by having just the two dropdown fields. You can certainly update the code to cater for as many dropdowns as you wish. But the main issue here is the page refresh for processing, we'll look at this again soon with a solution that will get rid of the page refresh altogether.

Happy coding.

Tutorial By	Submitted On	Views	Rating
Rob Boyle	02/03/2008	6289	Rate Tutorial