Example
To illustrate the principles
that have been discussed, we will create a working example of some of the
basic building blocks of an e-commerce system, using an Access database and
Active Server Pages.
Step 1 - Create the database
The first step is to create the
database. Using Access, create a new blank database, and save it with the
name "stock.mdb" in an appropriate location.
Step 2 - Create the tables, add
the necessary fields and add some sample data
Create four new tables for the
database. Select Design View when prompted each time.
The first table is for the
stock. As shown in the diagram below, add StockID, Description, Price, and
NumberInStock fields to the table. Right click on the StockID field name and
select this field to be the Primary Key. Save the table with the name
tblStock.
Now open the table in Datasheet
view and add some sample data to the table, as illustrated in the diagram
below. You do not need to enter a value for the StockID field as this is an
AutoIncrement field.
The second table is for
customers and should be called tblCustomer.
Add the following fields to
this table: CustomerID, Username, Password, Name, Address, and Email.
Remember to add the appropriate types for each of the fields as well. You
can also create some sample customers if you wish.
Thirdly, create a table for
orders called tblOrder. This table should contain the following fields:
OrderID, CustomerID, DateOfOrder. Note that we have included a foreign key
to create a relationship between the tables. Leave this table blank.
Finally, create a table for
items that will comprise an order. This table should be called tblPurchase
and contain the following fields: PurchaseID, OrderID, StockID, Quantity.
Step 3 - Register the database
in the system registry
Your next step is to register
the database in the System registry by assigning a data source name to it
using the ODBC Administrator from the Control Panel in Windows. Use the name
dbMyShop for your data source name. Alternatively, if you are using a remote
web server, you may have to use an online control panel to assign the data
source name if you do not have direct access to the web server. You should
upload the database to the web server using FTP (File Transfer Protocol).
Step 4 - Create an ASP page to
list the products
The next step is to create a
web page, formatted however you like, but which includes a section for
listing the products in the database. The products in the database will be
accessed using ASP code contained within this web page. Therefore, the page
should be saved with the .asp extension. Save the file with the name "default.asp".
The following ASP code will
display the stock items:
<%
set conn =
server.createobject ("adodb.connection")
conn.open "dbMyShop"
set rs =
conn.execute("select * from tblStock order by Price desc")
do until (rs.EOF)
set StockID =
rs("StockID")
set Description =
rs("Description")
set Price =
rs("Price")
set NumberInStock =
rs("NumberInStock")
response.write
"<FONT size=""3"" color=""black""><B>" & Description & "</B></FONT>"
response.write
"Price: £" & Price & ". "
response.write
"Number in stock: " & NumberInStock & ""
response.write "Order
this item. Quantity: <INPUT type=""text"" name=""" & StockID & """
size=""2""> maxlength=""2"" value=""0"""
rs.MoveNext
loop
rs.close
set rs = nothing
conn.close
%>
The following lines may need
some explanation:
These two lines create a
connection to the database, using the data source name we specified earlier:
set conn =
server.createobject ("adodb.connection")
conn.open "dbMyShop"
This line creates a variable
called rs which points to a record set from the database that is created
using a simple SQL select query:
set rs =
conn.execute("select * from tblStock order by Price desc")
Values from the database are
then read from the recordset that has been created:
set StockID =
rs("StockID")
set Description =
rs("Description")
set Price =
rs("Price")
set NumberInStock =
rs("NumberInStock")
These lines are written to the
web page and form part of the response of the page to the form submission:
response.write
"<FONT size=""3"" color=""black""><B>" & Description & "</B></FONT>"
response.write
"Price: £" & Price & ". "
response.write
"Number in stock: " & NumberInStock & ""
response.write
"Order this item. Quantity: <INPUT type=""text"" name=""" & StockID & """
size=""2""> maxlength=""2"" value=""0"""
The do loop continues until all
of the records have been accessed, and then the recordset and connection are
closed.
The Form
You may notice within this code
that we have included an INPUT tag, which in this case creates a text field
to allow the user to specify how many of the particular item they wish to
order. The whole ASP script is within a FORM tag, which has the following
format.
FORM details:
<FORM method="post"
action="placeorder.asp" name="frmMyShop">
....ASP code....
<INPUT type="submit"
value="Place order">
</FORM>
Post has been used as the
method of submission for this form. The form is submitted to another ASP
page, which in this case is placeorder.asp.
Step 5 - Completing the
purchase
The placeorder.asp page has a
number of jobs. First of all, it will store the details of the items that
have been ordered by the customer in hidden fields on this new page. For the
sake of simplicity we are not using a shopping basket system. Such a system
could use more tables to store details of the items in the current user's
basket or could use cookies to store the user's basket. The hidden fields we
are using store the StockID and the quantity that the user has ordered.
The details of the order should
be displayed on screen along with a calculation showing the total cost of
the order including VAT, if applicable.
Finally, this page should
contain fields which allow the user to enter their personal details, such as
name and address, as well as their method of payment. This could be directly
by credit card using a secure third party online payment system.
Once these details have been
entered, and the user is happy with their order, they can place their order.
When they do this, the details
of the order along with the customer's personal details are entered into the
database, and the order is placed. This can be achieved using similar ASP
code to that used above, except that INSERT and UPDATE SQL statements would
be used instead of SELECT.
Our e-commerce web application
may at this point generate an email to the sales department of the company
to notify them that an order has been placed.
Generating a notification
e-mail (using the JMail ASP component):
<%
Set JMail =
Server.CreateObject("JMail.SMTPMail")
JMail.ServerAddress
= "smtp.somewebsite.com"
JMail.SenderName =
"E-commerce web site"
JMail.Sender =
"webmaster@somewebsite.com"
JMail.AddRecipientEx
"sales@somewebsite.com", "Sales"
JMail.Subject = "An
order has been placed"
JMail.Body =
"............DETAILS OF ORDER..........."
JMail.Execute
%> |
