WebReference.com - Universal Related Pop-up Menus - Single Form Version II - (5/6)

[previous] [next]

URPM: Single Form Version II

The VBScript Code

For those of you who want to customize the script further, here is a line by line explanation of what the ASP code does:

The first line: <%@ LANGUAGE="VBSCRIPT" %>, found at the top of the getListData.inc file, tells the server that we are using VBScript. ASP also supports JavaScript and PerlScript, but when dealing with Microsoft products, it's usually easiest to stick with their programming languages too.

A couple of lines down, you'll notice the "Option Explicit" phrase before any variables are declared. By inserting this code, we are telling the ASP parsing engine that we want to explicitly declare all our variables via the Dim statement. Using Option Explicit allows us to easily catch misspelled variable names - a problem which could spell trouble if we're not careful.

After I declare some variables, it says: "On Error Resume Next". Without this important line of code, the script would bomb if anything went wrong - even the most minute error would cause the server to display an ugly error message instead of the page. Something like:

Microsoft OLE dB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Could not find file '(unknown)'.
/request_form.asp, line 11

Since the script is only responsible for setting up the list boxes, we should still get the page to load, even if the menus don't initialize properly. This line tells the script interpreter to continue running the script until it's done. While there can be no way to know what the heck will come out, at least it won't crash and burn. Unlike the full Visual Basic language, VBScript does not offer any other options for error handling. You either forge on, or quit right there. I'll be getting back to how I handled errors on the next page.

Connecting to the Database:

There are three types of database connections in ASP: system DSN, file DSN and DSN-Less. Many developers promote one above the others, but the truth is that all three have their strengths and weaknesses, so it's a good idea to become familiar with each of them. If you run your own dedicated Web server and have administration rights, it is usually easiest to create a server DSN, using the "ODBC Data Source Administrator" utility that comes with Microsoft's operating systems. When you create a system DSN, the database information is stored in the registry on the Web server. This makes connecting to the database a snap, as you would simply provide the name of the DSN:

Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "DSN=MyDSN"

The ODBC Data Source Administrator can also be used to create a file DSN. The information it contains is the same as a system DSN, but a file DSN is a lot more portable, because everything you need to connect to your database is stored in a text file:

[ODBC] 
DRIVER=Microsoft Access Driver (*.mdb) 
ReadOnly=0 
UserCommitSync=Yes    
Threads=3 
SafeTransactions=0 
PageTimeout=5 
MaxScanRows=8 
MaxBufferSize=512 
ImplicitCommitSync=Yes    
FIL=MS Access 
DriverId=25

Besides being portable, a file DSN also offers tremendous flexibility in the types of connections you can set up. For example, you could allow users to have read-only access to the database by changing the ReadOnly attribute in the file above to 1. Once you've uploaded it to your directory on the server, you would connect to the database by providing the full path to the file:

Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "FILEDSN=c:\dsn\MyDB.dsn"

The downside to using either the system or file DSN is that both entail some overhead, because the server has to look up the database information on the hard drive. This will likely cause some performance degradation if you get many concurrent hits on your server. A DSN-Less connection sidesteps this problem by passing all of the database information directly to the Open subroutine. For this reason, the DSN-Less connection should be your preferred method for very large sites. This is how I establish a DSN-Less connection in my script:

Set MyConn  = Server.CreateObject("ADODB.Connection")
MdbFilePath = Server.MapPath("vehicle lists '97.mdb")
MyConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"

We now have a connection to the database through which we can extract the list items. This is done by executing an SQL command, using the following code:

 Set cmd = Server.CreateObject("ADODB.Command")
 Set cmd.ActiveConnection = MyConn
 cmd.CommandText = "getLists"	'this is the name of the query in Access
 set RS = cmd.Execute

The next several lines define some variables which we will be using:

      'used to store the field values
      Dim lastID()
      ReDim lastID( RS.Fields.count / 2 )
      'used for keeping track of the JS array indexes
      Dim JSindex()
      ReDim JSindex( RS.Fields.count / 2 )

We only need enough elements for each menu level (3 in this case), but since there are two columns for each of them, I had to divide the number of fields by 2. I could have used one variable for each field, but that wouldn't be very generic. VB does not allow us to set an array's size to a variable so we had to create an empty array and then redim it to the proper size - thus creating a variable length array. The lastID array stores the most current value of the Value columns in the recordset. We know that we've reached a new item every time these no longer match the current record because they are sorted by the value columns, in ascending order. The JSindex array holds the JavaScript array indexes. There will be one for each JS array.

The next bit of code creates the JavaScript array which will hold the list data:

      Response.write("<"+"SCRIPT Language='JavaScript'>" & vbCrLf)
      Response.write( vbTab & "var m     = new Array();" & vbCrLf)
      'Define the JS arrays to the Table Field Names and initialize the indexes to 0
      For i=0 To RS.Fields.count - 2 Step 2
        lastID( i / 2 )  = RS(i+1)	'initialize to the first row ID's
        JSindex( i / 2 ) = 0
      	Response.write( vbTab & "var " & RS(i).name & " = new Array();" & vbCrLf )
      Next

This loop iterates through every second field, stores the IDs and sets the JavaScript arrays to 0. We have to initialize the array elements because VBScript wouldn't know what to set them to. At the same time, the script writes out the JavaScript arrays, using the description column name as the name of the array (bold text). That's why it is so important to use JavaScript safe names!

Another loop is then used to write out the first items in each of the JavaScript arrays. Using loops allows the script to work with any number of menus:

	'write out the first row
	For i = 0 To RS.Fields.count - 2 Step 2
          writeJSArray RS, i, JSindex, false
 	Next

It calls a subroutine to do the actual writing, called writeJSArray:

      Sub writeJSArray(ByRef RS, ByVal i, ByRef JSindex, ByVal blankVal)
        Dim val
        If (blankVal) Then
          val = ""
        Else
          val = Q & RS(i) & Q & ", " & Q & RS(i+1) & Q
        End IF
        Response.write( vbTab & space(i) & RS(i)Name & "[" & JSindex(i/2) & "] = new Array(" & val & ");" & vbCrLf )
        JSindex(i/2) = JSindex(i/2) + 1	'increment the JS index
      End Sub

This is a sub, and not a function, because it does not return anything. It takes 4 parameters: the recordset containing the data, the current column index, the current JavaScript array index, and a Boolean indicating whether or not we want an empty array. To eliminate any confusion, I added the ByRef and ByVal attributes to each parameter. ByRef tells the interpreter that we want to use a pointer to the actual value in memory. ByVal denotes that we want to use a copy of the variable. This distinction is an important one because variables passed ByRef can be changed by the sub or function, while those passed ByVal cannot. We don't need to change the recordset, but objects are always passed by reference in VB and VBScript. We do need to pass the JSindex by reference, because it is incremented by the sub routine. The first part of the sub sets the array's contents. If blankVal is false, it sets it to a blank string. Otherwise, it is set to the description and value fields respectively, with each of them surrounded by quotes. I used a variable to hold the double quote character to differentiate between the JavaScript output and the VBScript code. The "Response.write" line is what writes the data on the page. Again, it uses the description field to set the name of the JavaScript array. The last line increments the JavaScript array index. Here is what is produced by the loop, in the example:

manufacturer[0]=["ACURA", "ACU"];
  model[0]=["CL", "CL"];
   level[0]=["2.2 PREMIUM", "2.2"];

After we move the cursor to the next record we enter a large Do While loop, to process the remaining records. The check for the end of file (RS.EOF) is not essential the first time through the loop, so I could have used a post-check, but the standard pre-test loop tends to be safer.

One of the most challenging aspects in writing this code was the fact that I used multiple MoveNext statements inside the same loop. This can be dangerous because there is code which expects a valid record before the loop checks for the end of file marker. After several attempts at restructuring the loop to avoid this, I felt that it was easier to follow the code the way it was. To avoid a run time error, I checked for the end of file immediately after the MoveNext statement. Ironically, it is this line of code that exits the loop, and not the main loop check!

        'get the last sub-category
        i = RS.Fields.count - 2	'set i to the last subcategory description
        While lastID(ubound(lastID) - 2) = RS(i-1)
	  writeJSArray RS, i, JSindex, false
          RS.MoveNext
          If RS.EOF Then Exit Do
        Wend

The loop checks the current value field of the second to last menu against the stored one - that's why I subtract 1 from the i, and 2 from the ubound(lastID). As long as they match, the script writes out the innermost menu array. In our example, it would be the following lines:

   level[1]=["2.3", "2.3"]; // the script always writes out both the text and the values
   level[2]=["3.0", "3.0"];

Once the fields no longer match, the script writes out empty JavaScript array elements ([]) until the previous field's value is equal to the stored one. The new values are also stored for the next time through the loop.

	'write out blank JS array elements
        For i = RS.Fields.count - 2 To 2 Step -2
          If RS(i-1) = lastID(i/2 - 1) Then
            Exit For
          Else
            'Write a blank value
            writeJSArray RS, i, JSindex, true
            lastID(i/2 - 1) = RS(i-1)  'store the id for comparison
          End if
        Next

The next bit of code, which is used to write out the next row, is identical to the code which precedes the main loop, except that this time i is set to itself because it already holds the correct value:

        'write the next row
        For i = i To RS.Fields.count - 2 Step 2
          writeJSArray RS, i, JSindex, false
 	Next
        RS.MoveNext

At the point that the script exits the loop, all of the JavaScript arrays have been populated, except for the last empty ones. This is because the loop exits immediately after writing out the innermost array elements. This is then done with the following code:

      'finish the JS arrays
      For i = RS.Fields.count - 2 To 2 Step -2
        'Write a blank value
	writeJSArray RS, i, JSindex, true
      Next

Finally, we write out the closing JavaScript tag and clean up:

      Response.write(vbCrLf & "</" & "SCRIPT>")
      RS.Close
      set RS = Nothing
      MyConn.Close
      set MyConn = Nothing

[previous] [next]

Written by Robert Gravelle and Created: October 2, 2001
Revised: October 2, 2001

URL: https://webreference.com/dev/menus/oneformv2/5.html