dbDynalist - A very powerful implementation of dynamic drill down list  Pro | Enterprise | Gold

Dynamic list (Dynalist) is a very exciting new feature of "ASP-db Gold". It employs the Remote Scripting technology to create a linked lists is a very easy and powerful manner. This feature will enable user to construct drill down queries within the same of from different data sources. A classic purpose of using dynamic list is to guide the user to reach their destination record "in a straight line" manner. Unlike the regular RS constructs where ASP and Javascript are used to providing the coding, ASP-db uses the same ASP-db DLL for the coding hence speed is optimized plus the list can inherit the wonderful features of ASP-db. For instance, the Lookup feature is inherited and each list layer can perform a lookup into different DB and user sources. Dynalist support is tightly integrated into the Designer. An example in the following tutorial will show you how to construct a 3 layers multiple source dynamic list in about 20 clicks with NO keyboard interaction. On top of that, all the skeleton support files are being generated automatically. You can modify these files for advance implementation involving complex queries and HTML dress-up. Both IE 4.x and Netscape 4.08+ browsers are supported.

Dynalist Installation

Installation of Dynalist involves two parts -

1. Standard Remote Scripting installation

2. ASP-db Dynamic List Installation

Methods and Properties Syntax

Methods: ASPdbDListInit
Description: Creates a header describing objects necessary for Remote Scripting. This method must be the first call at the beginning of the DList setup. User also has to place a [[DLIST_INIT:S]] tag in the template file before the <form name="myaspdbform" id="myaspdbform"> tag.

Methods: ASPdbDListCreate
Syntax: Object.ASPdbDListCreate(ListName)
Description: Creates the list. A "list" is a select box containing the fields.
Example: DBList.ASPdbDListCreate("List1")

Methods: ASPdbDLHREF
Syntax: Object.ASPdbDLHREF ListName,URL
Description: Links the list with the URL object. At this time, there cannot be any "unsafe" characters in the List value therefore usage of this method id not recommended. Submit and Link cannot co-exist.
Example: DBList.ASPdbDLHREF "List1","Sample.asp?par1=" & List("List1")

Methods: ASPdbDListSubmit
Syntax: Object.ASPdbDlistSubmit("ListName")
Description: Submit the form in which the selected lists values can be retrieved via request.querystring("Listx"). Submit and Link cannot co-exist.
Example: DBList.DListSubmit("List1")

Methods: ASPdbDListComplete
Syntax: Object.ASPdbDListComplete
Description: Returns the object into the pool. This method is setup be the ASP-db-Designer automatically.
Example: DBList.ASPdbDListComplete


Properties: dbDynaList
Syntax:
Object.dbDynaList=True
Description: Indicate Dynamic List is in action. Session variables will be stuff with the Lists data accordingly for the template. If  "Designer" is used then dbExpress will be set to true.
Example: dbDynaDList = True

Properties: Add
Syntax:
Object.Add = Array
Description: Adds an "OPTION" element or element array to a list. Pre-build arrays like States, Country etc. are available in ASP-db-Designer. Note that the Designer has added features to allow either single or paired (Value|Text...) entries as well as many preprogrammed lists including the ones defined by user in a file.
Example: 
DBList("List1").Add = "0|zero"
Dim a(3)
a(0)="0|zero"
a(1)="1|one"
a(2)="2|two"
DBList("List1").Add = a

Properties: AllowBlank
Syntax:
Object.AllowBlank=True
Description:  Allow a blank in the list. This is a very unusual case and is not expected to happen in dynamic list.
Example: DBList("List1").AllowBlank = True

Properties: UserORS
Syntax:
Set ObjectUserORS = "ADO recordset"
Description: Sets an ADO Recordset object as a data source for the first object. Use this property only under special situation which the  source of RS is out of user's control.
Example: 
Set con = Server.CreateObject("adodb.connection")
con.open "DSN=ORA;UID=ops;PWD=kan;SERVER=chuck;ConnectString=DSN=MyDSN;"
Set DBList("User1").UserORS = con.Execute("Select bank from tbls where bank="Chemical" order by bank")

Properties: Action (default=True)
Syntax:
Object("List").Action = boolean
Description: Indicates whether the current List will be used as a navigation key to subsequent Lists. Note that an ASPdbCreateLink is considered as True. Normally the last List is false unless it is a Link. 
Example:
dbl("List2").Action = false

Properties:
ConnectString
Syntax:
Object.ConnectString = "ADO connect string"
Description: Sets up a connection with the DB to obtain the List.
Example: 
DBList("List1").ConnectString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=D:\Data\Shop.mdb;"

Properties: Parent
Syntax: Object.Parent = "List Name"
Description: Defines a connecting list (i.e. the list on which the current list depends). This is usually the previous List.
Example:
DBList("List2").Parent = "List1"

Properties: FormName
Syntax:
Object.FormName = "Form Name"
Description: Defines the form name in which the list in used. Each List can have it's own form name. This is very flexible when comes to layout the Lists in a template. A default name of "myaspdblistform" is given if there is no input. Note that these form name(s) must be present in the template.
Example: DBList("List1").FormName = "F1"

Properties: Size
Syntax:
Object.Size = SelectSize
Description: Defines the box size of the list select box. In Netscape 4.08+, you'll need to set this to >1 in order to see the selection in the list.
Example:
DBList("List1").Size = 10

Properties: SQL
Syntax:
DBListObject.SQL = "SQL Query"
Description: Defines the SQL query to fill the list. If more than one expression is mentioned in the query, the first one is used as "OPTION" value and the second - as the text.
Example: DBList("List1").SQL = "select employeeid from employees"

Properties: Text
Syntax:
DBListObject.TExt = "Text to display in the List Box"
Description: This is the text displayed in the select box during the initial display. That normally it should be blank. Note that in Netscape, you need this to dress up the select box appearance due to nasty bugs in it.
Example:
DBList("List1").Text = "           "

Properties: Value
Syntax:
DBListObject.Value
Description: Used for substituting the selected value.
Example:
"select Name from Table where ID = '" & DBList("List1").Value & "'"

Tutorial

The easiest way to get going in using the Dynalist feature is to use the Designer2001 which is included in your Gold package. The following are the step-by-step instructions -

  1. Fire up Designer2001 and connect to Northwind. You can stop after you see the table names show up. These table names will be automatically transferred to the DynaList form.
  2. Go to the "Path" form and make sure all the paths especially the template path contains a valid virtual path. The DynaList template will create sample templates and deposit into this directory. The example detail file will be deposited into the preview virtual directory defined by the pair of virtual/physical directories in the path form. A detail file is a file generated by the Designer to display the details of the dynamic list selections. The rules are - if the same table is used throughout (Shopping database) then the detail SQL will include all the selected fields. If more than one table is used for the lists then only the last criteria will be used to construct the detail display SQL. Aside from the Designer, any SQL statement can be used including (JOIN/UNION) as long as they are valid. Study the three files generated by the Designer in the Express mode (more details).
  3. Fire up the DynaList Form. You'll see the DListDSN is filled in with the values in the DSN box of the Connection form. If it is not there, hit the copy button to accomplish that.
  4. Click the "Gen Temp" checkbox and the template filenames will be constructed for you.
  5. Check the Express checkbox and excitement will begin !!
  6. Pick a table from the Table drop box - Employees.
  7. Pick a field from the Field drop box - EmployeeID.
  8. Hit the transfer arrow button and record the first layer. In real practice, you can type in the same values in the SQL box.
  9. Pick a second table from the Table drop box - Orders.
  10. Pick a field from the Field drop box - CustomerID. At this point pay attention to the data type of the selected fields. Designer will determine the correct type to construct the SQL statement. If you are manually building the SQL, make sure you use the correct data wrapper. The field you pick from this second layer even though not mandatory but should be linked to the first layer. Transfer to the SQL box.
  11. Pick a third table from the Table drop box - Customers.
  12. Pick a field from the Field drop box - CompanyName and transfer.
  13. Make sure the Submit and Sample Code boxes are checked. Then hit the "Build DynaList" button. The Build box will be stuffed with the pseudo text.
  14. Now, hit the "Big E" and execute the code. So far you have used a total of 20 clicks in constructing a 3- layers dynamic lists. Make your selections and when you hit the last box you'll be redirected to the same file but with the selected values retrieved and displayed.
  15. Go through a few rounds of clicking and get a feel of the "dynamic" aspect. Go and find a bigger database or download a good size shopping database from the web site. 
  16. Now, you might noticed that the Employeeid is a number and can use some lookup. Go back to the connection form. Pick the employee table and select the employeeid field such that the dbSQL is constructed. Go to the Lookup form. In the LookUP Source Table drop box, the Employees table is already ready and no selection is needed. In the LookUp Source Fields listbox, Choose EmployeeID, FirstName and LastName in that exact sequence. The LookUpSource SQL box then would have -> 
    SELECT DISTINCT EmployeeID,FirstName,LastName FROM [Employees]
    Modify it to look like 
    SELECT DISTINCT EmployeeID,FirstName + ' ' + LastName FROM [Employees]
    Hit the "Add DB dbDisplayLookUPList" button and you have defined a lookup field for the employeeID. Click the "Big E" again. Now you have the first layer displaying the First and Last name of the employee instead of the ID. When you get to the last layer, the value of the selection will be displayed and you'll find that the first layer selection correctly remains as employeeID and not the lookup mask. "Magic effects" can be applied to the all layers. 

 

Express Dynamic List - Designer example

The Designer will generate 3 files after the user made their selections of the lists -

  1. Template file for input (DListTemplate.htm)
    Modify this file to customize the look of the input. The location of this file will be displayed before the List boxes.
  2. ASP file for output (DListDetails.asp)
    This template asp file will construct the SQL based on the criteria of the listboxes. The first record of the query will then be displayed.  Modify this file to customize the look of the output.
  3. Regular core ASP-db code (Temp.asp)
    Modify this file to customize the SQL query. Make it as complicated as needed.
Source code with explaination

File = Temp.asp (Regular)

  1. <% Set X= Server.CreateObject("ASP.DB")
  2. X.dbUnit = "999"
  3. X.dbMode = "Grid"
  4. X.dbDSN = "DSN=NWIND"
  5. X.dbDisplayLookUpList = "(~|+)EmployeeID||SELECT DISTINCT EmployeeID,FirstName + ' ' + LastName FROM [Employees]"
  6. X.dbDynaList = True
  7. X.ASPdbDLISTInit
  8. X("employeeid").FormName = "mydlistform"
  9. X("employeeid").DListDSN = "DSN=NWIND"
  10. X("employeeid").SQL = "select distinct employeeid from Employees"
  11. X.ASPdbDListCreate("employeeid")
  12. X("customerid").SQL = "select distinct customerid from Orders WHERE employeeid= " & X("employeeid")
  13. X("customerid").Parent = "employeeid"
  14. X.ASPdbDListCreate("customerid")
    X("companyname").SQL = "select distinct companyname from Customers WHERE customerid= " & "'" & X("customerid") & "'"
  15. X("companyname").Parent = "customerid"
  16. X.ASPdbDListCreate("companyname")
  17. X.ASPdbDListSubmit("companyname")
  18. X.ASPdbDListComplete
  19. X.ASPdbSendTemplate("d:\aspdb\data\DListTemplate.htm") %>

Line 1-5
Regular ASP-db stuff. Remember that #5 is only good for layer 1.

Line 6
If dbDynaList=True then the regular ASP-db display will be ignored instead ASPdbSendTemplate is expected.

Line 7
DynaList Initialization. This method must be the first called.

Line 8
Define the form name for the first list. The list name is "employeeid" assigned by the user. This listname is case sensitive and used throughout the entire setup. I suggest you use lower case all the way. If should you encounter javascript error. This is the first thing you check. If all the list items resides in the same form. It is not necessary to re-define this name again.

Line 9
This DListDSN property is the full connection string which is defined as one that can be used by a pure asp file and not one defined by ASP-db. For example, dbDAT, dbMDB cannot be used. However, if you use the Designer to define dbDAT and dbMDB, the full string will be deposited in this property box.

Line 10
This SQL property is used retrieves the column data for the first layer ("employeeid"). If more than one column are present, only the first column will be used.

Line 11
All is done for the Listname (Layer). It's time to generate the data for that particular layer. Note that there are two properties that needs attention - Parent and Action. Since the first layer is not depending on any previous layer, this should be false which is the default. This layer will cause the next one to have action (Action or submit). So the Action property is true which is the default.

Line 12
This SQL property is build using the selected value of the previous layer. The variable is drawn from the value property of the previous list object. Do not try to use these list values outside the DynalIst confine as you'll not have much luck. These values are generated in real time and is not available in static mode. Something like Var1=X("employeeid") will not make it. The only way to retrieve the selected variables is to use the ASPdbDListSubmit and request.querystring.

Line 13
Specify this list is depending on the previous select value.

Line 14
Create the customerid list box.

Line 15-16
Repeat the operation to generate the companyname list box.

Line 17
Submit the selected values

Line 18
Wrap up and release objects

Line 19
Dynalist must use a template and not the ASPDB.

Input HTML template code with explanation

The entire Dynamic List implementation is presented through a template file via the X.ASPdbSendTemplate method. The following is a sample skeleton template created  by the Designer-

File=DListTemplate.htm (Input)

Sample DynaList template in -> g:\aspdb2000\data\DListTemplate.htm
[[DLIST_INIT:S]]
<form name="mydlistform" id="mydlistform" ACTION="DListDetails.asp" TARGET="XXX">
<table cellspacing='0' cellpadding='2' rules='all' bordercolor='#000000' border='1' style='background-color: #FFFFFF; border-collapse: collapse; border-color: Black'>
<tr><td>state</td>
<td>[[DLIST_state:S]]</td></tr>
<tr><td>city</td>
<td>[[DLIST_city:S]]</td></tr>
</table></form>

The only significant part of this template are the [[...]] tags where these tags will be replaced by session variables generated by ASP-db. The [[DLIST_INIT:S]] is mandatory and should be placed before the form tag. The [[DLIST_ListName:S]] tag will place the Dynamic listbox of the corresponding listname. The rest are cosmetics HTML code. Pay attention to the line -> 

<form name="myaspdbform" id="myaspdbform" ..... > 

It just happens in this example, all the list boxes are placed in the same form. Actually, each Listbox can be placed in a different form (different form name). This offers extreme flexibility in the web page layout.

Output ASP template code with explaination

 

File=DListDetails.asp (Output)

Sample Detail template in -> D:\Inetpub\scripts\DListDetails.asp

<%
reportsto = Replace(Request.querystring("reportsto"),"'","''")
employeeid = Replace(Request.querystring("employeeid"),"'","''")

Set Detail = Server.CreateObject("ASP.DB")
Detail.dbQuickProps = "88;;;Form;;;;;;;1;;False"
Detail.dbDSN = "DSN=NWIND"
Detail.dbNavigation = "NONE"
Detail.dbSQL = "SELECT * FROM Employees WHERE employeeid= " & employeeid
Detail.ASPdb
session.abandon
%>

This is a sample output file using the values from the selected list to perform some information retrival and presentation. The selected values submitted by ASPdbDListSubmit can be retrieved by the Request.querystring(Listname). 

Designer - Dynalist form

The DynaList form is the most condensed one among all other forms. Besides generating the regular ASP-db code, it generates additional Input and Output template files required to create the DynaList. An input template (html) is for user input. It consists of dropdowns boxes representing the layers. An output template (asp) is one that when user arrives a target and firs the trigger, the record representing that target will be displayed according to this template. These files can then be modified and be put intro production mode. You also have the option to use different template files established ahead of time. The above two illustrations below shows the normal and express mode of the DynaList. In the normal mode, you have to type in all the parameters in the List Parameters manually. The most cumbersome one is the SQL where all the single and double quotes have to be balanced.

Template File, Gen Input -
This is the filename of the input HTML template which dictates how the listboxes will be configured. If the "Gen Input" box is checked, a default name (DListTemplate.htm)  will be given. See more details

Detail File, Gen Output
This is the filename of the output ASP template file which presents a standard way to display some results from the selected listbox values. If the "Gen Output" box is checked then a default name (DListDetails.asp) will be given. The physical location of this file is in the "Preview directory" of the Path form. See more details

Form Name
This is the form name for "List Name" in action. All list boxes can reside in either the same or different forms. Remember that if different forms are used for the list, the one submitted will be the one defined in the Input template. Express mode only supports a single form when generating the detail file. 

List Name
This is the Listname of the layer. This name is case sensitive. That is why the designer forced lower cases to be safe.

Xlast
This button deletes the last layer. Since most of the time the lists are linked, it is only meaningful to delete the last one.

DlistDSN, Copy dbDSN
You can either manually input a full asp connection string or you can use the Connection form to make one up. A full asp connection string means one that is used in a pure asp file and not short cuts in ASP-db like dbmdb and dbdat etc. If you use the connection form to construct this connection string, the value will be expanded and copied to this box automatically. If for some reason it is not, then use the "Copy dbDSN" button to perform copy.

SQL
This is the full query statement to construct the lists in the normal mode. In the express mode, this has the syntax of -
Field,Table | Field2, Table2 |.... if table parameter is not present then it is assumed that all lists query the same table. Note that the "field" element is wrapped by the correct type wrapper. If you input this manually make sure you so that. In express mode the automatically generated SQL only performs a single field list. Actually you can select two fields (manually) in which the first field is the Value and the second field is the Text.

Express
When this checkbox is checked the express mode is activated. Use the connection form to identify the data source when you use the express mode. All the table names will be loaded into the table combobox.

Table
This box contains all the tables from the data source. Select one for the list.

Field
This box contains the fields belonging to the selected table.


This is the transfer button to construct the Field | Table statement in the SQL box.

Build Link
This is the HREF linked to the list item. At this time it doesn't support any unsafe characters and is not recommended.

Add Array, Special Add items
User can specify hard coded values for the list. The format is "Value1 | Text1, Value2 | Text2 ..." or "ValueText1, ValueText2...". Note that in the actual ASP code, you can use Dim and Array to construct this list. There are many preprogrammed items for the array including a "File" option in which the array can be fed from an ascii file. The format of the ascii file is the same.

Parent List Name
This list is the same as the ListName list. This is the name of the list (normally) the previous list that the present list is depended upon. In other words, the depended listname is used to construct the SQL to obtain the present list.

List Size
Size of the List box. Normally, this value is 1, but in case of Netscape, you might need to use a bigger box. See More details

Action
While Parent List Name indicated what this list is depending on, the Action property indicates whether this list is going to cause some subsequent actions like changing the subsequent list, Link or submit. This property defaults to true and is almost always true.

Build Dynalist
This button builds the Dynalist box from the parameters. When a parameter is changed just refresh the box by clicking this button.

Reset
Reset the values of the Dynalist box or parameters.

Disable Dynalist
When the Dynalist box is not blank, dbDynalist will override the normal ASPDB. Sometimes, it is desirable to test the regular functions, check this checkbox to disable the Dynalist operation.

Submit
This checkbox indicates whether the selected values will be submitted upon the last list being clicked. Usually the value is true otherwise, there will be no action after clicking the last list.

Example Code
This checkbox indicates whether example codes (input/Output) should be generated.

Snake
This the form Column number of the example code.

Notes - Netscape

ASP-db DynaList works on Netscape and IE. However, the list box display in Netscape is erratic as it behaves differently upon the layers that has a parent. Upon resizing the browser, the display will sometimes return to normal and sometimes loose the contents of a layer. The one way to make Netscape works consistently is to set the size of the list box to > 1. Another way to make it looks better is to use the Text property to set the initial display with some blanks.