Creating the list boxes

Note: The code that follows makes an assumption about how your tables are designed. The foreign tables MUST have the values that will appear in the list box as the second field. (It is implied, though not necessary, that the first field is the primary key)

for ( i=0; i<nForeign; i++ ) {    DBGetRecords ( 'SELECT * FROM ' + sForeignTables [ i ] );    try    {       var sList = '<select name="' + sRefColumns [ i ] + '">';       while ( !oRecordSet.EOF )       {          // I assume that the second field is the one to show in dropdown list          sList += '<option value="' + oRecordSet ( sForeignColumns [ i ] ) + '">'          sList += oRecordSet ( 1 ) + '</option>';          oRecordSet.MoveNext  ( );       }       sList += '</select>';       Application ( sRefTables [ i ] + ':' + sRefColumns [ i ] ) = sList;    }    catch ( e )    {       DebugOut ( '<p>Failed to create dropdown list for ' + sRefTables[i] + ':' + sRefColumns[i]);    } }

Here I loop through the foreign keys, first getting all the values from the foreign table with a simple SELECT query. I then wrap the code in a try..catch statement to catch any errors that occur when accessing the recordset - for example if the table has no second column.

The data in the recordset is then used to fill the sList variable with the relevant <select><option></option>...</select> HTML for the list boxes.

The sList variable is then stored in an Application variable whose name is formed from the primary table name and the relevant field name.

As an example, here is the actual data stored in my live Application ( 'Cars:MakeID' ) variable:

Which, when used on a page becomes this: undefined

The catch statement simply displays an error message when the page is being debugged - read more about this in my article.

DBReleaseConnection ( );       Application ( 'GatheredForeignKeys' ) = true;    } }

Lastly, the connection to the database is released and the GatheredForeignKeys variable set to stop this happening again until the server is restarted (when all Application variables will be lost).

Part 4: Using the list boxes...