A VBScript Application

Gathering Requirements for Your Application

• Can You Do It?

• Use Word to Create Rough Drafts of the Three Main Pages
• Design the Database in Access
  • Add a Driver for the Bike Shop databas Manager
• Using the Internet Information Server Add-In
• What Do You Have So Far?
  • The Catalog Page
  • The Sales Page
  • The Order-Tracking Page
  • Odds and Ends
• Summary
• Q&A
• Workshop
• Quiz

Chapter Eight

A VBScript Application

Today you will use knowledge gained in Chapter 6, "Writing VBScripts," to construct an application using VBScript and ActiveX controls. You will start with the requirements for the application, do some quick design work, then implement the design.

Gathering Requirements for Your Application

You've got a friend, let's call him JP, who wants to open a bicycle store on the Internet. Ten years and forty pounds ago, he raced bicycles and has always wanted to open his own shop (or it could be that his wife wants to get rid of all the bicycles he has accumulated over the years). He has conducted some research and figures the best, least expensive way to start selling bikes is over the Internet. He wants to show an online catalog. He wants to allow customers to order the catalog. He also wants to allow the customers to order bikes and accessories online, and to track the status of their orders. He asks you, "can you do it?"

Can You Do It?

Tell your friend to hold on for a couple of minutes. Lets take a look at what he wants and how you might do it.

Luckily, the customer knows what he wants. The worst times I have spent programming revolved around customers who knew what they didn't want (normally after it was built), but couldn't quite articulate what they did want. Lets look at the basic requirements of the program you will write:

• a Web site
  
  
• a display catalog
  
  
• an order catalog
  
  
• the capability to handle customers' orders for bikes and accessories
  
  
• the capability to track orders
  
  

Write these down and show them to your friend. Tell him you will have something by the end of the day. First, draw a rough layout of the Web site. Sketch something that looks like Figure 8.1.

Figure 8.1. Layout of the Web site.

This shows a Welcome page linked to Catalog, Sales, and Order-Tracking pages. Next, follow these steps:

1. Use Word to create rough drafts of the three main pages.
   
   
2. Design the database in Access.
   
   
3. Add a driver for the bike shop database to your system's ODBC manager.
   
   
4. Use the Internet Information Server Add-In for Access to create two insert pages: one for catalog orders, and another for sales.
   
   
5. Use the Internet Information Server Add-In for Access to create a search page for order tracking.
   
   

After you finish these steps, complete the project by:

1. Customizing the pages with products from the ActiveX Control Pad.
   
   
2. Hacking your way into the scripts that Internet Information Server Add-In for Access creates, and customizing them using VBScript.
   
   

Use Word to Create Rough Drafts of the Three Main Pages

Open Word and make sure you have Internet Assistant installed (see Chapter 4, "The Tools of the Trade," for details). Go to File | New and select HTML.dot as your template. Add the logo (logo.bmp on the CD-ROM that came with this guide), some general welcome remarks, and links to the catalog (catalog.asp), sales (sales.asp), and order-tracking (track.asp) screens. Your screen should look like Figure 8.2.

Figure 8.2. Welcome page for JP Bikes!

Save your work as welcome.asp. Your HTML code should look like Listing 8.1.

Listing 8.1. welcome.asp .

Next, open a new HTML page in Word. This will be the Catalog page. You will be putting ActiveX controls here later. For now, you need a logo, a return link to welcome.asp, and a link to catalog order page (catord.asp, which you will generate with the Internet Information Server Add-In a bit later). Your page should look like Figure 8.3.

Figure 8.3. Catalog page.

Save your work as catalog.asp. Your HTML code should look like Listing 8.2.

Listing 8.2. catalog.asp .

Open a new HTML page in Word. This will be the Sales page. You will be putting ActiveX controls here to calculate the cost of the order before the user sends it. You will also add some of the code generated by Internet Information Server Add-In, and generate orders from this page. Place a logo and a return link to the Welcome page (welcome.asp) on your Sales page. Your page should look like Figure 8.4.

Figure 8.4. Sales page.

Save your work as sales.asp. Your HTML code should look like Listing 8.3.

Listing 8.3. sales.asp .

Open a new HTML page in Word. This will be the Order-Tracking page. You will modify this page with frames and code generated by the Internet Information Server Add-In. Again, all you need now is a logo and a return link to the Welcome page (welcome.asp). Your page should look like Figure 8.5.

Figure 8.5. Order-Tracking page.

Save your work as track.asp. Your HTML code should look like Listing 8.4.

Listing 8.4. track.asp .

This is a good place to stop and check your work. Look at these pages and test the links in Word or in the browser. I recommend using the browser because few of your friend's customers will be using Word to look at this site. Go ahead and check, I will wait...

Now, lets design a database.

Design the Database in Access

I won't go into database theory or design philosophy here. It would be fun, but it is beyond the scope of this guide. You will simply create the objects you need.

---

Note
You don't need to use Access to build the database. You can use any database you have an ODBC driver for. To use another database, create the tables as described in this section. Add an ODBC entry to the system DSN as described in the next section. Finally, type the idc and htx files generated in the upcoming section, "Using the Internet Information Server Add-In," by hand (since this tool only works in Access). The important things are the column names in the database tables and the name you give the connection in the ODBC system DSN.

---

Open Access and create an new database called bikeshop.mdb. Add two new tables, Customers and Orders, as shown in Figure 8.6.

Figure 8.6. Bike shop database.

Switch to the Customers table and create the fields shown in Figure 8.7.

Figure 8.7. Customers table.

Switch to the Orders table and create the fields shown in Figure 8.8.

Figure 8.8. Orders table.

Add a Driver for the Bike Shop database to Your System's ODBC Manager

At this point, add a reference to your database to the ODBC driver manager . Open up your ODBC administrator, click the System DSN button, and add a new entry based on an Access driver. Fill in the entry, as shown in Figure 8.9.

---

Note
Those of you who have worked with ODBC before have probably never put an entry into the system DSN. The system DSN makes the data sources available to the system, or any other user that can log into the system.

---

Figure 8.9. ODBC administrator.

Now you are ready to use the Internet Information Server Add-In.

Using the Internet Information Server Add-In

Use the Internet Information Server Add-In to create a page that will insert the customer's catalog-order information into your database. Call it catord.asp. You must close and re-open Access for the Internet Information Server Add-In to see the new entry you made in the ODBC administrator. Open the bike shop database and launch the Internet Information Server Add-In by choosing it from Tools | Add-Ins. On the first page of the Internet Information Server Add-In Wizard, choose Insert Page Wizard. On the next screen, choose the Customers table (after all, asking customers to order catalogs is just a sneaky way of getting their names and addresses). Proceed to the field screen and add all the fields. Move to the final input page. You are going to tinker with this screen, so there is no need to add anything. Click Finish; you have created three files that resemble Listings 8.5, 8.6, and 8.7.

---

Note
The Access database you just set up has not been optimized to account for multiple users. See your database documentation to determine how to tune you database to handle multiple users.

---

Listing 8.5. catord.asp .

The htx file is the template for the page sent back by the Internet Information Server.

Listing 8.6. catord.htx .

The idc file contains the query that will run against the database.

Listing 8.7. catord.idc .
Datasource: BikeShop
Template: catord.htx
DefaultParameters:
SQLStatement:
+INSERT INTO "CUSTOMER" ("LastName", "First Name", "Addr1",
"Addr2", "City", "State", "Zip", "Country", "Phone", "FAX")
+VALUES ('%LastName%', '%First Name%', '%Addr1%', '%Addr2%',
'%City%', '%State%', '%Zip%', '%Country%', '%Phone%', '%FAX%');
#IDC-Insert FrontHTM-catord.asp ReportHTX-catord.htx

Do the same for the Orders table and save the result as salord.asp (which will create the files files salord.htx and salord.idc). Generate salord.asp, salord.htx, and salord.idc as shown in Listings 8.8, 8.9, 8.10.

Listing 8.8. salord.asp .

Listing 8.9. salord.htx .

Listing 8.10. salord.idc .
Datasource: BikeShop
Template: salord.htx
DefaultParameters: OrderNumber=NULL,

SaleDate=NULL, SaleTime=NULL
SQLStatement:
+INSERT INTO "ORDERS" ("OrderNumber", "LastName", "FirstName",
"SaleDate", "SaleTime", "ShipToAddr1", "OrderInfo",
"ShipToCity", "ShipToState", "ShipToZip", "CardNumber",
"ShipToPhone", "PaymentMethod", "Status")
+VALUES (%OrderNumber%, '%LastName%', '%FirstName%',
%SaleDate%, %SaleTime%, '%ShipToAddr1%', '%OrderInfo%',
'%ShipToCity%', '%ShipToState%', '%ShipToZip%', '%CardNumber%',
'%ShipToPhone%', '%PaymentMethod%', '%Status%');
#IDC-Insert FrontHTM-salord.asp ReportHTX-salord.htx

Use the Internet Information Server Add-In Wizard to generate one more group of forms: the query for the Track Order page. Call up the Wizard and choose the Query and Display option on the Wizard's first page. Then pick the Orders table. Choose the OrderNumber field as the search field. Pick the LastName, FirstName, SaleData, and Status fields for display on the Display Web Page. All this is shown in Figure 8.10.

Figure 8.10. Setting up the Search and Return pages .

Save the file as tracord.asp, which will also save tracord.asp and tracord.idc. Your files should look like Listings 8.11, 8.12, and 8.13

Listing 8.11. tracord.asp .

Listing 8.12. tracord.htx .

Listing 8.13. tracord.idc .
Datasource: BikeShop
Template: tracord.htx
DefaultParameters: OrderNumber="OrderNumber"
SQLStatement:
+Select "LastName", "FirstName", "SaleDate", "Status"
+From "ORDERS"
+Where "OrderNumber" = %OrderNumber%
#IDC-Search FrontHTM-tracord.asp ReportHTX-tracord.htx

Now you have what you need from the Internet Information Server Add-In Wizard, so lets move on.

What Do You Have So Far?

All the files you have generated are in the rough subdirectory of this chapter's entry on the CD-ROM.

The Catalog Page

The purpose of the Catalog page is two-fold. First, it displays your catalog online. Second, the Catalog page allows the user to order a catalog, which allows your friend to collect the user's address. Let's build an online catalog.

Open your ActiveX Control Pad and add an image control, seven labels, and a spin button (as shown in Figure 8.11).

Figure 8.11. Screen setup for catalog.alx.

Name your variables and add the subroutines, as shown in Listing 8.14.

Listing 8.14. catalog.alx .

Note the font and alignment settings of the controls inside the <OBJECT> tags. The <PARAM> tags show the differences between the default properties and the ones you put in using the property box in the ActiveX Control Pad. Use whatever fonts and alignment you want; they only change the appearance of the page. catalog.alx doesn't have anything critical hidden in this section, but some of the examples coming up take some interesting turns inside the <OBJECT> tags.

First, declare some global variables. Use catalogindex to keep track of where you are in the catalog, and declare four arrays to hold the names, descriptions, prices, and pictures from the catalog. Then create a procedure called LoadInfo (to be loaded from the OnLoad event of the Layout control)to load the arrays with the items from the catalog. The OnLoad event of the Layout control is a good place to put things you want done at the beginning of the program.

Inside the spin_up and spin_down events of the spin button, put code to cycle through the catalog. Notice how the numbers loop; when the user goes below zero on the index, it is put back to 5; when the value spun is above five, it is set back to 0. Also notice how the controls are given initial values in the OnLoad event of the Layout control.

After you save your work as catalog.alx, insert it into catalog.asp (as shown in Listing 8.15).

Listing 8.15. catalog.asp .

This is not a major change from the rough draft you made at the beginning of the day (see Listing 8.3). You will not change the catord files you made earlier. The catord files demonstrate the raw material generated by the Internet Information Server Add-In, and provide a good contrast to what you will do on the next page. Launch the Welcome page(welcome.asp) and pick the catalog. Your screen should look like Figure 8.12.

Figure 8.12. Catalog page .

Selecting the order catalog link gives you the stock screen generated by the Internet Information Server Add-In, as shown in Figure 8.13. This is okay for a program you can download free, but you can and will improve it on the Sales and Order-Tracking pages it generates.

Figure 8.13. Order Catalog page .

That will do it for the Catalog page. Let's move on to the Sales page.

The Sales Page

The sales page has to do three things: it must allow the user to pick items to buy, it must allow the user to fill out an order form, and finally, it must transmit the order form back to the bike store computer. Create a dual-list-box dialog using the ActiveX Control Pad. Use two list boxes, two buttons, and six labels as shown in Figure 8.14.

Figure 8.14. Layout for sales.alx.

Name and configure the controls as shown in Listing 8.16.

Listing 8.16. sales.alx .

Notice that the ColumnCount property on both list boxes is set to 2. This is to provide automatic alignment in the list box for the name and price fields. It also facilitates using the list property of the list box to add and obtain information. The very first subroutine, the onload event of the layout control, uses the list to load up the Lbinventory list box with the names and prices of your inventory. The next two functions use the click events of the buttons to add to or subtract from the lbOrder list box. The subroutine CalcTotals, called inside each button's click event, calculates the subtotal, shipping, and grand total of the items in the lbOrder list box. The function MakeDollar changes this number to formatted currency strings. The most interesting function is SetOrderInfo, which writes information about the order to a text string, then uses the line:

window.document.ORDERFORM.OrderInfo.Value = OrderInfo

to place the Orderinfo string into the variable OrderInfo in the form named ORDERFORM. ORDERFORM exists on the page where sales.alx is placed, as shown in Listing 8.17

Listing 8.17. sales.asp .

This new sales.asp evolves from the sales.asp you did earlier (see Listing 8.3) combined with salord.asp, which you generated with the Internet Information Service Add-In (see Listing 8.8). Access the <FORM> section from the second alx file on this page, order.alx. Use eight text boxes, ten labels, two buttons, and three radio buttons (as shown in Figure 8.15).

Figure 8.15. Layout for order.alx.

Write the code and configure the controls as a shown in Listing 8.18.

Listing 8.18. order.alx .

This code starts with a load form function that transfers the data in the controls contained in order.alx to the hidden controls in sales.asp (Listing 8.16). Notice the use of the select case structure and how the variables in the <FORM> section are accessed using the elements property. The rest of the functions are straightforward. They include a function for clearing all the text, btnCancel_Click(), and another for ensuring that all the fields have been filled in, CheckFields(). The next notable code is the click event of the btnSend button. The click event for the btnSend button submits the data in the <FORM> tags to salord.idc using window.document.Forms(0).submit. This allows you to have the Submit button inside the alx object rather than on the page with the <FORM> section. salord.idc is shown in Listing 8.19.

Listing 8.19. salord.idc .
Datasource: BikeShop
Template: salord.htx
DefaultParameters: Status = "Ordered"
SQLStatement:
+INSERT INTO "ORDERS" ("LastName",
"FirstName","ShipToAddr1",
"ShipToCity", "ShipToState", "ShipToZip", "ShipToPhone",
"PaymentMethod", "SaleDate", "SaleTime",
"OrderInfo", "Status", "CardNumber")
+VALUES ('%LastName%','%FirstName%',
'%ShipToAddr1%','%ShipToCity%',
'%ShipToState%', '%ShipToZip%', '%ShipToPhone%',
'%PaymentMethod%',#%SaleDate%#,
#%SaleTime%#, '%OrderInfo%','%Status%', '%CardNumber%');
#IDC-Insert FrontHTM-step2.asp ReportHTX-salord.htx

salord.idc is only slightly changed from what you saw in Listing 8.9. The real magic in this section comes in the template file, salord.htx, shown in Listing 8.20.

Listing 8.20. salord.htx .

This is nothing like the salord.asp (Listing 8.10) you generated at the beginning of the day. The new salord.asp generates a form, which in turn generates yet another form that returns an order number to the user. The Loadme subroutine, which is set to the form's onload event, loads and submits the <FORM> ORDERNUM as soon as the form is loaded, which calls ordernum.idc, shown in Listing 8.21.

Listing 8.21. ordernum.idc .
Datasource: BikeShop
Template: ordernum.htx
DefaultParameters:
SQLStatement:
+Select "OrderNumber"
+From "ORDERS"
+Where "LastName" Like '%LastName%'
+And "SaleTime" = #%SaleTime%#
+And "SaleDate" = #%SaleDate%#
#IDC-Insert FrontHTM-step2.asp ReportHTX-ordernum.htx

This code gets the order number from the database after the data is inserted by salord.idc. The order number is an auto-number data type. Access assigns a unique number to the auto-number type for every new record so it makes a good order number. The order number is presented in a straightforward way by ordernum.htx, as shown in Listing 8.22.

Listing 8.22. ordernum.htx .

Notice how this form uses the JPBikes! logo that you used on the other main forms. The logo is another chance to advertise your company on the order confirmation form. This form, shown in Figure 8.16, also gives the user a tracking number and shows that the order has been placed successfully in the database.

Figure 8.16. The order-tracking number.

Now that you have manipulated a <FORM> section from inside an ActiveX Control Pad built alx object and generated a file with code that enabled it to launch another job, lets finish the project.

The Order-Tracking Page

This page has a simple purpose: to let the user find out the status of an order by entering the order number you worked so hard to provide in the last section. To do this, place three buttons and a text box on an HTML insert, as shown in Figure 8.17.

Figure 8.17. Screen layout for track.alx.

Configure and name the controls using Listing 8.23.

Listing 8.23. track.alx .