Introduction
This example shows how Dynamic-CD scripts and databases can be used to put a product catalog
on a CD. The scripts list all the available parts and show how to build up
an order. The Cart has an option to send the order to a Check Out at the PHD web site;
when there, the CD prices are checked against the current prices and a revised
order displayed; the order is not stored in a database.
The list of available parts is found from a Product database on the CD.
The cart of ordered items is stored in a database that is created in the user's
Windows temporary directory. Unless the user clears this directory, the cart
persists from one session to the next. A modified copy of the Product database is
used online to display the latest prices for ordered items.
Site design
The site uses four scripts on the CD and one script on the PHD server.
A client side image map is used to provide quick links to the
"Order Items" categories and the "Cart".
The CD scripts use three "include" files that define constants and
common functions and subroutines. These are described below.
The "List all available items" script (list.asp) and the
"More information" script (moreinfo.asp) show how to do
simple database accesses.
The guts of the CD site are in the "Order Items" script (pick.asp)
and the "Cart" script (cart.asp).
The "Order Items" script either displays all products, or only products
in the selected category. For each listed product there is a link
to let you add one more of the chosen item to your cart.
The script also lists the total number of items in your cart.
The "Cart" script does several jobs. Its primary purpose is to display
the contents of the current cart. It also accepts order items from the
"Order Items" script, lets you update the order quantities, and clear the cart.
Finally it also provides a means of transferring the order to the online web site.
The "Check Out" script on the PHD web site (CheckOut.asp)
accepts the order from the CD "Cart" script. It lists the order again,
looking up the latest prices in the online product database.
Any price changes are indicated to the user.
Note that the site design suffers from the "Back problem", ie if the user
presses the Back button in their browser then ordered items can seem to
be removed from the order. However most folk understand that systems can
be fooled if you do this.
Parts Database on CD
The figure on the right shows the tables in the Parts database file
ShopCart.mdb. I created the structure in Access 2000 and added the example data by hand.
I then converted the database into Access 97 format just to be on the safe side.
You can open this database to look at the raw data.
I have only provided a Full Description for a few items
in the Parts table.
|
|
The Categories table contains the list of categories,
ie "Books", "CDs" and "Trivia". The Categories table
ID field is a unique "Autonumber" integer.
The Description field is the category name.
The Order field is used to sort the categories into the desired order
when displaying choices to the user.
The Parts table contains the part definitions for all categories.
The Parts table
ID field is a unique "Autonumber" integer.
The Category ID field links the Parts and
Categories tables, so that the scripts can find all the parts that
are of a certain category, and vice versa.
The PartNo field identifies the part, with the
Description field giving a part name for display to the user.
The Full Description field has more information in HTML form.
Finally the Price field gives the part's cost in pounds sterling (GBP, £).
Finally, here's a useful tip for determining the SQL needed to query the database.
In Access, design a Query that suits your need. Then copy the text from the SQL View
to your script.
Parts Database online
The Parts database is also online in file ShopCart2.mdb.
The database structure is exactly the same, as is the data except that
some prices have been changed up or down.
The "Check Out" script does not cope with situations where a part is no longer
available. If this might happen on your site, then make sure your
online check out can cope.
Order Database on CD
The user's order is stored in an Order database called
DynCart.mdb in their Windows temporary directory.
This database is created when it is first needed.
The figure on the right shows the one table called
Order that is created in the database.
The Quantity and PartNo
fields hopefully are obvious, with each record storing one order line.
The "Cart" script maintains the values in this database.
Note that if the Quantity field goes down to zero, then the record
is not deleted from the database. Instead, the order line is simply not
listed on screen.
Note that there is no option to delete this database;
you could use the FileSystemObject
to do this job.
The database will be removed if the user selects the "Disk Cleanup"
option in Windows Explorer.
|
|
Directory structure
This example directories are structured as follows:
ShopCart | --- Source |
| --- Image | --- dyncd |
|
The entire site is developed in ShopCart\Source, ie this directory contains
all the static pages, images, the ASP scripts and the databases. The file
ShopCart\ShopCart.dcd is the Dynamic-CD-Wizard project file. This tells
Dynamic-CD-Wizard to build the CD image in the ShopCart\Image directory;
building the CD image makes a dyncd directory in ShopCart\Image.
Note that port 8003 is specified in the Dynamic-CD-Wizard Advanced options; this lets
this example run while the Dynamic-CD Documentation is being viewed.
The scripts are not encrypted in the CD image.
Try out this example by running ShopCart\Image\dyncd\DynCD.exe.
You can inspect the scripts in Notepad by clicking on the "view source" link
in the Design Notes for each page. Alternatively use your favourite editor
to view or change the scripts in the ShopCart\Source directory.
Use Access 97 or later to view or change the databases. If you make
any changes, use Dynamic-CD-Wizard to rebuild the CD image and try it out.
Script structure
Each script starts off with a Server Side Include (SSI)
of three files. adovbs.inc (from Microsoft) contains
most of the ADO constants that might be wanted. vbsconst.inc contains
additional VB constants. Finally cartutil.asp contains several
subroutines and functions that are used in all scripts - see below.
<!--#INCLUDE FILE="adovbs.inc"-->
<!--#INCLUDE FILE="vbsconst.inc"-->
<!--#INCLUDE FILE="cartutil.asp"-->
|
Make sure that the constants you want to use are actually listed in these headers.
If they are not then VBScript silently assumes that the values
are zero, which is almost certainly not what you want.
Using "Option Explicit" does not seem to work.
Note that VBScript predefines some constants such as VbCrLf.
The scripts then have some static HTML at the top of
the returned page. The bulk of the rest of the page is then returned from the script,
with Design Notes and </BODY></HTML> as static HTML at the end.
The main code in each script is in subroutine RunMyScript.
At the very end, the
script calls RunMyScript to do the work of the script.
As far as possible, the scripts try to avoid presenting the user with obscure
errors. Some such as "Could not open database" are pretty unavoidable.
Finally note that the code sets all object variables to Nothing once their
use has been finished.
Cart Utilities
The SSI include file cartutil.asp defines variables, constants,
subroutines or functions
that are used by most scripts.
Variable PartsDbCon is used for the connection to the Parts database.
OrderDbCon is used for the connection to the Order database.
Constant ExchangeRate is set to one; this could be modified in future
to help cope with other currencies.
Subroutine ReportError is used to write out an error message if a script runs
into serious problems.
Function FormatCurrencyUK converts a Dbl value into a string with the value
formatted as a UK currency.
Function Connect2Parts opens the Parts database on the CD.
It obtains the CD directory path from the "ROOT_DIR" Dynamic-CD
variable using Request.ServerVariables
DIM rootPath, DbPath
rootPath = Request.ServerVariables( "ROOT_DIR")
DbPath = rootPath & "\ShopCart.mdb"
|
Subroutine CloseParts closes the connection to the Parts database.
Function OpenOrder creates or opens the Order database in the
current user's Windows temporary directory
(obtained from the "WINDOWS_TEMP_DIR"
Dynamic-CD variable). OpenOrder first tries to open the database.
If this fails, it tries to create it using the ADOX object. It creates an
empty database then opens it as normal using ADO. (If the open fails, delay
and try again - see below.) OpenOrder finally
adds the required Order table - see
below for more information.
Subroutine CloseOrder closes the connection to the Order database.
Localisation
This example is hard-wired to use United Kingdom currency, pounds sterling,
even if the CD is run elsewhere. Rather than use the standard
FormatCurrency routine,
our function FormatCurrencyUK is used.
Script tips
Don't use FileSystemObject
A script could use the Microsoft standard
FileSystemObject
object to do file system work, eg see if a file exists, delete a file,
or create a text file.
However, Windows 95 and Windows NT 4 and earlier do not
support FileSystemObject .
If you want to see if a file exists, then use the Dynamic-CD specific function
Server.FileExists ;
this returns Boolean True if a file exists.
The first cut of these scripts used the FileSystemObject
FileExists method to see if the
database files were present before trying to open them. However it was decided
to remove this test because the open would fail anyway if the database file
were not present.
Open/Close in each script
The Shopping Cart scripts open and close connections to the database files in
each script. An alternative approach would be to open the databases at the beginning of
the session (using a session variable or code in a global.asa file).
When is the best time to close the database connections - we don't know.
The connections will be cleared when Dynamic-CD stops. However it seems neatest
to open and close the connections in each script. The time delay will be negligible.
Creating tables in NT4
The OpenOrder function creates the Order database if need be.
It does this using the ADOX object to create an empty database.
ADOX can be used to add a new table to a database (using the ADOX.Table object).
However adding a table in ADOX does not work in Windows NT 4.
Therefore OpenOrder adds the table to the empty database in a different way.
It closes ADOX, opens the database in ADO and adds the table by executing a "CREATE TABLE" SQL command.
Creating tables in fast computers
As described earlier, the OpenOrder function creates the Order database if need be.
This is done by creating an empty database using the ADOX object and then adding the
Order table.
This process runs into problems on some computers. We think the problem
occurs on fast computers.
After OpenOrder creates an empty database using ADOX, it closes the ADOX
object. The script immediately then tries to open the new database using ADO.
This sometimes fails on fast computers. We think that ADOX has not properly closed the
new database file. If a small delay is put in, the retried open will now
succeed.
Field Access
Note that there seems to be a bug in the ADO recordset object.
If you refer to a recordset field explicitly more than once, then the
second and subsequent accesses return an empty value.
For example, the second line here will produce no output:
Response.Write CategoryRS("Description")
Response.Write CategoryRS("Description")
|
Rewrite the code like this:
descr = CategoryRS("Description")
Response.Write descr
Response.Write descr
|
Forming SQL queries
SELECT queries must use square brackets around the table name, eg:
OrderSQL = "SELECT * FROM Order" ' Will not work
OrderSQL = "SELECT * FROM [Order]" ' Will work
Set OrderRS = OrderDbCon.Execute( OrderSQL)
|
| 