GenericDB Help - Jump to:

  • The Config File
  • Quick Reference
  • Unabridged Reference (with explanations)
  • Click here to download this Help reference in Adobe Acrobat format.

    The Config File

    The Config File is your interface to GenericDB. It contains the settings which guide how GenericDB displays your data. This is a minimal Config File, see the Reference sections below for syntax and explanations:

    <%
    ' Minimal interface to the Northwinds Employee table.
    Session("dbGenericPath") = "/Eli/ASP/"
    Session("dbExitPage") = "http://www.ofifc.org/Eli/ASP/GenericArticle.asp"
    Session("dbTitle") = "Northwinds Employee Table"
    Session("dbConn") = "dbEli"
    Session("dbRs") = "Employees"
    Session("dbKey") = 1
    Session("dbDispList") = "011101000000100010"
    Session("dbViewPage") = Request.ServerVariables("PATH_INFO")
    Response.Redirect Session("dbGenericPath") & "GenericList.asp"
    %>

    Quick Reference

    ** Denotes a new or updated feature.
    Setting Description
    Session("dbBodyTag") = "VLINK=#CC9999 ALINK=#CC9999 LINK=#CC9999" (build a custom body tag)
    ** Session("dbBoolean") = "CHECKBOX" (display a checkbox for yes/no fields instead of radio buttons)
    Session("dbBorderColor") = "Navy" (colour to display table borders, defaults to #99CCCC.)
    Session("dbCanAdd") = 0 (or 1, defaults to 0)
    Session("dbCanDelete") = 0 (or 1, defaults to 0)
    Session("dbCanEdit") = 0 (or 1, defaults to 0)
    Session("dbCombo") = "LIST, value1, desc1, value2, desc2,..." (values to store and descriptions to display in a pull-down/combo box)
    Session("dbCombo<x>") = "TABLE, tablename, RowForVal, RowForDesc, BlankVal, BlankDesc, WhereClause"  
    Session("dbConfirmDelete") = 0 (or 1, defaults to 0)
    Session("dbConn") = "myDSN" (Data Source Name or DSN connection)
    Session("dbCSV") = 0 (or 1, defaults to 0. Adds a link from the Lister to display the table as a CSV (comma-delimited) file)
    Session("dbCSVQuote") = 1 (or 0, defaults to 1. If dbCSV is set to 1, this setting controls whether string fields are padded with quotation marks.)
    Session("dbDebug") = 0 (or 1, defaults to 0)
    Session("dbDefault<x>") = <value> (default value for field x, use any value appropriate for the field)
    Session("dbDispEdit") = "0211" (field display settings for the edit screen. 2=Read-only, 1=Read/write, 0=Do not display. In the example, field 1 is not displayed, 2 is read-only, and 3 and 4 are editable.)
    Session("dbDispList") = "011101" (1's or 0's representing whether each field will/won't be displayed)
    Session("dbDispNew") = "10010" (controls which fields are displayed when editing a new record, defaults to the value of dbDispEdit.)
    Session("dbDispView") = "011101" (1's or 0's representing whether each field will/won't be displayed)
    Session("dbEditTemplate") = "tblEmployeesEdit.htm" (The Custom Edit Template file, to be found in the same directory as the Config File.)
    Session("dbEMailFor3") = 4 (Display field 3 as a link to the E-mail address stored in field 4.)
    ** Session("dbExcel") = 1 (or 0, defaults to 0. Adds a link from the Lister to open the table in MS-Excel.)
    Session("dbExitPage") = "http://www.home.com/myhomepage.htm" (URL of the page to exit to)
    Session("dbExitPageText") = "Back to Main Menu" (text for the Exit link, defaults to "Back")
    Session("dbFields") = "field1, field2, field3" (SELECT clause or Field List, defaults to all fields)
    Session("dbFieldNames") = "field1, field2, ..." (defaults to actual Field Names, can customize here)
    Session("dbFont") = "Times New Roman" (font list, defaults to "Verdana, Arial, Helvetica")
    Session("dbFontSize") = 2 (0 to 4, defaults to 2)
    Session("dbFooter") = 1 (use GenericFooter.asp as footer on each page, default is 0 for no footer.)
    ** Session("dbFormatDateTime") = "00021032" (Display settings for date/time fields: 0=General, 1=LongDate, 2=ShortDate, 3=LongTime, 4=hh:mm)
    Session("dbGenericPath") = "/mypath/" (relative path to the GenericDB files)
    Session("dbGroupBy") = "field list" (a GROUP BY clause for SQL queries)
    Session("dbHaving") = "Count(myField) > 1" (a HAVING clause for SQL queries)
    ** Session("dbHeader") = 1 (use GenericHeader.asp as a page header, default is 0 for no header.)
    Session("dbHidePageNumbers") = 1 (suppresses Page Numbers, defaults to 0)
    Session("dbKey") = 1 (field number to use as the Key field, usually an Autonumber field)
    Session("dbMenuColor") = "Navy" (Colour to display menus, defaults to #99CCCC.)
    ** Session("dbMenuTextColor") = "Navy" (change the color of menu text and links, default is black.
    ** Session("dbOnlyAdd") = 1 (returns to the dbExitPage after submitting the record, default is 0. See detailed notes below.)
    ** Session("dbOnlyEdit") = 1 (returns to the dbExitPage after editing the record, sefault is 0. See detailed notes below.)
    Session("dbOrder") = 2 (field number to sort by, make it negative to do the reverse sort)
    Session("dbOrderBy") = "field1 DESC, field2 ASC, etc." (ORDER by clause)
    Session("dbRecsPerPage") = 15 (number to display per page, default is all)
    Session("dbRequiredFields") = "011101" (1's or 0's representing whether a red * is displayed beside each field. Does not validate data, just displays the star.)
    Session("dbRs") = "mytable" (A table name or FROM clause)
    ** Session("dbSearchAdvanced") = 1 (activates an Advanced Search link which helps the user with a field-by-field search, default is 0.)
    ** Session("dbSearchEnhanced") = 1 (adds option below the regular search box for a user to search "any of these words" or "exact phrase", defaults to 0.)
    Session("dbSearchFields") = "011101" (1's or 0's representing whether each field will/won't be searched. Note: Only string and memo fields are searchable.)
    Session("dbSearchPos") = "TOP" (Position of the Search box, "top" or "bottom", defaults to bottom.)
    Session("dbStartRec") = 51 (a record number to start the display at, default is 1)
    Session("dbSubTable") = "Title, ConfigFile, LinkingField"  
    Session("dbTitle") = "My Page Title"  
    Session("dbTotalFields") = "00101" (Totals the specified fields at the bottom of each page, in this case fields 3 and 5.)
    Session("dbType") = "Access" (or SQL or UDF, defaults to Access)
    ession("dbUpdateField<x>") = <value> (After updating a record, field x will be set to this value.)
    Session("dbURLfor3") = 4 (display field 3 as a link to the URL stored in field 4)
    Session("dbViewPage") = Request.ServerVariables("PATH_INFO") (DO NOT CHANGE)
    Session("dbViewTemplate") = "tblEmployeesView.htm" (The Custom View Template file, to be found in the same directory as the Config File.)
    Session("dbWhere") = "fruit='apples'" (a WHERE clause)


    Unabridged Reference

    Session("dbBodyTag") = "VLINK=#CC9999 ALINK=#CC9999 LINK=#CC9999"
    Define a custom body tag, including BACKGROUND, BGCOLOR, LINK colour tags (as above), and more. Often, single quotes are allowed around tags: Session("dbBodyTag") = "BACKGROUND='MYGIF.GIF' BGCOLOR='white'"

    If you have trouble with this, spend a couple lines at the beginning of your config file building the body tag string of your dreams:
    QUOTE = chr(34)
    txtBodyTag = "BACKGROUND=" & QUOTE & "MYGIF.GIF" & QUOTE & " BGCOLOR=" & QUOTE & "WHITE" & QUOTE
     and set: Session("dbBodyTag") = txtBodyTag

    Session("dbBoolean") = "CHECKBOX"
    By default, boolean yes/no fields are displayed as radio buttons in the Editor like this:Yes No
    Setting dbBoolean to "CHECKBOX" changes the display to a checkbox like this:

    Session("dbBorderColor") = "Navy"
    Change the colour of the menu borders.

    Session("dbCanAdd") = 1
    Can the user add a record? 1 means yes, 0 means no.

    Session("dbCanDelete") = 1
    Can the user delete records? 1 means yes, 0 means no.

    Session("dbCanEdit") = 1
    Can the user edit records? 1 means yes, 0 means no.

    Session("dbCombo") = "LIST, <value1>, <desc1> [, <value2>, <desc2>]"
    Example 1: Session("dbCombo11") = "LIST, ??, Unknown, CA, Canada, US, United States, DE, Denmark"
    OR
    Session("dbCombo") = "TABLE, <tablename>, <RowForVal>, <RowForDesc>, <BlankVal>, <BlankDesc>, <WhereClause> "
    Example 2: Session("dbCombo3") = "TABLE, tlkpProvince, 1, 2, ??, Unknown, Country='Canada'"
    Display a "pull-down" combo box to edit a field. Options will be drawn from either a list or a lookup table. Example 1 makes field eleven a combo box (hence dbCombo11) taking values from a list. Example 2 works on field three (dbCombo3) taking values from a table.

  • LIST: The rest of the line is a list of paired values (like Canada, CA). The first of the pair is the value actually stored in the table, the second shows up on the screen in the pull-down.
  • TABLE: Parameters passed are the table name (which must be in the same database/connection/DSN as the table currently being displayed), the number of the field/row which contains the value you want stored, the number of the field/row which contains the text to display, the value to store when the field is blank, and the value to display when the field is blank. The final parameter is an optional WHERE clause (Country='Canada' in Example 2 above). If you include a WHERE clause it cannot contain commas.

    Session("dbConfirmDelete") = 1
    0 = Don't confirm (default), 1 = Confirm deletions. A screen will be presented to the user to confirm that the record should be deleted.

    Session("dbConn") = "dsnNorthwinds"
    The name of the ODBC System data source name (DSN) as it's set up on your server. If your data source requires a username and password, this string should have the following format:
    "DSN=dbEli;UID=sa;Password=mypass"

    If you have an Access database without a DSN you can create a DSN-less connection by using a string with the following format:
    "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=c:\database\mydata.mdb"

    Session("dbCSV") = 1
    A value of 1 displays a View CSV link in the Lister which displays the current table as comma-delimited plain text, suitable for saving into a text file. The local text file can then be imported into virtually any database format, including those used by electronic organizers. Defaults to 0 for no such link.
    See also dbCSVQuote, below.

    Session("dbCSVQuote") = 1
    When dbCSV (above) is set to 1, this setting may be used to specify whether text fields should be enclosed in quotation marks. For example, a table with a numeric field, a text field and another numeric field might be displayed on a single line like so:
    3, "my text field here", 2
    If set to 0 (the default) the same line would be output like this when the user clicks the "View CSV" link:
    3, my text field here, 2

    Session("dbDebug") = 1
    This value of 1 or 0 decides whether the link to the dbInfo page is shown. Once you're done setting up the field display lists you will probably want to change this to 0.

    Session("dbDefault6") = Date()
    Session("dbDefault11") = "Canada"
    Use this format to set defaults for new records. The first example sets field 6's default value to Date(). In the tblEmployees.asp example this sets HireDate to today's date. The second sets the default value of field 11 (Country) to Canada.

    Session("dbDispEdit") = "011111111111111111"
    Sets which fields appear on the Edit page. Same format as dbDispList and dpDispView.

    Session("dbDispList") = "0111010000001000"
    This string of 1's and 0's decides which fields will be shown on the Lister page. 1 means show, 0 means don't show. In this case we're displaying fields 2, 3, 4, 6, and 13 as shown by the positions of the 1's. The best way to construct this string is to start it off with "11111", save it, open the Lister in your browser, head to the dbInfo page and read off the field list as you type the 1's and 0's into your editor.

    Session("dbDispNew") = "0111010111110111"
    Sets which fields appear on the Edit screen when a new record is being created. If this is not set, the fields displayed will default to those set in dbDispEdit.

    Session("dbDispView") = "1111111111111111"
    This string of 1's and 0's decides which fields will be shown on the Viewer page. It is formed the same way as Session("dbDispList"), above. Leaving it blank (Session("dbDispView") = "") will prevent the View links from being displayed.

    Session("dbEMailFor3") = 4 This setting displays a field as a link (in this case, field 3) using the E-mail address stored in another field (in this case, field 4). If field 3 contains "Eli" and field 4 contains "erobillard@ofifc.org" this would display as: Eli.

    Session("dbEMailFor4") = 5
    This setting displays a field as a link using the E-Mail address stored in another field. In this example, the 4th field (possibly the employee's name) will be displayed as a link to the URL stored in field 5 (the employee's E-mail address).

    Session("dbExcel") = 1
    A value of 1 displays a View Excel link in the Lister which generates an Excel-compatible file for download or display. Defaults to 0 for no such link.

    Session("dbExitPage") = "http://www.ofifc.org/Eli/"
    Each page has an "Exit" link and this is where you decide what page that link goes to.

    Session("dbFields") = "EmployeeID, Title, UserLevel, ParkingPass"
    Sets the list of fields to display. That's right, you're no longer limited to selecting all the fields. See the dbGroupBy setting below for a tip on how to use this to build complex queries.

    Session("dbFieldNames") = "<field1>[, <field2>, <field3>, ...]"
    Session("dbFieldNames") = "EmployeeID, Last Name, First Name, Title"
    Set field names to use when displaying the data instead of the raw field names from the database.

    Session("dbFont") = "Arial, Helvetica"
    Set the font used by GenericDB.

    Session("dbFontSize") = 2
    Set the font size used to display data.

    Session("dbFormatDateTime") = "00021032"
    This is a string of 1's and 0's similar to dbDispList and dbDispEdit. Each number indicates how the corresponding field is to be displayed. To format your Date / Time fields, set the values according to the list below. Settings of 1, 2, or 3 use the Regional Settings (in Control Panel) of the server to determine how the format is displayed.
    In the example above, the 4th field will be shown as a ShortDate, the next as a LongDate. 0=General (No formatting, you can also use this value for non-date/time fields)
    1=LongDate
    2=ShortDate
    3=LongTime
    4=hh:mm

    Session("dbFooter") = 1
    Set this variable and modify the GenericFooter.asp file to include a standard footer on each page. Note that the extension of the footer file has changed from .inc to .asp in recent versions as a security precaution.

    Session("dbGenericPath") = "/Eli/ASP/"
    The relative path of your Generic Database files (GenericList.asp, GenericEdit.asp, etc.) This path is used to build the links on the pages.

    Session("dbGroupBy") = ""
    Sets a GROUP BY clause.

    Tip! To execute a relational query (great for building reports)

    1. Build a SQL query in any SQL Query Editor. In Access, build your relational query in design mode and then select the SQL view.
    2. Copy everything after GROUP BY into the dbGroupBy variable
    3. Copy the text after ORDER BY into the dbOrderBy variable.
    4. Copy the text after WHERE into the dbWhere variable.
    5. Copy the text after FROM into the dbRs variable.
    6. Copy the text after SELECT into the dbFields variable.
    7. Set dbCanEdit, dbCanAdd, and dbCanDelete to 0.
    8. Set Session("dbType") = "UDF"

    Note! If your query JOINs two or more tables then Edits, Adds and Deletes should always be disabled. Attempting these operations will most likely generate errors.

    Session("dbHeader") = 1
    Set this variable and modify the GenericHeader.asp file to include a standard footer on each page. Note that the extension of the header file has changed recently from .inc to .asp as a security precaution.

    Session("dbKey") = 1
    A unique key field, in this case it is the first field in the table (Field 1) or the [Employee ID] field. This should always be a Counter or Auto-Number field. Why? It is the only way to guarantee a unique identifier for each record. Auto-Numbers should be assigned randomly if possible (this is an option in Access 97).

    If you don't have a Key field then set this value to 0, but you will not be able to Edit or View records from the lister since there is no guaranteed unique key to load the pages. Note that the Key field is not editable in the GenericEdit screen to encourage the use of Counters and Auto-Numbers.

    Session("dbMenuTextColor") = "Navy" Change the colour of text in the menu tables including the page title, field names, etc. Defaults to black.

    Session("dbOnlyAdd") = 1
    To skip the lister and drop the user straight in to add a new record, use this switch. Requires changing the last line in the Config File to:
    Response.Redirect Session("dbGenericPath") & "GenericEdit.asp?CMD=NEW"

    Session("dbOnlyEdit") = 1
    To skip the Lister and drop the user straight in to edit a record, use this switch. The Key value of the record must be set in a Sessin var before the config file is loaded, and the last line in the Config File must be changed to:
    Response.Redirect Session("dbGenericPath") & "GenericEdit.asp?KEY=" & Trim(Session("myKeyValue"))

    Session("dbOrder") = 2

    The field to sort the records by. In this case we've chosen the second field (Field 2) or [Last Name].

    Session("dbOrderBy") = "[Last Name] DESC, [First Name]"
    Sets an ORDER BY clause. dbOrderBy uses a full string to set sort order as opposed to dbOrder which only takes a single field number to sort on. Note that dbOrder has precedence, if you have both variables set, dbOrderBy will be ignored. See Session("dbGroupBy") above for a great tip to form a relational query using both dbGroupBy and dbOrderBy.

    Session("dbRecsPerPage") = 10
    Sets the number of records to show per page and displays Previous and Next buttons for navigation.

    Session("dbRs") = "Employees"
    The name of the table in the ODBC datasource.

    Session("dbSearchAdvanced") = 1
    This setting adds an "Advanced Search" link where the user can specify a field-by-field search of the recordset, including whether to AND or OR the search terms. Currently this feature does not support numeric or date fields. The default is 0.

    Session("dbSearchEnhanced") = 1
    This setting enhances the regular search box (see dbSearchFields) on the Lister screen. The new options which appear below the searhc box allow the user to specify whether to match "any of these words" (search words are OR'ed), or "exact phrase" (search words are treated as a single string).

    Session("dbSearchFields") = "011100010010001000"
    When this variable exists, a Search box appears on the List page. The 0's and 1's determine which fields are searched, and are limited to string and memo fields for now (no dates, no numbers, no greater than or less than, just text).

    Session("dbStartRec") = 1
    Indicates which record to begin displaying at. Useful to force GenericDB to start at 1, or if for some reason you want to start from the middle. GenericDB is "smart" enough to begin at 1 each time you use a different recordset, but if you have two Config Files for different views of the same recordset, you may need to force it to begin at 1.

    Session("dbSubTable") = "Title, ConfigFile, LinkingField"
    Session("dbSubTable") = "Access Log, tblAccessLog.asp, EmployeeID"
    Allows sub-tables. This example presumes a table exists wshich contains access logs. The title Access Log will be the displayed on a link beside each user listed in the Lister, tblAccessLog.asp will be the asp file (use the same format as any other Config File but NO dbWhere setting and your dbExitPage points back to the main table's config file), and EmployeeID is the key field used to set the relationship.

    Session("dbTitle") = "Northwinds Employee Table"
    This is where you set your page's title.

    Session("dbType") = "Access"
    Possible values: SQL, Access, or UDF. Default is Access.
    This declares the type of database driver used on the host. Microsoft Access allows spaces in table and field names and therefore requires [brackets] around table and field names to identify where the names start and stop. SQL hosts do not allow spaces and therefore don't know anything about the use of brackets around field names.

    In GenericDB all queries are built using the brackets, and setting this switch to "SQL" will have it strip the brackets out using the Replace() function.

    The "UDF" option should be set for any query which uses a JOIN, or where the brackets you specify yourself are not to be tampered with when GenericDB forms the query. Brackets are neither added nor stripped, the query is submitted "as is."

    Session("dbURLfor2") = 20
    This setting displays a field as a link using the URL stored in another field. In this example, the 2nd field will be displayed as a link to the URL stored in field 20, which might be the employee's Home Page.

    Session("dbViewPage") = Request.ServerVariables("PATH_INFO")
    You won't normally need to change this one, it gets the name and location of the current file (our tblEmployees.asp) for use in making the links on the pages. If you don't want these settings to be reloaded whenever the user pressed Refresh, you might change this line to:

    Session("dbViewPage") = Session("dbGenericPath") & "GenericList.asp"

    That would cut this tblEmployees.asp file out of the loop once the Generic interface got rolling.

    Session("dbWhere") = ""
    A SQL Where clause. Leave it empty to display all records. If we only wanted to view Employees whose titles begin with "Sales" we would set this as Session("dbWhere") = "Title LIKE 'Sales%'"