WebReference.com - Universal Related Pop-up Menus - Single Form Version II - (4/6)
URPM: Single Form Version II
Part 2: Retrieving the list contents from a database
One of the weaknesses of using arrays to represent the list data is that they can be very time-consuming to maintain. The problem lies in the fact that the element indexes are always incremented by 1. As a result, in order to insert new items in the middle of the list, you have to adjust all the subsequent indexes accordingly. To get around this, I wrote a small application to update the lists from an Access database using Visual Basic. More recently, I adapted the original program to VBScript, to be used within an ASP page. I used these tools because people asked me specifically about them, and they are among some of the easiest to use. If your data needs to be updated more than once a month, you may want to consider this approach. Performance will take a slight hit because the script has to go to the database every time it sends the page, but this is almost always the case with any dynamically generated page.
Once you've downloaded the zipped file and extracted the contents to a folder, you should have a total of 10 files:
- A copy of this article and accompanying 3 image files.
- A sample ASP page.
- A sample Access database.
- 2 ".inc" files containing VBScript code.
In order to run the example, you'll need a server that can execute ASP scripts. For testing purposes, I use Microsoft's Personal Web Server. It was designed for testing small applications such as this and is very easy to use. You can download it here for free. The personal Web server is part of a suite of related Web development products. After you select your operating system, you have to download a small program which will take you through the steps to install it. Be forewarned, the entire download file is about 23 megs, so make sure you have sufficient space on your hard drive. Maybe it's time to burn some of those mp3's to a CD?
To try it out, place the ASP page, database, and 2 .inc files in the root folder of the server. In the PWS it's called wwwroot. Then, bring up the ASP page in a browser via the server by typing the following in the address box:
Note that your server name may be different. If your server is listening on a different port, you must append it to the server name, using a colon:
This line goes at the very top of your page (even before the opening <HTML> tag). If your server does not allow server side includes, just copy and paste the contents of the getListData.inc file directly into your Web page. There are 2 pieces of information that you will have to add to the file for it to work properly: The name of your database and the name of the query to retrieve the data:
'*SET THE FOLLOWING LINE TO YOUR DATABASE:---------------------------- db = "vehicle lists '97.mdb" '*SET THE FOLLOWING LINE TO YOUR QUERY:------------------------------- query = "getLists" '---------------------------------------------------------------------
Finally, insert the showErrors.inc file somewhere in the document body. It really depends on where you want errors to be displayed. You could even skip this part, if you don't care what happens if an error occurs, but be prepared to see some pretty obscure error messages, instead of your Web page!
<body onLoad="initLists(BaseArray, BaseMenu, 1stSubcategoryArray, 1stSubcategoryMenu, 2ndSubcategoryArray, 2ndSubcategoryMenu)">
To refresh the list contents when the user selects an item, you need to call the relate function from the onChange event of each menu, except the last one, since it does not affect any other menus:
The first argument is the listbox itself and never varies. The second one is the array which contains the linked list items. It should never vary either, since this variable is hardcoded in the ASP script. The only argument that you have to worry about is the last one. It denotes the depth of the list items in the m array, starting at 1 for the base list. In the example page, 1 is the Manufacturer list, 2 is the Model list, and 3 is the Model Level list.
The script extracts the data from the database by running the query you specified earlier, along with the database name, in the getListData.inc file. Most ASP scripts I have seen run the SQL code directly from the script, but I like the idea of having it run from within the database, much like a stored procedure. This approach offers several advantages, including making the script shorter and giving you the ability to write and test your query right in Access using the Query design screen.
Here is how my query looks when I run it in Access:
If your column name follows the above rules and is not in the reserved word list, then you're in the clear. Otherwise, you will have to change the name in the query. There are two ways to do this. The first would be to precede the column name with the new name and a colon in the query design window. In the following example, the original tables' description column names are innapropriate because of the spaces:
The other way would be to insert the code directly into the select part of the SQL statement:
SELECT Manufacturer.[Manu Name] AS Manufacturer, Manufacturer.[Manufacturer ID], Model.[Mod Name] AS Model, Model.[Model ID], Level.[Lev Name] AS Level, Level.[Level ID]
That's all there is to it. With your query and scripts in place, you should be able to link up as many menus as you need. Remember to view the page through the Web server, as the script will only run when it's interpreted by the server.
Written by Robert Gravelle and
Created: October 2, 2001
Revised: October 2, 2001