Reading the foreign keysBefore I start, I'm assuming that you understand what a Primary Key and a Foreign Key is. If not, read MSDN's description.My new code consists of 3 functions in my SSI, utils/database.asp. You can download the entire site, including this file, for free. The first of the three functions is DBGatherForeignKeys, and should be called from any page that will want to display list boxes containing the data defined by the foreign keys. This function queries the database for all the foreign keys, builds the HTML for the list boxes and stores it in Application variables. function DBGatherForeignKeys ( )
{
if ( !Application ( 'GatheredForeignKeys' ) )
{
DBInitConnection ( );
oRecordSet = oConnection.OpenSchema ( adSchemaForeignKeys );
|
Don't worry about calling this function from all your pages - it only does anything the very first time it is called. This is handled by the line that tests if there is a GatheredForeignKeys variable set. If there is then the function does nothing. The next two lines open the database, and then fill a recordset with a list of all the foreign keys in the database. var sRefTables = new Array;
var sRefColumns = new Array;
var sForeignTables = new Array;
var sForeignColumns = new Array;
var nForeign = 0;
while ( !oRecordSet.EOF )
{
sRefTables [ nForeign ] = '' + oRecordSet ( 'FK_TABLE_NAME' );
sRefColumns [ nForeign ] = '' + oRecordSet ( 'FK_COLUMN_NAME' );
sForeignTables [ nForeign ] = '' + oRecordSet ( 'PK_TABLE_NAME' );
sForeignColumns [ nForeign++ ] = '' + oRecordSet ( 'PK_COLUMN_NAME' );
oRecordSet.MoveNext ( );
}
|
Next we create some arrays to hold the information about the foreign keys temporarily, and loop through the recordset filling them up. nForeign is incremented each time, so it ends up holding the number of foreign keys contained in the arrays. Part 3: Creating the list boxes... | 
