Reprinted with permission from Visual Basic Programmer's Journal, September 1999, Volume 9, Issue 9, Copyright 1999, Fawcette Technical Publications, Palo Alto, CA, USA. To subscribe, call 1-800-848-5523, 650-833-7100, visit www.vbpj.com, or visit The Development Exchange.

What you need: Visual Basic 6.0 Enterprise Edition SQL Server 7 (to use the provided database) or 6.5 (if you create your own database) Internet Information Server (IIS) 3 or later NT Server 4.0 Any Web browser

This VBPJ Special Project provides the solutions for questions you might have while developing an e-commerce site. I'll discuss setting up the database with SQL Server 7, writing a shopping cart, and loading images from the database to a browser. You'll find out how to interact with a database using ActiveX Data Objects (ADO), and how to code templates and Custom WebItems in a WebClass. The e-commerce site you'll develop lets users select a product, and add it to or delete it from the shopping cart (see Figure 1). After that, users can view the items in the shopping cart and go to the order page to make a purchase. In addition, they can search products from the database. Although the functionality of the site in this Special Project is simplified, you can extend the techniques described here to any e-commerce site.

	Figure 1 Navigate the E-Commerce Site. Click here.

WebClasses simplify the e-commerce process. Once you set up the database for your e-commerce site, Visual Basic 6 provides everything you need to get it online quickly. VB6 WebClasses have an integrated development environment, so you can easily access the resources of Internet Information Server (IIS) and Active Server Pages (ASP) and interact with the database. This allows you to create an online transaction, retrieve information from a browser, send information to it, and perform complex operations with Web page contents. In addition, WebClasses make managing a Web project, as well as the debugging process, much less troublesome. (See Figure 2 for the steps to follow during the development process.)

Figure 2 Understand the Process for the E-Commerce Web Project. Click here.

Set Up the SQL Database
Setting up a database using SQL Server 7 is pretty straightforward. For this project, I've simplified the database of an online store, representing general e-commerce sites, to two tables: Category and Product. There are four categories, and each category has four products. To get started, highlight the Databases node, right-click on and select New Database, name the database Store, and give a size of 4 MB each for the database and transaction log.

After completing the initial database setup, check the Truncate Log on Checkpoint option on the Options tab of the Database Properties dialog box. This avoids filling the transaction log quickly during the development process; just remember you need to uncheck this option once you deploy the site online. Unchecking this option allows you to restore changes made to the database if the system fails. Use a SQL script to create the tables. Select Tools | SQL Server Query Analyzer (for SQL Server 7), and choose Store from the DB pull-down list. Type or paste in the Store.sql SQL script (download the project code here) and click on the Execute button. This code shows part of the script for creating the Product table:

CREATE TABLE Product
   ( Prod_id smallint,
   PRIMARY KEY NONCLUSTERED 
      IDENTITY(1, 1),
   Categ_id smallint NOT NULL 
      FOREIGN KEY REFERENCES 

   Category(Categ_id),
   Prod_name varchar(100) _
      NOT NULL UNIQUE NONCLUSTERED,
   Prod_desc text NOT NULL,
   Prod_unitprice smallmoney NOT NULL,
   Prod_unitquant Char(10) NOT NULL,
   Prod_pic image NULL)

The Product table consists of a primary key and a foreign key. The primary key is nonclustered, because it has a distinct value for each row (Identity property). The foreign key is referenced to category ID (the Categ_id column) in the Category table. The category ID connects the Category table and the Product table using a one-to-many relationship. The Prod_desc and Prod_pic columns contain the description of a product and its image, respectively. Its image is defined as a Binary Large Object (BLOB). Prod_unitprice and Prod_unitquant give the product's unit price and unit quantity. The Category table has only two columns: Categ_id and Categ_name.

Every e-commerce site uses a tool called a shopping cart (or shopping basket) to record information about items a customer chooses to buy. For example, say you want to buy two packs of chocolate at $10.95 each. Using the shopping cart, you can store the product name (Almond Clusters), the quantity (two packs), and the unit price ($10.95). If you actually make the purchase, the contents of the shopping cart are passed to the database.

In software terminology, the data in the shopping cart has state. State is object data that is maintained across multiple calls from a client. Particularly, data stored in the shopping cart has two characteristics. First, the shopping cart can contain more than one item, so data can be in a series, stored in the form of an array. Second, you need to allow the customer to add a new item or delete an existing item from the shopping cart. These characteristics make transferring a customer's shopping list over the Internet and storing this list in the shopping cart more complex.

Maintain State
You can maintain state on either the client or the server side, and you can use several available methods to persist data (see Table 1). One way to maintain state is to use cookies to implement the shopping cart. However, clients need to have a browser supporting cookies. In addition, coding cookies takes effort. For example, if a user wants to delete an item from the shopping cart, first you need to search for this item in the cookie. Then, you need to rewrite the cookie completely for the existing selected items. An ASP QueryString method is adequate for a few nonchangeable data items such as username or user preferences, but isn't powerful enough for the shopping cart. In addition, QueryString can hold only about 2K of data. A third way to maintain state is to use a disk file to store data in the shopping cart. However, reading and saving data to the hard drive is time-consuming. Also, storing data in the disk file might cause the problem of having to manage each visitor. Using a database is another candidate for maintaining state. Although it's easy to store data in a table in the database, every data connection is an expensive server resource.

After comparing all available methods of maintaining state, a Component Object Model (COM) component is the best way. A COM component (or class) is flexible, reusable, and easy to implement. However, using a COM component consumes server memory, so you need to keep the data in the shopping cart as lean as possible.

Creating a COM component is similar to adding a class to your project. In the sample e-commerce site for this Special Project, you implement the shopping cart using a class named clsShoppingCart in the WebClass project. You set the Instancing property of clsShoppingCart to Private, because the WebClass and clsShoppingCart are in the same project. Write four Get properties (read-only properties) and two methods in the class. You use the Get properties to retrieve information about an ordered item, while you use the methods to add or delete the information (see Listing 1).

Declare one variable (mintNum-berOfOrders) and three arrays (mstrName-OfItem(), mdblUnitPrice() and mint- Quantity()) at the beginning of the class. The mintNumberOfOrders variable gives the number of orders. Use the arrays to store the name, unit price, and quantity of an ordered item. The dimensions of the arrays are defined dynamically. The application inserts the name, unit price, and quantity of an ordered item into the respective arrays in Sub AddItem, while Sub DeleteItem lets you delete an existing item according to name and quantity. Also, place error handlers inside the two methods.

Load Images From Database to Browser
With SQL Sever, you can store an image as a BLOB in the database table. If you develop a front-end Windows application using either VB or Visual C++, you can easily retrieve the image data from the database and show it on screen. However, things become more complicated if you want to get the image from the database and show it, together with other HTML, on a Web browser.

You can use three approaches to deliver the image from the database to the browser. The first way is to keep the image file on the hard drive, and store the file's name and path in the database. I don't recommend this approach, because putting the image file and its path in two different locations causes management problems. For example, if you change the filename, you must change the data entry in the database for its associated path. The second approach is to use the ASP Response object's BinaryWrite method. This method passes the image to the browser as an HTTP transaction with an HTTP header containing the IMAGE/GIF or IMAGE/JPEG type. Suppose you have a recordset named Rs that contains the image column Prod_pic in the Store database's Product table. This code demonstrates how to apply the ASP Response object's BinaryWrite method, where Cn is an instance of the ADO Connection object:

Response.Buffer = TRUE
   Response.Clear
   Response.ContentType = "image/gif"
   Sql = "SELECT Prod_pic FROM" & _
      "Product WHERE Prod_id = 2"
   Set Rs = Cn.Execute(Sql, , _
      adCmdText)
   Response.BinaryWrite Rs("Prod_pic")
   Response.End

You must use the Response object's Clear method; otherwise users will see only ASCII in the browser, not the image. However, the Clear method erases any buffered HTML output. As a result, this piece of code displays only an image on the screen.

There's a better way to display images that allows you to have HTML together with the image. This method circumvents the management problems of image data, because it centralizes image data in the database. Use the Field object's GetChunk method in ADO to retrieve the BLOB's content. Then, save the BLOB's content to a disk file using the VB Put statement. Use the product ID (Prod_id) to identify each image file, displaying the image for the correct product. You don't need to worry about what type of images (GIF or JPG) are stored in the database. After you create the image file, you can put its path as the SRC attribute in the IMG tag. You might question the time consumed in writing the image file to the hard drive, but you need to write only once. The program checks for existing files, and if the same file is already there, it doesn't write it to the drive.

Wrap Up With WebClasses
VB6 introduced a new type of project: an IIS application. An IIS application is a server-side application that's browser-independent on the client side. In IIS applications, you use the ASP object model to retrieve information from the user, send information to the browser, and interact with data services. The object model allows you to create dynamic interactive content and to implement complex business rules, and it gives you all the benefits of working in VB, such as using class modules, controls, and designers. In addition, you can debug your IIS project under VB's standard debugging environment. (Script and CGI-based Internet applications, on the other hand, can be difficult to debug.) IIS applications also make it simple to manage a project.

Like HTML and other window-based VB applications, one important aspect of IIS applications is designing the user interface. In the sample e-commerce site, I laid the whole page out into three frames (see Figure 1). The left frame shows the category items in the Category table. The right frame is the main area displaying product information, contents in the shopping cart, and order information. The top frame functions as menus, and includes four buttons: View Basket, Order, Search, and Help. Each button links to a Custom WebItem (see Figure 3).

	Figure 3 Behind the Scenes of the E-Commerce Project. Click here.

Name the project Ecommerce and the WebClass wcStore, then set the State-Management for wcStore as wcRetainIn-stance. The WebClass object instance is created when a user requests the URL of your e-commerce site through a browser. The value of wcRetainInstance ensures the instance isn't destroyed until the WebClass object either calls ReleaseInstance or the ASP session times out. So, the server can safely keep state between browser requests, and state won't be destroyed at the completion of each request made by a client. You must set the StateManagement property as wcRetainInstance, because you'll keep the information in the shopping cart over more than one HTTP request.

Next, create two HTML Template WebItems: tmpHome and tmpTop. HTML templates are HTML files parsed by a WebClass and included in a WebClass project. Each HTML template represents an HTML page. In this e-commerce Web project, HTML templates tmpHome and tmpTop come respectively from the Home.htm and Top.htm pages. tmpHome starts three frames, as shown in Figure 1, while tmpTop displays the menu buttons. To add the HTML template, make sure you're in the WebClass Designer view. Then, right-click on wcStore to bring up the Add HTML Template dialog box, and type Home.htm in the File Name field to open it.

In addition to HTML templates, there are seven Custom WebItems: wciCategory, wciDeleteItem, wciHelp, wciOrder, wci-Product, wciSearch, and wciViewItem. Unlike HTML templates, which are linked to HTML pages, Custom WebItems provide more programmatic resources. The process of creating a Custom WebItem is similar to that of an HTML template, as shown in this code for the HTML template tmpHome:

<HTML>
   <HEAD>
   </HEAD>
   <FRAMESET rows=15,85>
   <FRAME align=top name=Top 
scrolling=no
src=">
   <FRAMESET cols=17,73>
   <FRAME name=Category scrolling=yes 
   src=">
   <FRAME name=Product scrolling=yes 
   src=">
   </FRAMESET>
   </FRAMESET>
</HTML>

If you compare tmpHome (HTML template) and Home.htm (HTML page), you'll find that VB6 parses the HTML page and lists all the tags in the template. Each tag can launch a request to the server. For example, you can use the attribute SRC inside the FRAME tag to specify a URL location to retrieve a file from. So, using SRC, you connect FRAME tags to the top, left, and right frames. You fill these frames using the tmpTop template, the wciCategory Custom WebItem, and the wciProduct Custom WebItem, respectively. To do so, click on tmpHome in the left panel of the WebClass Designer. Then, in the Designer's right panel, right-click on the attribute you want to connect, then select Connect to WebItem. In the dialog box, select the WebItem you want to connect the tag to, then click on OK. To check which item is connected to which tag/attribute, look under the Target column in Figure 3. (See Table 2 for information about the connection between the tags and WebItems for the tmpHome and tmpTop templates.)

After you connect the frame tags in tmpHome to the WebItems, put this code into the tmpHome template's Respond event. To insert the code, right-click on tmpHome, click on Context | View Code, and type "tmpHome.WriteTemplate" in the skeleton handler VB has created. Your subroutine should look like this; otherwise, you won't see three frames on your browser:

Private Sub tmpHome_Respond()
tmpHome.WriteTemplate
End Sub

Interact With the Database and Shopping Cart
To interact with the database and shopping cart, create two classes in the Ecommerce project: clsStoreDb and clsShoppingCart. clsStoreDb takes care of all database issues, such as database connection and retrieving data from the database. clsShoppingCart provides shopping cart services (copy the code in Listing 1 to clsShoppingCart).

To interact with the database, you need to use ADO in clsStoreDb to connect to the database and create a Recordset object. For the database connection, use a data source name (DSN) in the Connection object's ConnectionString property. You can create a system DSN by going to ODBC under the Control Panel. You can use four methods to create a Recordset object: the Connection object's Execute method, the Command object's Execute method, the Recordset object's Open method, and a stored procedure within SQL Server. If you use the Connection object's Execute method, the returned Recordset object is always a read-only, forward-only cursor. If you need a Recordset object with more functionality, first create a Recordset object with the required property settings, then use the Recordset object's Open method to execute the query and return the desired cursor type.

Now, take a look at the roles of the Custom WebItems in your e-commerce project. The wciCategory contains the category name from the Category table in the database. If users click on a category in the browser's left frame, the browser's right frame shows all products corresponding to that category. The Custom WebItem's UserEvent event, called wciProduct, contains the code to retrieve the product information from the Product table in the database. You use UserEvent, instead of the default event, to pass the category ID (Categ_id) as an EventName (see the code for wciProduct in Listing 2). Note that you should use the URLFor method to programmatically generate the HREF attribute used in the anchor tag (<A>) to navigate to the page. Don't hardcode a URL such as www.yourserver.com/yourproject.asp. Also, close the database connection once you stop using it, because it's an expensive server resource.

When users are in the product page, they can add any product they want to the shopping cart. Clicking on the View button in the top frame directs users to wciView. This page shows all items the shopping cart contains. After users add items to the shopping cart, they can delete the shopping cart or go to the order page to complete the purchase. Use wciDelete and wciOrder for the deleting and ordering operations.

Every e-commerce site must be able to search for merchandise in the database. In our example, users can enter the keyword for a product. This keyword is passed to the GetProductIdsBySearchKeyword method of the clsStoreDb class. After calling the method, users see a list of product IDs (Prod_id) whose names match the keyword. To enable the searching capability, create a recordset using the condition clause WHERE in the SQL script. The e-commerce application uses the product ID(s) to retrieve the complete information about the product(s), such as product name, product description, unit price, and product image. The Custom WebItem wciSearch allows users to enter a keyword, which displays the results from searching the Product table in the database.

The last step in developing your e-commerce site is compiling your IIS application. Click on File | Make Ecommerce.dll to open the Make Project dialog box, then click on OK to make the DLL file. The startup file name is defined by the WebClass' NameInURL property. In your case, for the WebClass wcStore, the file name is Store.asp.

Once you develop and compile the code, you still need to deploy your IIS application on the Web server. You can prepare the installation kit using the VB Package and Deployment Wizard (see the sidebar, "Package and Deploy a WebClass Application").

You now know the major aspects of developing an e-commerce site using VB6 and SQL Server 7. You can extend these techniques to your project and add more functionality. For example, you can add code to verify users' credit card numbers first, and add a page listing a customer's account information. E-mail notification is another essential feature for a commercial site. You can put shipping information in e-mails and send them to customers who have made purchases. There are three methods of sending e-mails: using MAPI controls, using Collaboration Data Objects (CDO), or creating a trigger inside SQL Server that runs SQL Server's xp_sendmail extended procedure. Transaction management is also important. Every transaction should have ACID (Atomicity, Consistency, Isolation, and Durability) properties. Consider using Microsoft Transaction Server (MTS) objects in your code for this purpose. Whatever enhancements you choose, always simplify the project into basic elements. Then, think about the right method for working on each element. Once you understand the basic elements required for developing an e-commerce site, using the combination of VB6 and SQL Server is an ideal option to get it online quickly.


Jia Wang, Ph.D., MCSD, is president of Promesse Systems (www.promesse.com), which offers database consulting services. He has extensive experience in the fields of software development, database design and implementation, numerical methods and analysis, and mathematical formulations. You can reach Jia at mail@promesse.com.