Lecture 9- ABC Tutorial
Download
Report
Transcript Lecture 9- ABC Tutorial
Tutorial 2 ABC Web site
1
Objective
Learning web applications design
Conducting assumed business logic online
Connecting the Database with the web
pages
Major Tasks
Product Searching
Ordering
Customer Administration
Registration
Password-based Security Mechanism
http://buscom.mcmaster.ca/users/yuanyuf/
Tutorial_asp/homepage.html
http://facbusad1.mcmaster.ca/users/ap1/yu
anyuf/Tutorial_asp/homepage.html
Tutorial_2.zip
8 Files
1 subfolder: pictures
Homepage.html, Search.asp, Results.asp, Check.asp,
Ordering.asp, Registration.htm, Registration.asp,
Adovbs.inc
5 JPG files with the product codes as the file names
Download Tutorial_asp.zip
Unzip the files and put it in your sub directory
Tutorial_asp in your Q drive
Run the application from your web site
http://facbusad1.mcmaster.ca/users/ap1/yourusername/Tutorial_asp/homepage.html
Download and Unzip
You download the Tutorial_asp.zip file
from course web site.
You save it to Q drive
You then right click using Open With
Compressed (zip) folders
Define your site in Dreamweaver
Use a server technology ASP
VBScript
At home you
should select
option 2. You
must make sure
you have logged
on McMaster
VPN.
In lab, you can
select option 3
because you can
access Q drive
dirctely
FTP hostname:
facbusad1.mcmaster.ca
Folder on the testing server:
Tutorial_asp
FTP Login:
Your MAC login name
FTP Password:
Your MAC login password
Test Connection:
When you test the ftp
connection at home, you
must make sure you have
logged in McMaster VPN
because facbusad1 is not
open to public.
Test URL: http://facbusad1.mcmaster.ca/users/ap1/
yourname/Tutorial_asp/homepage.html
ABC Database
Please do not use space in
the field names
Business Logic
Select Searching Method
Homepage.html
Specify Search Criteria
Search.asp
Product Display & Ordering
Results.asp
Save order items &
Customer Logon
Check.asp
Order Confirm & Display
Ordering.asp
Customer
Registration
Registration.htm
Processing
Registration
Registration.asp
Select Searching Method
(Homepage.html)
Homepage.html
Source Code of select search method
Invoke search.asp
when submit
<form action="search.asp" method="post">
<select name="searchingMethod" size="1">
<option value=“ByProductName">Product Name</option>
<option value=“ByPriceRange">Price Range</option>
</select>
<input type=submit value="Go!">
</form>
Select one of the
Two options. In your
assignment you can
add ByOccasion
Search.asp
Specify Product Name
Specify Price Range
<% select case Request.form(“SearchingMethod") %>
<% case “ByProductName"%> ‘Case 1#
<input type=hidden name=“SearchingMethod“ value=“ByProductName">
<input type=text name="ProductName" value="" size=20 maxlength=20>
<input type=submit value="Go!">
<% case “ByPriceRange"%> ‘Case 2#
<input type=hidden name=“SearchingMethod" value=“ByPriceRange">
<select name="comparison" size="1">
<option value=">">greater than </option>
<option value="<">less than </option>
Use case to display
Different search form
</select>
<input type=text name="PriceRange" value="$" size=10 maxlength=10>
<input type=submit value="Go!">
<% end select %>
Product Display & Ordering
Flowchart of Results.asp(1)
Create a database connection
Request.Form(“SearchingMethod”) ?
= “ByProductName”
Create a SQL with
Request.Form(“ProductName”)
= “ByPriceRange”
Create a SQL with
Request.Form(“PriceRange”)
Request.Form(“Comparison”)
Execute the SQL
Flowchart of Results.asp(2)
Are there any products meet the criteria ?
N
Hyperlink to
“Homepage.html”
Y
•Show the Products
•Provide Checkbox and Input
Box for Customer to Select
and Specify the Quantity
Click “Buy Now”
Close Database
Connection
Source Code of Results.asp
ODBC Connection:
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open “DSN=ODBCyuanyuf;uid=yuanyuf;pwd=yuank723”
To connect to your SQL database, you have to
change the DSN, uid, and pwd to your setting.
Select * from Products where (Unit_Price >100) order by Product_code
Select * from Products where (Product_Name like ‘%vacuum%’)
Source Code of Results.asp
<% select case Request.form(“SearchingMethod") %>
<% case “ByPriceRange”
In VB, using
underscore to
continue a line.
SQL = "SELECT * FROM Products WHERE (Unit_Price"_
& Request.Form("Comparison") & Request.form("PriceRange")_
& ") Order by Product_Code”
case “ByProductName”
& is used to combine
strings
SQL = "SELECT * FROM Products WHERE (Product_Name Like '%"_
& Request.form("ProductName") & "%') Order by Product_Code”
end select %>
Product Display & Ordering
<%RecordNo = 1%>
<% do while Not Rec.EOF %>
Checkbox name as Product1,
Product2,…
If checked,
the value is
product_code
<input type="checkbox" name=“Product<%=RecordNo%>"
value="<%=Rec.Fields("Product_Code").Value%>">
<img src="pictures/<%=Rec.Fields("Product_Code").Value%>.JPG">
<input name=“QuantityOfProduct<%=RecordNo%>" value="1"
size="5" maxlength="10">
<%Rec.MoveNext
QuantityOfProduct1,…
RecordNo = RecordNo + 1%>
<% loop%>
<input type="hidden" name="NumberOfResults"
Calculate and save
number of results
value="<%=RecordNo-1%>">
<input type="submit" name="buyButton" value="Buy Now">
Results.asp
Form input values (example)
Checkbox Name = Product1
Checkbox value =“A021”
QuantityOfProduct1=1
Checkbox Name = Product1
Checkbox value = “”
QuantityOfProduct2=1
Checkbox Name = Product3
Checkbox value =“B043”
QuantityOfProduct3=2
NumberOfResults= 3
checked
did not check
checked
Flowchart of Check.asp
Based on Request.Form(“NumberOfResults”)
Repeat the Following Check
CheckBox “RecordNo” Checked?
N
Y
• Save the selected Products and their
quantities into Session Objects of ASP
• Save the number of Selected products to
Session Object
Input Customer ID and Password
Click “Place Order”
Source Code of Check.asp
Save the Results to Session Variables
ItemNo = 1
for i = 1 to Request.Form("NumberOfResults")
if (Request.Form("Product" & i)<> "") then
Session("ProductCodeOfOrderItem" & ItemNo) =
Request.Form("Product" & i)
Save
multiple
Order line
data to
session
Session("QuantityOfOrderItem" & ItemNo)=
Request.Form("QuantityOfProduct" & i)
Session("TotalOrderItem")= ItemNo
ItemNo = ItemNo + 1
end if
next
Save the Results to Session
Variables
Session(ProductCodeOfOrderItem1=A021)
Session(QuantityOfOrderItem1=1)
Session(ProductCodeOfOrderItem2=C387)
Session(QuantityOfOrderItem2=2)
Session(TotalOrderItem= 2)
User Logon
Source Code of Check.asp
input customer name and password
<form action="ordering.asp" method="post">
<input name="CustomerName" size="17" maxlength="32">
<input name="CustomerPassword" type="password"
size="17" maxlength="32">
<input name=emptyCheck type=button value="Place Order"
onClick="myform(this.form)">
<form>
Source Code of Check.asp
check if the user has inputted name and
password using java script at Client site
<!-- function myform(theForm) {
if(theForm.CustomerName.value.length <= 0) {
alert("Sorry, you should not leave your CUSTOMER
NAME empty!")
return false}
if(theForm.CustomerPassword.value.length <= 0) {
alert("Sorry, you should not leave your PASSWORD
empty!")
return false}
theForm.submit() } -->
Order Confirm & Display
Flowchart of Ordering.asp
Create a database connection
Define three Recordset Objects, corresponding to
Customers, Orders and Orderln table
Create and Execute a SQL
Check if the customer has registered
Y
Get New Order Number
Use the Recordsets defined above to Update
Orders and Orderln respectively
Display Order Information
Close Database Connections
N
Hyperlink to
Registration.htm
Source of Ordering.asp
Database Connection
Include the Definition of ADO Constants:
<!-- #Include file="ADOVBS.INC" -->
Create ODBC Connection:
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "DSN=ODBCyuanyuf;uid=yuanyuf;pwd=yuank723“
You should use your DSN, uid, and pwd.
Source of Ordering.asp
Create record sets for table updating
Create record sets for updating three tables :
Set RsCustomers = Server.CreateObject("ADODB.Recordset")
Set RsOrders = Server.CreateObject("ADODB.Recordset")
Set RsOrderln = Server.CreateObject("ADODB.Recordset")
Source of Ordering.asp
Check if user has registered
Create and Execute SQL to Check if the user has registered:
SQL = "SELECT * FROM Customers WHERE ((Customer_Name='"_
& Request.form("CustomerName") & "') AND (Password='" _
& Request.Form("CustomerPassword") & "'))"
Set RsCustomers = Conn.Execute(SQL)
Source of Ordering.asp
if user has registered, save order
If So:
<%if not RsCustomers.EOF then %>
Save the Order Information
Otherwise:
<%else%>
<a href="registration.htm">Sign up now</a>
<%end if%>
Source of Ordering.asp
Check if user has ordered some products
Save Order Information:
Check if the user already has selected some products:
if (Session(“TotalOrderItem")>0) then
Source of Ordering.asp
generate a new order number
To avoid duplicated order numbers, generate a newOrderNumber as the
Max(Order_Number) +1:
SQL = "SELECT Max(Order_Number) As BaseOrderNum”_
&”FROM Orders"
Set RsOrders = Conn.Execute(SQL)
newOrderNumber = RsOrders(“BaseOrderNum") + 1
RsOrders.Close
Note: if the OrderNumber in Orders table is defined as autonumber
then need not use this method
Source of Ordering.asp
Update Orders table
RsOrders.ActiveConnection = Conn
RsOrders.CursorType = adOpenStatic
RsOrders.LockType = adLockOptimistic
RsOrders.Source = "Orders"
RsOrders.Open
RsOrders.AddNew
RsOrders("Order_Number") = newOrderNumber
RsOrders("Order_Date") = date()
RsOrders("Customer_Name") = Request.Form("CustomerName")
RsOrders.Update
RsOrders.Close
the user’s order we have saved in
Session Variables
Session(ProductCodeOfOrderItem1=A021)
Session(QuantityOfOrderItem1=1)
Session(ProductCodeOfOrderItem2=C387)
Session(QuantityOfOrderItem2=2)
Session(TotalOrderItem= 2)
Source of Ordering.asp
update orderline table
RsOrderln.Source = "Orderln"
RsOrderln.Open
for i = 1 to Session(“TotalOrderItem")
RsOrderln.AddNew
RsOrderln("Order_Number") = newOrderNumber
RsOrderln("Product_Code") =
Session("ProductCodeOfOrderItem" & i)
RsOrderln("Quantity") =
Session("QuantityOfOrderItem" & i)
next
RsOrderln.Update
Order Confirm & Display
Source code of Ordering.asp
display order information
Display Order Information:
Customer Information and Date
<p>Order#: <font size=3><b><%=newOrderNumber%></b></font></p>
<p>Customer Name: <font size=3><b>
<%=Request.Form("customerName")%></b></font></p>
<p>Date: <font size=3><b><%=Date()%></b></font></td></p>
In Assignment 5 you need to display recipient name, address, as
well as credit card information
<% for i = 1 to Session("TotalOrderItem")%>
<%= Session(“ProductCodeOfOrderItem" & i) %>
<%SQL = "SELECT * FROM Products Where (Product_Code='" &
Session(“ProductCodeOfOrderItem" & i) & "')"
Set RsOrders = Conn.Execute(SQL)%>
<%=RsOrders("Product_Name")%>
$<%=RsOrders("Unit_Price")%></td>
Retrieve order line
data and calculate
amount and total for
invoice
<%= Session(“QuantityOfOrderItem" & i)%>
$<%=RsOrders("Unit_Price")*Session(“QuantityOfOrderItem" & i)%>
<%totalAmount = totalAmount +
RsOrders("Unit_Price")*Session(“QuantityOfOrderItem" & i)%>
<%RsOrders.Close%>
<%next%>
Total Amount: <%=totalAmount%>
User Registration
Source Code of Registration.htm
<input type="Password" name="Password" size="10"
maxlength="32">
<input type="Password" name="PassConfirm" size="10"
maxlength="32">
<input name=formChecking type="button" value="Submit“
onClick="myform(this.form)">
Source Code of Registration.htm
password confirm using java script
<!–
function myform(theForm) {
if(theForm.PassConfirm.value.length <= 0) {
alert("Sorry, you should not leave your PASSWORD
CONFIRMATION
empty!")
return false}
if(theForm.Password.value != theForm.PassConfirm.value) {
alert("Sorry, you typed different passwords!")
return false}
theForm.submit()}
-->
Processing Registration
Flowchart of Ordering.asp
Create a database connection
Create and Execute a SQL
Check if the customer name already used
N
Update Customers Table
Y
Hyperlink to
Homepage.html
The Customer already selected some products
Y
Place the Order
Close Database Connections
Hyperlink to
Registration.htm
N
Source Code of Registration.asp
Include the Definition of ADO Constants:
<!-- #Include file="ADOVBS.INC" -->
Create ODBC Connection:
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "DSN=ODBCzhangj4;uid=zhangj4;pwd=99xxxxx"
Source Code of Registration.asp
Check if the same name has been registered:
Set RsCustomers = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM Customers WHERE (Customer_Name = '" _
& Request.Form("CustomerName") & " ') ORDER BY
Customer_Name"
Set RsCustomers=Conn.execute(SQL)%>
Suppose we want check if there is already a user with user name
David, we want make a query like this:
SELECT * FROM Customers WHERE (Customer_Name = 'David')
ORDER BY Customer_Name
Source Code of Registration.asp
If Not:
If RsCustomers.eof then
Save the Customer Information
Otherwise:
<a href="registration.htm">re-sign up</a>
Source Code of Registration.asp
update customer table
RsCustomers.ActiveConnection = Conn
RsCustomers.Source = "Customers"
Set new values
RsCustomers.Open
RsCustomers.AddNew
RsCustomers("Customer_Name") = Request.Form("CustomerName")
RsCustomers("Password") = Request.Form("Password")
RsCustomers("Address") = Request.Form("Address")
RsCustomers("Phone_Number") = Request.Form("Phone")
RsCustomers.Update
Source Code of Registration.asp
<% if session(“TotalOrderItem") <> "" then%>
<form action="ordering.asp" method="post">
<input type = hidden name="CustomerName" value =
<%=Request.Form("CustomerName")%>>
<input type = hidden name="CustomerPassword" value =
<%=Request.Form("Password")%>>
<input type="submit" value="Place Order" >
</form>
<%else%>
<a href = "homepage.html"></a>
<%end if%>
Passing CustomerName and
CustomerPassword to
ordering.asp through hidden
input
Practice using Tutorial 2
Follow the instruction of tutorial 2.doc
Download Tutorial_asp.zip
Unzip the files and put it in your sub directory
Tutorial_asp in your Q drive
Run the application from your web site
http://facbusad1.mcmaster.ca/users/ap1/yourusername/Tutorial_asp/homepage.html
You may use your database in SQL server or upload
Abc_Demo.mdb to your SQL server
Change the ODBC DSN, uid, and pwd in your ASP
files and upload to server
Run the application again
Tips for Assignment 4
Make sure tutorial 2 works on your Q drive
Make sure you understand the code of
tutorial 2 before doing your assignment
The task of your assignment 4 is similar to
the tutorial. Only thing you need to do is
make some modifications. Keep the structure
of the asp pages unchanged.
Tips for Assignment 4
First make the search product work
Change the connections to your database by modify
ODBC DSN, uid, pwd
Put flower pictures in Pictures folder using
product_code.jpg for corresponding flower products
Test if search for product name and price range
works for your flowers in products table
Keep the logic for order and invoice
Keep the logic for user registration
Check if the entire web site works
Tips for Assignment 5
Add searching flower by Occasion
Add data entry for recipient name and
address at the point of “Buy it now”
Modify updating Orders with additional
fields of recipient name and address as
well as credit card information
Modify ordering.asp to display order with
recipient name and address as well as
credit card information
Technical tips
Make sure login MAC VPN if you work at
home.
In VB, each line is one statement. Use under
score if the VB program statement is continue
to the next line.
When reference your table in SQL, you need
not to put your database name before it. But
if you want to do it, use “.” not “_”. E.g.
The wrong code is: serenko_tablename
The correct one is: serenko.tablename
Technical tips
To debug the asp program, display the value
of the variable by inserting <%=variable
name%> such as
<%=mysql%> to see if the SQL is assembled
properly.
Most programming problems are caused by
misspelling of variable names and the
mismatch of reference variable names, e.g.
you changed a name in one asp page but did
not change it in another related page.
Print out the code and highlight the changes.
Do careful code checking will save you a lot
of time.