Building an E-commerce Store with UltraDev

Lots of people have been asking for a tutorial on how to use the UltraCart. I've had this one in the pipeline for a while and I have finally gotten the time to put it together. Hope it helps.

If you want to do anything of substance with UltraDev you need to look beyond the skimpy documentation that Macromedia provides with the product. This tutorial is designed for intermediate to advanced UltraDev users. You should already be familiar with how to create recordsets, repeat regions, and forms. It is designed to present the overview of storefront creation rather than show step-by-step guidelines. If you are not that familiar with UltraDev, I suggest you spend some time building sites with material and databases you know well first.

There are an incredible range of options for creating an E-commerce application. It is not possible in the scope of one tutorial to cover them all so I'll give you the overview to one approach that I have used successfully. You will need to make you own adaptations to this to fit your business model and database design. I'll show you detailed screen shots of each major page in the site along with shots of the Data Bindings and Server Behaviors so you can see what has been applied to each page. When invisible items like Hidden Fields and ASP code have been added to a page, I have circled the item in the screen shot and marked it with a number. There will be an explanation of each of these items below the screen shot.

The underlying database used in this Tutorial is SQL Server 7. You need to be familiar with basic relational database design and it is helpful if you understand how to write stored procedures. For more information check out E-commerce Database Design Principles which shows the underlying database tables used in this tutorial.

Before beginning any E-commerce project, you should sit down with paper and pencil and sketch out the flow on your site. There are a number of important questions you have to answer in order to create the proper flow from page to page.

• Is the site open to everyone or is it membership only. If membership-driven, you will need to enforce this with some sort of initial login page or through cookies kept on the user's PC.
• Do your products have different colors and/or sizes?
• Where in your flow will you collect customer information (billing address, shipping address, credit card info, etc.)
• Will you need to deal with shipping costs, sales tax, etc.?

Login Page

This particular store application is a members-only storefront which requires users to log in before accessing the store. This uses a standard Login page with CustomerID and password which then redirects to Search.asp if the login is successful or to LoginFailed.asp is the recordset is empty. When the user logs in, several items (CustomerID, FullName) are written to Session variables so that they can be maintained for later use. Create Session variables for each CustomerID through the Server Data Bindings. These will now appear as available Session variables throughout the site. These Session variables will later be inserted into the Orders table.

Here is the Login validation code on the Login.asp page which also sets up Session variables for use later. The code evaluates to see if the form values passed to the stored procedure on the page create a recordset. If so, the Session variables are created from recordset field values. If not, the user is redirected to the LoginFailed.asp page.

Search Page

The Search.asp page contains two dynamic list boxes, one for Suppliers and one for Equipment Categories. Both allow multiple selections. The values of both of these list boxes are passed to a stored procedure on the List.asp page. The Search page uses a dynamic stored procedure to evaluate the possible multiple list box values and create the correct AND/OR operations on IN statements. [This process of Dynamic Stored Procedure Searches will be covered in the next tutorial]. Below you will see the basic layout of the page and the Data Bindings and Server Behaviors for the page. You will also see the stored procedures that create the recordsets for the two Dynamic List boxes (called SupplierID and CategoryID) which pass their values onto a stored procedure that populates the List.asp and Details.asp pages.

Product List Page

The values for SupplierID and CategoryID are passed from the Search.asp page to the List.asp page which provides the parameters for the SearchGear stored procedures (shown below) and shows the Search recordset. This search recordset is also used on the Detail.asp page.

The Search recordset on the List.asp page shows a Go to Detail SB on the product name which passes Form parameters onto the Detail.asp page. The Shopping Cart can appear on this page if you want people to be able to order products here. To do so the repeat region showing the list must be inside a form and you need a Quantity form element (viewable or hidden if 1 is the only option). Then apply the Add to Cart Server Behavior (SB). In this case because some items require size or color choices I wanted to force all users onto the detail page so they would have to deal with this. The Navigation Link SB (written by Tom Muck) allows users to move through multiple pages of the recordset returned from the search. Below you will see the basic layout of the page and the Data Bindings and Server Behaviors for the page.

The Shopping Cart Object

Defining the Shopping Cart object must be done at this point since it will appear on several subsequent pages. I didn't use it on my List.asp page but I am going to use it on the Details.asp page so I'll define how to create it here. On the Details.asp page go to Server Behavior and select UltraDev Shopping Cart/Shopping Cart. This dialog box will come up. Several fields are required in all Carts (ProductID, Name, Quantity, Name, Price, Total). You can change these names (I changed Name to Product), you don't have to use them all, but you can't remove any of these since they are hardcoded in the UltraCart code. If you want to track other information in your cart like size or color, select the + and add those column names. By default there is only one calculated Column--Total (Price * Quantity) which is again hardcoded into UltraCart. You could create other computed columns like Shipping (weight * Zone). Whatever extra columns you add items will be maintained in the cart and can be used just for display to the customer and/or they can be written to your OrderDetails database during the Check Out process. For example you might show the customer shipping weight in your Cart as drawn from the Product table but you don't want to save that information in the OrderDetails table. You would want to say the shipping cost for each item (weight * zone). When you add or change names you want them to be the same field names as exist in your database. That will make it easiest for the UltraDev behaviors to save the cart information to the correct database field. For more details on setting up the Cart, see UltraCart help files by clicking on UltraDev Help and select UltraCart Help.

UltraCart

Product Details Page

Clicking on the hyperlinked Product Name on the List.asp page takes the user to the Detrails.asp page. The Details page includes the Search recordset and the Shopping Cart. In order to add information into the Cart you need to enclose the entire detail record within a form. Place a hidden form element named ProductID within the form and bind it to the ProductID in the Search recordset. This is to make sure that Product ID is written to the Cart when you press Add to Cart. Many of the fields in the form are static and simply drawn from the database (Product Name, Price, etc.). Some fields like Quantity, Size and Color are ones that you need to user to interact with and make selections/entries. Set up these as the appropriate type of form elements and give them names that correspond to your Cart. The Add to Cart via Form SB is used to add the order information into the Cart. The recordset shows two fields: AvailableColors and AvailableSizes. Have two edit boxes labeled color and size where, when appropriate, the user would type in the correct size or color. When the user presses the Add to Cart link or button, the ProductID, ProductName, Size, Color, Price, and Quantity are added to the Cart.

Shopping Cart Page

Let's look at this page. We are only showing what is in the Shopping Cart so we don't need any recordsets. Copy the cart from another page and paste it in to Data Bindings. First add a form to the page. In order for items to be updated in the Cart (just like adding to the Cart) you need to have form elements and buttons. Put a table on the page within the form and drag the appropriate Cart columns into the table. Select the row that these columns are on and add the Cart Repeat Region SB. The Cart Repeat Region is built only to work with the Cart and it will show all items currently in the cart (the normal database Repeat Region behavior won't work here). For any items from the Cart that you want the user to able to change on this page you need to create form elements for them and put a form element in the Cart Repeat Region. Here you can see I added a form element named Quantity to allow the user to change the order quantity. I need it to show the Quantity that is currently in the Cart so I need to bind it to the appropriate Cart element (ex. select the Quantity form element click on the Quantity field listed in the Data Bindings window and click the Bind button). Do this will any other Cart fields you want the user to be able to change. The cart is just holding an array of values. You can call those value up and change them, but if the user enters a size that doesn't exist and that gets written to your OrderDetails table how are you going to fill the order. In my case I don't want the user to be able to change size or color here since they might change it to a value that is not available for that item. I want to force them back to the details page to see what the available sizes and colors are. In fact, include instructions that say, "once you have placed a size/color choice in the cart if you want to change it you have to remove the item from your cart and add it again."

There should be a set of form buttons along the top of the Repeat Region which also should be enclosed within the form. The repeat region includes one edit box element named Quantity which is bound to the Shopping Cart Quantity element. There is also a Check Box labeled Remove with automatically sets the Quantity to 0. You need to add a few other Cart SB's. Select the Quantity button and add the Update Cart SB. Do this for any other Cart field values you want the user to be able to update. Add the Redirect Cart is Empty SB and select the CartEmpty.asp page. Select the Empty Cart button and add the Empty Cart SB. Then when the Update Cart button is pushed, this item or any items manually reset to 0 are removed from the Cart. The set of buttons provides various functions. [Note: This same setup is applied to the Checkout Page. Create the Cart Page first and then save it as again as Checkout.asp. Then add the remaining behaviors and code to the Checkout.asp page.]

Cart Empty Page

This page simple holds text that says "Your cart is empty." It serves as the redirect page whenever the Cart Page is accessed and it is empty or when the Cart page is full and all items are removed. There are no Data Bindings or Server Behaviors on this page. The Exit button runs an onclick behavior that redirects the user to the start page. The Continue shopping button redirects the user to the Search.asp page. See the onclick code above for button redirects. These buttons are set to None and you can remove the form tags from the page if you wish since no form values are being passed anywhere.

Check Out Page

The Checkout page can be accessed from the Cart page or from the navigation header at any time. This page can easily be created from the Shopping Cart Page. It contains all of the same elements (already explained in detail above). The Check Out button form the Cart page is replaced with a Submit Order button which redirects to the Order.asp page using similar code.

I initially tried to use Rick Crawford's stored procedure that inserts data into the Orders and OrderDetails table from the same stored procedure. Rick Crawford's method first inserted data into the Orders table utilized the @@identity feature in SQL 7 to pull up the newly added OrderID then it loops through the Cart Array and writes the Cart Values into the OrderDetails table using the same OrderID. This is a nice approach, however I found his code way to cryptic to interpret and change to fit my database tables. I got it to work using his sample books table, but when I tried to modify his code to work with my database fields like size and color it bombed. More documentation from Rick on what he is actually doing here might help. So I developed another approach.

I create a Timekey using the code in blue below. The Timekey is basically a snapshot numerical value of current date and time. So 12/10/2000 10:13:08 PM ends up as 121000101308. I take this value and the CustomerID and insert them into the Orders table. This, along with the CustomerID, creates a unique Primary Key pair to be able to identify the newly inserted Order record. [Note: The only way this would not be a unique pair is if two people are logged on with the same CustomerID and enter this page at the exactly same time. Pretty unlikely. If you were nervous, you could set a flag field on the Customer table that sets a LogIn bit field (True/False, 0/1) to True (1) when the person Logs in and don't allow any other Logins if that bit is set to True (1). Then when the customer exists the store you reset the LogIn bit to 0.]

CustomerID already exists as a Session variable. Timekey is created on the Checkout page and made into a session variable. CustomerID, FirstName, LastName, and Class were already created as Session variables at the Login page. Create a series of Hidden Form fields called CustomerID, Timekey, FirstName, LastName and Class and bind them to the Session variables with the same names. [Custom Note: I need to add FirstName, LastName and Class to my Orders table for special reasons. In most cases CustomerID will be enough since you can pull Name data back from the Customer table when it is needed.]

The form is submitted through the Insert Record SB (see screen shot below). By setting the Session variables to form elements, you can more easily use the Ultradev Insert Record SB to automatically match up the form elements with the database fields. Once the record is inserted the Insert Record SB is set to redirect to the Orders.asp page. By default when you add in Insert SB to the form it will put the code in front of the UltraCart code. You need to select all of this code and move it to just above the <html> starting tag as shown below.

Insert Order SB

Order Page

A simply redirect here can be used for new customers. Once the user presses the Submit Order button on the Checkout Page simple VBScript code can redirect to either the Order Page to confirm shipping and billing information or to a New Customer Profile page if the CustomerID cookie or session variable does not exist. Since our example has the customer already logged on we go straight to the Order.asp page.

You will see two recordsets on this page, rsOrders and rsCustomerInfo along with the Shopping Cart. The action of submitting the Checkout.asp page added a new record to the Orders table with the CustomerID and Timekey and then redirects the user to the Order.asp page. Then the CustomerID and Timestamp generated from the Checkout page are used as a Primary Key pair to retrieve the newly added Order and retrieve the autonumber OrderID from the Orders table (so we can write the correct OrderID into the OrderDetails table).

We already know the CustomerID from the Login we pass the CustomerID on to the CustomerInfo stored procedure which brings up basic information about the person for the transaction for the rsCustomerInfo recordset. In a store that doesn't involve an initial login this could be accomplished by a cookie stored on the user's PC that contains the CustomerID or you might have the customer log in here.

The way the UltraCart code is written there must be an Insert or Update Record SB on the page that fires before you an run the Save Cart to Table SB. [Note: Rick Crawford's Access solution writes first to a dummy table to get the next OrderID. This is not the best approach in terms of database integrity. His SQL version does both through one stored procedure.] To get around his coding requirement we can Update the Customer table using the Update Record SB. This could be helpful for getting updates on new address, credit card, or shipping information. Now we can add the Save Cart to Table SB and select the appropriate fields in the OrderDetails table to insert data from the Cart. When you insert the Update Record SB UltraDev will put it at the top of the page. Move that code down so that it comes below the Recordset behaviors for rsOrder and rsCustomer info the page and so that it is still above the Save Cart to Table SB. If you don't do this move, your page won't work.

We need to pass the newly generated OrderID into the OrderDetails table. Add a Hidden Field in the form called OrderID. Select the form element and the bind it to the value for OrderID returned from your rsOrders recordset. The other option is to set the OrderID as a Session variable using this code. Depending on which approach you use the recordsets on the OrderConfirmation.asp page will either take Request("OrderID") or Session("OrderID") as their Run-time parameters.

We have two recordsets on the Orders page, one for the customer and the other for the orders. In order to properly do the update we need to make sure that UltraDev knows which recordset is our primary one. At the bottom of the page you will see a Hidden Field UltraDev generated by UltraDev. You need to make sure that the value of this field is set to Recordset.Field.Item("OrderID")

Pressing the Complete Order button fires a number of actions: Update Customer Record SB and the Save Cart to Table SB. The one disadvantage to this approach is that if the customer aborts the order before pressing the Complete Order button a row has already been written into the Orders table but there will be no corresponding OrderDetails rows. Once I can decipher Rick Crawfords' stored procedure code I will move to a one-step stored procedure where Orders and OrderDetails gets written at the same time.

Update Record SB

Save Cart to Table SB

Order Confirmation Page

This page is reached when the user presses the Complete Order button on the Order page. It contains two recordsets, OrderConfirm and OrderTotals. The Session variable OrderID created on the Orders.asp page is passed to both of these stored procedures. The OrderConfirm stored procedure links the Orders table, OrderDetails table, and the Customer table. The OrderTotals recordset calculates totals and counts on the order.

The OrderConfirmation page also includes an E-mail SB that sends a confirming E-mail as the page loads. This SB is available from the Macromedia Exchange.

Email Order SB

The Server extension was written by Julian Roberts and is available at charon.co.uk/Downloads/EmailOrder.mxp. Simply insert the SB on the Order Confirmation page and select the appropriate fields from your database. You must already have set up CDOMail to work on your server.

Well, that's it! As I said there are lots of ways to do this both in terms of page flow, database design and page code. This is just one approach that has worked for me. I hope that it helps you in your E-commerce development. Happy coding.

Rick Curtis

PS. People often ask me for source code, databases, etc. Like many others I use UltraDev as part of my business. The tutorials are a free service to the UltraDev community. Completed applications, databases, and source code cost me time and money to produce and are therefore not provided for free.