Store Database: The “Store” database is in Access. It contains a table called “Products.” More tables will be added to create a database for an e-commerce system.
The Products table contain several fields. To start with, each field except the “prod_id” is declared as text. In the future, the “prod_price” field should be declared as money. Other fields will be added as necessary. None of the fields are required except the Prod_id, but zero length text is not allowed. Thus, there is a validation in the database to all the fields. The type and size of the fields are as follows:
Field / Type / Size / Required / Allow Zero LengthProd_id / Autonumber / Yes (auto) / No
Prod_name / Text / 20 / No / No
Prod_price / Text / 10 / No / No
Prod_picture / Text / 30 / No / No
Prod_type / Text / 20 / No / No
Prod_desc / Text / 50 / No / No
HTML and ASP Pages: Separate ASP pages are developed to display, update, add, and delete products in the Product table. These pages will be modified in the future, so that a complete e-commerce system can be developed.
addProducts.htm
This is the form to enter data for a product in the Products table. This form calls addProducts.asp file. All data are considered as text and no validation check on data are performed in the form. If any of the fields is empty, an error is occurred due to database validation. Check your data with all possible scenarios.
addProducts.asp
This file contains server-side scripts that contain database connectivity and necessary SQL commands to add a product that is read from the user input of the form AddProfucts.htm
Code for addProducts.htm
<html>
<head<title>Add Product </title</head>
<body bgcolor="gray">
<form method="post" action="addProducts.asp">
<center>
<table width="600" border=1 bgcolor="lightyellow"
cellpadding="4" cellspacing="0">
<tr>
<td colspan="2" bgcolor="yellow">
<font face="Arial" size="3"<b<center>
Add Product</center>
</b</font>
</td>
</tr>
<tr>
<td>
<b> Product Name:</b>
</td>
<td>
<input name="productName"
size="20" maxlength="20">
</td>
</tr>
<tr>
<td>
<b>Product Price:</b>
</td>
<td>
<input name="productPrice" size="10">
</td>
</tr>
<tr>
<td>
<b>Product Picture:</b>
</td>
<td>
<input name="productPicture"
size="30" maxlength="30">
</td>
</tr>
<tr>
<td>
<b>Product Type:</b>
</td>
<td>
<input name="productType"
size="20" maxlength="20">
</td>
</tr>
<tr>
<td>
<b>Product Brief Desc:</b>
</td>
<td>
<textarea name="productDesc"
cols="50" rows="2" wrap="virtual"</textarea>
</td>
</tr>
<tr>
<td colspan=2 align="right">
<input type="submit" value="Add Product">
</td>
</tr>
</table>
</center>
<input name="addProduct" type="hidden" value="1">
</form>
</body>
</html>
Code for addProducts.asp
<%
'Author: Dr. Mohammad A. Rob
'This is a simple example of database opening, closing, and inserting a record
' Get the Form Variables from addproduct.htm
addProduct = TRIM( Request( "addProduct" ) )
productName = TRIM( Request( "productName" ) )
productPrice = TRIM( Request( "productPrice" ) )
productPicture = TRIM( Request( "productPicture" ) )
productType = TRIM( Request( "productType" ) )
productDesc = TRIM( Request( "productDesc" ) )
'Make sure that the database is closed (both by the user and by the program)
'Opening a database which is closed properly can cause a problem
' Open the Database Connection using a System DSN (must have a DSN defined in the Web Server)
'Set Con = Server.CreateObject( "ADODB.Connection" )
'Con.Open "accessDSN"
' Open the Database Connection using acconnection string with mappath
Dim Con
set Con = Server.CreateObject("ADODB.Connection")
Con.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("store.mdb")
'Verify that the database connection is open
response.write ("Database Connection =")
response.write (Con.state)
%>
<html>
<head<title>Add a New Products</title</head>
<body bgcolor="white">
<p>
<%
' Add New Product
sqlString = "INSERT INTO Products " &_
"( prod_name, prod_price, prod_picture, prod_type, prod_desc ) VALUES ( " &_
" '" & productName & "' , '" & productPrice & "' , '" & productPicture & "', " & _
" '" & productType & "' , '" & productDesc & "' )"
'Test the SQL string
response.write ("SQL String entered =")
response.write (sqlstring)
'Execute the SQL string
Con.Execute sqlString
'Close the database connection
Con.close
set Con = nothing
%>
<p>
<%=productName%> was added to the database
</body>
</html>
Open addProducts.htm and then enter an item in the database.
Modified Store Database: The “Products” table in the database is modified to include currency/integer value for the prod_price field. The table is called, “ProductsNew.”
Field / Type / Size / Required / Allow Zero LengthProd_id / Autonumber / Yes (auto) / No
Prod_name / Text / 20 / No / No
Prod_price / Number / - / No / -
Prod_picture / Text / 30 / No / No
Prod_type / Text / 20 / No / No
Prod_desc / Text / 50 / No / No
addProductsNew.htm
This form is modified from addProducts.htm. This form calls addProductsNew.asp file as shown below. All data entered in the form are verified for text and numeric values as required by the database table. Check your data with all possible scenarios.
addProductsNew.asp
This file contains server-side scripts that contain database connectivity and necessary SQL commands to add a product that is read from the user input of the form addProfuctsNew.htm
The script checks for validity of data entered on the form such as empty values and numerical value for price.
Code for addProductsNew.htm
All codes are same as the addProducts.htm, except the following form tag is replaced to reflect the new asp file, addProducts.asp (codesshown below).
<form method="post" action="addProductsNew.asp">
Code for addProductsNew.asp
<%
'Note, HTML tags are not allowed within the scripts.
'Comments are placed inside the script tags, so that it is not printed.
' Get the Form Variables from addproduct.htm
productName = TRIM( Request( "productName" ) )
productPrice = TRIM( Request( "productPrice" ) )
productPicture = TRIM( Request( "productPicture" ) )
productType = TRIM( Request( "productType" ) )
productDesc = TRIM( Request( "productDesc" ) )
%>
<p>
<%
'Check that the field values are not empty and they are of the type required in the "NewProducts" table
'If any of the form fields is empty, addItem variable will be an empty string
Dim addItem
addItem = "true"
IF productName = "" THEN
response.write ("You must enter a product name. ")
addItem = ""
END IF
IF productPrice = "" THEN
response.write ("You must enter a product price. ")
addItem = ""
END IF
'Verify that the product price is a number
IF NOT isNUMERIC( productPrice ) THEN
response.write ("Product price must be a number. ")
addItem = ""
END IF
IF productPicture = "" THEN
response.write ("You must enter a product picture value. ")
addItem = ""
END IF
IF productType = "" THEN
response.write ("You must enter a product type. ")
addItem = ""
END IF
IF productDesc = "" THEN
response.write ("You must enter a product description. ")
addItem = ""
END IF
' Open the Database Connection using acconnection string with mappath
Dim Con
set Con = Server.CreateObject("ADODB.Connection")
Con.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("store.mdb")
'HTML tags are not allowed within the scripts. See below
%>
<html>
<head<title>Add a New Products</title</head>
<body bgcolor="white">
<p>
<%
' Add New Product
IF (addItem > "") THEN
sqlString = "INSERT INTO ProductsNew " &_
"( prod_name, prod_price, prod_picture, prod_type, prod_desc ) VALUES ( " &_
" '" & productName & "' , " & productPrice & " , '" & productPicture & "', " & _
" '" & productType & "' , '" & productDesc & "' )"
'Write out the SQL string
response.write (sqlstring)
'Execute the SQL string
Con.Execute sqlString
'Close the database connection
Con.close
set Con = nothing
%>
<p>
<%=productName%> was added to the database
<% END IF %>
</body>
</html>
Open addProductsNew.htm and then enter an item in the “ProductsNew” table.
selectProducts.asp
This file retrieves product ID and Product name from the “ProductsNew” table of the “Store” database. This example shows how a return field is tied up with a hyperlink to a file that will be executed on click event. See updateProducts.asp below.
Code for selectProducts.asp
<%
' Open the Database Connection
'Set Con = Server.CreateObject( "ADODB.Connection" )
'Con.Open "accessDSN"
Dim Con
set Con = Server.CreateObject("ADODB.Connection")
Con.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("store.mdb")
%>
<html>
<head<title>Products Available</title</head>
<body bgcolor="gray">
<center>
<table width="600" border=1 bgcolor="lightyellow"
cellpadding="4" cellspacing="0">
<tr>
<td colspan="2" bgcolor="orange">
<font face="Arial" size="3"<b>
<center>These are the Products in the Store. <p>Click a hyperlink to look at the details and edit the product</center>
</b</font>
</td>
</tr>
<tr>
<td align="center">
<table border="2" size="400" cellpadding="3"
cellspacing=0 bgcolor="white">
<tr>
<th>Product ID</th>
<th>Product Name</th>
<tr>
<%
sqlString = "SELECT prod_id, prod_name FROM productsNew " &_
"ORDER BY prod_name"
SET RS = Con.Execute( sqlString )
WHILE NOT RS.EOF
%>
<tr>
<td>
<a href="updateProducts.asp?pid=<%=RS( "prod_id")%>"<%=RS( "prod_id" )%</a>
</td>
<td>
<%=RS( "prod_name" )%>
</td>
</tr>
<%
RS.MoveNext
WEND
%>
</table>
</td>
</tr>
<tr>
<td>
<a href="addProductsNew.htm">Add a New Product</a>
</td>
</tr>
</table>
</center>
</body>
</html>
Run selectProducts.asp to display all products
UpdateProducts.asp
This file is used to update a product. It is first used within the selectProducts.asp file (see above) to create a hyperlink and then to retrieve details of a selected product ID. The addProductsNew.asp file (see before) is used to display the details of the selected product. The product can then be updated.
Codes for UpdateProducts.asp
<%
' Get the Product ID
productID = Request( "pid" )
' Open the Database Connection
'Set Con = Server.CreateObject( "ADODB.Connection" )
'Con.Open "accessDSN"
' Open the Database Connection using acconnection string with mappath
Dim Con
set Con = Server.CreateObject("ADODB.Connection")
Con.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("store.mdb")
' Open the Recordset
Set RS = Server.CreateObject( "ADODB.Recordset" )
RS.ActiveConnection = Con
RS.CursorType = 3
RS.Open "SELECT * FROM ProductsNew WHERE prod_id=" & productID
IF NOT RS.EOF THEN
productName = RS( "prod_name" )
productPrice = RS( "prod_price" )
productPicture = RS( "prod_picture" )
productType = RS( "prod_type" )
productDesc = RS( "prod_desc" )
END IF
' Close the Recordset
RS.Close
%>
<html>
<head<title>Update Product </title</head>
<body bgcolor="gray">
<form method="post" action="addproductsNew.asp">
<center>
<table width="600" border=1 bgcolor="lightyellow"
cellpadding="4" cellspacing="0">
<tr>
<td colspan="2" bgcolor="yellow">
<font face="Arial" size="3"<b>
Update Product
</b</font>
</td>
</tr>
<tr>
<td>
<b> Product Name:</b>
</td>
<td>
<input name="productName"
size="20" maxlength="20"
value="<%= productName %>">
</td>
</tr>
<tr>
<td>
<b>Product Price:</b>
</td>
<td>
<input name="productPrice" size="10"
value="<%=productPrice%>">
</td>
</tr>
<tr>
<td>
<b>Product Picture:</b>
</td>
<td>
<input name="productPicture"
size="30" maxlength="30"
value="<%= productPicture %>">
</td>
</tr>
<tr>
<td>
<b>Product Category:</b>
</td>
<td>
<input name="productType"
size="20" maxlength="20"
value="<%= productType %>">
</td>
</tr>
<tr>
<td>
<b>Product Description:</b>
</td>
<td>
<textarea name="productDesc"
cols="50" rows="2"
wrap="virtual"<%= productDesc %>
</textarea>
</td>
</tr>
<tr>
<td colspan=2 align="right">
<input type="submit" value="Update Product">
</td>
</tr>
</table>
</center>
<input name="productID" type="hidden" value="<%=productID%>">
<input name="updateProduct" type="hidden" value="1">
</form>
</body>
</html>
Run selectProducts.asp again to display all products from the “ProductsNew” table, and then click on a hyperlink to see how the above code for updateProducts.asp works
1