Tutorial 2 ABC Web site

1. Tutorial 2 ABC Web site 1

2. Objective • Learning web applications design • Conducting assumed business logic online • Connecting the Database with the web pages

3. 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/yuanyuf/Tutorial_asp/homepage.html

4. Tutorial_2.zip • 8 Files • Homepage.html, Search.asp, Results.asp, Check.asp, Ordering.asp, Registration.htm, Registration.asp, Adovbs.inc • 1 subfolder: pictures • 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/your-username/Tutorial_asp/homepage.html

5. 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

7. Define your site in Dreamweaver

9. Use a server technology ASP VBScript

10. 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

11. 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.

12. Test URL: http://facbusad1.mcmaster.ca/users/ap1/ yourname/Tutorial_asp/homepage.html

13. ABC Database Please do not use space in the field names

14. Business Logic Select Searching Method Homepage.html Specify Search Criteria Search.asp Product Display & Ordering Results.asp Customer Registration Registration.htm Processing Registration Registration.asp Save order items & Customer Logon Check.asp Order Confirm & Display Ordering.asp

15. Select Searching Method (Homepage.html)

16. Homepage.htmlSource 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

17. Search.aspSpecify Product Name

18. Specify Price Range

19. <% 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> </select> <input type=text name="PriceRange" value="$" size=10 maxlength=10> <input type=submit value="Go!"> <% end select %> Use case to display Different search form

20. Product Display & Ordering

21. Create a database connection Request.Form(“SearchingMethod”) ? = “ByProductName” = “ByPriceRange” Create a SQL with Request.Form(“ProductName”) Create a SQL with Request.Form(“PriceRange”) Request.Form(“Comparison”) Execute the SQL Flowchart of Results.asp(1)

22. Flowchart of Results.asp(2) Are there any products meet the criteria ? N Y Hyperlink to “Homepage.html” • Show the Products • Provide Checkbox and Input Box for Customer to Select and Specify the Quantity Click “Buy Now” Close Database Connection

23. 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.

24. Select * from Products where (Unit_Price>100) order by Product_code Select * from Products where (Product_Name like ‘%vacuum%’) Source Code of Results.asp In VB, using underscore to continue a line. <% select case Request.form(“SearchingMethod") %> <% case “ByPriceRange” SQL = "SELECT * FROM Products WHERE (Unit_Price"_ & Request.Form("Comparison") & Request.form("PriceRange")_ &") Order by Product_Code” case “ByProductName” SQL = "SELECT * FROM Products WHERE (Product_Name Like '%"_ & Request.form("ProductName") & "%') Order by Product_Code” end select %> &is used to combine strings

25. Product Display & Ordering

26. <%RecordNo = 1%> • <% do while Not Rec.EOF %> • <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 • RecordNo = RecordNo + 1%> • <% loop%> • <input type="hidden" name="NumberOfResults" • value="<%=RecordNo-1%>"> • <input type="submit" name="buyButton" value="Buy Now"> Checkbox name as Product1, Product2,… If checked, the value is product_code QuantityOfProduct1,… Calculate and save number of results

27. Results.aspForm 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

28. 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”

29. Source Code of Check.aspSave 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) • Session("QuantityOfOrderItem" & ItemNo)= • Request.Form("QuantityOfProduct" & i) • Session("TotalOrderItem")= ItemNo • ItemNo = ItemNo + 1 • end if • next Save multiple Order line data to session

30. Save the Results to Session Variables Session(ProductCodeOfOrderItem1=A021) Session(QuantityOfOrderItem1=1) Session(ProductCodeOfOrderItem2=C387)Session(QuantityOfOrderItem2=2)Session(TotalOrderItem= 2)

31. User Logon

32. Source Code of Check.aspinput 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>

33. Source Code of Check.aspcheck 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() } -->

34. Order Confirm & Display

35. 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 Hyperlink to Registration.htm N Y Get New Order Number Use the Recordsets defined above to Update Orders and Orderln respectively Display Order Information Close Database Connections

36. Source of Ordering.aspDatabase 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.

37. Source of Ordering.aspCreate 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")

38. Source of Ordering.aspCheck 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)

39. Source of Ordering.aspif 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%>

40. Source of Ordering.aspCheck if user has ordered some products • Save Order Information: • Check if the user already has selected some products: • if (Session(“TotalOrderItem")>0) then

41. Source of Ordering.aspgenerate 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

42. Source of Ordering.aspUpdate 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

43. 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)

44. Source of Ordering.aspupdate 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

45. Order Confirm & Display

46. Source code of Ordering.aspdisplay 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

47. <% 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> • <%= Session(“QuantityOfOrderItem" & i)%> • $<%=RsOrders("Unit_Price")*Session(“QuantityOfOrderItem" & i)%> • <%totalAmount = totalAmount + • RsOrders("Unit_Price")*Session(“QuantityOfOrderItem" & i)%> • <%RsOrders.Close%> • <%next%> • Total Amount: <%=totalAmount%> Retrieve order line data and calculate amount and total for invoice

48. User Registration

49. 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)">

50. Source Code of Registration.htmpassword 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()} • -->