Universally Related Popup Menus AJAX Edition: Part 1 / Page 2

Universally Related Popup Menus AJAX Edition: Part 1

Running the Sample Script

I made modifications to the ASP scripts from the last article on the same IIS 5 server as before. Therefore, you also need to have a Web server which supports ASP at your disposal unless you plan on using a different server-side language.

There are 5 files included with this article. They can be downloaded here. Here's an explanation of each:

1. Vehicle lists 2000.mdb:: An Access database. It should reside in a folder called "URPMs db" located directly parallel to the "WWWRoot" folder.
2. URPMs.dsn: A DSN file containing database connection information.

All the following files should be placed in a folder called "URPMs", directly under the "WWWRoot" folder.

3. testAJAX.htm:: The main html page - used for testing the script.
4. URPM_AJAX.js:: The JavaScript file that contains all of the client-side AJAX and URPM functionality.
5. getListItems.asp: The server-side script that retrieves the list items from the database.

Unzip the files to the parent directory of your server's WWWRoot. This will probably be Inetpub folder. Here is a diagram of the resulting directory structure, once the files have been extracted:

Try it out!

To see the script in action, open up your favorite browser and type something like the following into the address bar, where <servername> is the name of your server. If it's running on your desktop, it may very well be "localhost." Otherwise, you'll have to know the IP address or server name:

https://<servername>(:port)/UPRMs/testAJAX.htm

This is what I had to type to bring up the sample page:

https://localhost/URPMs/testAJAX.htm

Using the URPMs in Your Own Web Page

To use the script with your asp page, you have to make some changes to your database as well as add some simple HTML to your Web page. Here's what you have to do:

1: Create the File DSN

This is the exact same File DSN as in part III. To create the DSN:

Go to Start->Control Panel->Administrative Tools->Data Sources (ODBC)
Click the File DSN tab
Click the Add button
Select the appropriate driver for your database. Our test database is Microsoft Access, so we would select it from the list.
Click the Next button.
Either enter the name of the File DSN in the text box to create the file in the default directory or use the browse button to select a location for it.
Click the Next button.
Click the Finish button. Depending on the database, a dialog will come up for you to enter details about the connection. In the case of our example, the "ODBC Microsoft Access Setup" dialog appears.
Enter "URPMs" in the Data Source Name field or feel free to substitute a different name of your choosing. If you do go with your own name, you'll have to add a hidden field to your html file:

That's all you really need to do, but if you don't want to allow users to be able to write to the database, you can hit the "Options>>" button and check the "Read Only" checkbox. You could also enter a user ID and Password in the Advanced dialog if your database requires it.

2: Design the Query (or Queries)

Every time the getListItems.asp script is called it runs a stored query (also known as a stored procedure) in the database. I like keeping the queries in the database because they're a lot easier to design and test from within the database. If you open up the Vehicle lists 2000.mdb database and look at the Queries Objects you'll see that there is a query for each URPM. In a larger scale database such as MySQL or Oracle, you could develop one stored procedure to handle the retrieval for all the lists' items! Unfortunately, you can't pass table or column names as parameters in Access, so you need one for each. To keep some consistency between the queries and the HTML, I named the queries "get" + the Select list's ID. For example, the query name for the Manufacturers Select element is called getManufacturers. Use whatever names fit your selects' contents, but make sure to name your queries "get" plus the select's ID. See line 32 in the getListItems.asp file to see how the list's ID is used to derive the query name.

To design the query for the base list, all that's required is to select the ID and Description fields from the table, in that order. The query below weeds out the null rows, but that might not be an issue for you. Finally, use an ORDER BY clause to sort if you wish. This doesn't affect the URPMs' behavior. The following SQL retrieves the IDs and descriptions from the Manufacturer table and sorts the results by the description:

The queries for the child lists require slightly more SQL code because of table joins and the ID parameter, but they're still quite simple as far as SQL statements go. Since we want all the items that are related to the parent ID, there has to be a foreign key in the table linking back to the parent ID field. Here's the SQL for the getLevels query:

These queries bring back a much smaller recordset than the version III URPMs query because that version had to not only get the contents for all the lists, but it had to maintain their relationships as well. This had a tendency to result in a lot of repetition. Just have a look at this small sample of the deprecated getLists query:

Sample Output from the URPMs III getLists Query

Compare that with the typical output of one of the new queries. Here's the list of model levels that relate to an Audi model:

Sample Output from the getLevels Query

Yep, that's the whole thing. Notice the difference in number of rows: 5 vs. 919! That's the difference that retrieving list options when a selection is made as opposed to when the page first loads makes.

[next] [next]

Universally Related Popup Menus AJAX Edition: Part 1 / Page 2

Universally Related Popup Menus AJAX Edition: Part 1

Running the Sample Script

Try it out!

Using the URPMs in Your Own Web Page

Find a programming school near you