[ Home ] [ Up ] [ Syllabus ] [ Resources ] [ Tools ] [ Announcement ] [ Code ]

Assignment 2

6 points

Due Date: Sept. 27, 2006

  1. Read the instruction very carefully.
  2. Name all three aspx pages according to the requirements specified below.
  3. You need to submit your project to an hosting web site such as SOMEE to host your assignments.   
  4. Build a link from to this assignment from your default.aspx page

*****************************************************************************

 We are assuming that you put your Northwind.mdb database  in the same folder under your main folder 

Run it from my sample Web site.

 

********************************************************************

Purpose of Assignment 2

Learn how to develop ASP.NET Web application that integrates with database using ADO.NET.  In this assignment you are required to use DataReader and primitive HTML code to format dynamically generated HTML code.  Users can navigate from customer list to a list of orders from a customer, and then drill down to view the information of a specific order.  No insert, update, or delete are required.

Detailed Requirements

1. Use the NorthWind.mdb  database.      

 

2. Develop a web application that consists of the following pages using DataReader object (do not use DataGrid in the assignment)

 You can upload the NorthWind.mdb Access database and the three pages in the root directory of your account's folder.

 You have to always set your start-up page to customer.aspx when you try to test your assignments.  There are totally three pages to be written. 

File name Function and Interface design
customer.aspx

(2 points)
  1. Display a list of all the customers in tabular format.
  2. Information displayed include CustomerID, CompanyName, City, PostalCode, and Phone.
  3. The CompanyName column is a hypertext link linking to the order.aspx page that displays all the orders placed by the selected customer.   Customer ID and CompanyName are passed to the next program via QueryString (i.e., URL variables). 
  4. Sample screen snapshot:
order.aspx

(2 points)
  1. Display a list of all the order from a customer in tabular format.
  2. Information displayed include OrderID, CustomerID, OrderDate, RequiredDate, ShippedDate.
  3. The OrderID column is a hypertext link linking to the orderdetail.aspx page that displays all the order items of the selected order.
  4. You need to use a parametered query for this program in which CustomerID is the parameter and should be determined dynamically by running the first program (customer.aspx) and choose a customer's hypertext link to invoke the order.aspx.  Use QueryString to pass the customerID and CompanyName to order.aspx.  Remember the CustomerID column is a String data type.
  5. You need to create a parameterized SQL that looks like

"SELECT OrderID, ... FROM Orders WHERE CustomerID = '" & _     
        Request.QueryString("cid") & "'"

  1. Sample screen snapshot after you select the first customer from previous page:

orderdetail.aspx

(2 points)
  1. Display a complete information of a specific order including the following information:

      2. OrderID, CustomerID, and CompanyName

       There are two approaches to do this:

    1. You need to pass cid and cname from program 1 (Customer.apx) to program 2 (Order.apx) as part of the QueryString (URL variables).  But if you are not passing these URL variables again from program 2 (Order.apx)  to program 3 (OrderDetail.aspx), you cannot use them in program 3 (OrderDetail.aspx). 
      Solution: 
      1. You can either formulate the hypertext link in program 2 with two additional URL variables cid and cname and passing the customer ID and customer name you received from Program 1 to Program 3.  An example of QueryString formed when OrderDetails.aspx is invoked in a sample program is: http://ebiz.gmu.edu/mis412/assignments/orderdetail.aspx?cid=AROUT&cname=Around+the+Horn&orderid=10453

    2. Use orderid to go back to the database to select customer id and customer name with some JOIN operation such as 
      SELECT Orders.CustomerID, Customers.CompanyName
      FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID Where OrderID = Request.QueryString(“orderid”)

      3.  


       

  2. A list of detailed order items displayed in tabular format. Each item should include the following information:

[Order Details].ProductID, Products.ProductName, [Order Details].UnitPrice, [Order Details].Quantity

  1. UnitPrice should be displayed in currency format and aligned to the right. 
  2. Also display the total amount for this order on this page.
  3. Assuming that no discount will be given to customers. 
  4. A sample screen snapshot after you have selected the first order from the previous screen. 

 

Hint for orderdetail.aspx:

SQL statement:

SELECT [Order Details].ProductID, Products.ProductName, [Order Details].Quantity, [Order Details].UnitPrice FROM [Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductID WHERE [Order Details].OrderID = ????

Since you use join in the SQL, we need to use zero-based column ordinal to access Quantity and UnitPrice.

Also since you need the use Quantity and UnitPrice to calculate the total amount of an order, you should be proper GetXXX method of the DataReader to retrieve the data.   For Quantity uses myReader.GetInt16(2) method; for UnitPrice uses myDataReader.GetDecimal( 3) method.

 

You need to add a criterion to select only order item for a particular ordered. The OrderID's value is passed to this page (i.e., ???? indicated in the SQL statement) via QueryString from the order.aspx program.  For Example, Request.QueryString("orderID")

 

Accessing the value of a column from the current record in the record set, i.e., dr.  You can use either dr(i) where i is an ordinal reference (0, 1, 2, ...) or dr("ColumnName").   It will return an object and when it is used for output, it is implicitly converted to string data type.  If you want to use Type Safe Reference, you can use dr.GetXXX(i) where i is an ordinal reference (0, 1, 2, ...) and XXX is the appropriate data type of the column such as dr.GetDecimal(2).   Type Safe Reference is more convenient when you want to format the retrieved data or to use it in a calculation.  You cannot use column names with Type Safe Reference. 

By Zero-based Column

Type Safe or Ordinal Reference

By Column Name String Reference

Returned Value

dr.GetInt32(0) or dr(0)

dr("Num")

200

dr.GetString(1) or dr(1)

dr("Name")

HTML

dr.GetDecimal(2) or dr(2)

dr("Price")

250.00