Assignment 4:  Nov. 1, 2007

Total: 7 points.

      You need to download the coffee.mdb database

The physical data model of the database is illustrated in the following: 

Sample data in the table are: 

===============

Build a set of ASP.NET programs that can insert new coffee records into the COFFEE table and select coffees by price range (minimum price and maximum price).

A sample SQL insert statement:

insert into COFFEES values('Kona', 51, 10.05, 0)

   or

insert  into  COFFEES  (COFNAME, SUPID, PRICE, SALES)  values ('Kona', 51, 10.05)

---

 

You can check out two run-time versions of my solutions at Brinkster.com to get a better feeling about the requirements for this assignment!

1.  Preliminary version for getting 4 points

2. Complete version for another 3 points

Source code at DataAware DropDownList may help you with dealing with the data aware dropdownlist box in the Coffee update operation where the Supplier Name is displayed on first request and it should be the current Supplier Name.  

**  You need to store the Coffee.mdb to the   App_Data   subdirectory locally and on the server due to security related issue.   The connection string should be something similar to the following:

Dim Conn As OleDbConnection = _  
               New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;"  &  _
               "Data Source="  &   Server.MapPath("App_Data/Coffee.mdb") )

---

The following screen snapshots are based on the Preliminary version of the solution. 

• Preliminary version of Insert Coffee (2 points) 
• Add a new coffee with Supplier field as a data-driven drop down box (1 point)
• Update Coffee's price (2 points)
• Search coffee by price range (2 points)

 

1.  Coffeemenu.aspx

User can click on a hypertext link (use relative URLs) of an data maintenance function to invoke the corresponding program. 

 

2. Coffeeinsert.aspx (Add a new coffee).   See InsertProduct.aspx code below in single file format based on the code-behind examples from our hands-out.  Use it as a template for this program. 

Allow the user enters a new coffee record or go back to the Coffeemenu.aspx program.

The user can fill out the data entry form and click the Submit button to insert a new coffee.  If the insert is successful, you should display a message on the screen to inform the user.   Year-To-Date sales column's data type is an long integer.  It tracks the YTD total number of units sold.

If the insert fails, an error message should be displayed on the screen.  Keep all the data been entered as they are.

If the user hit the reset button.  The system should reset all the textboxes to blank and remove any error message / confirmation message on the screen.

 

3.  Coffeesearch.aspx (Select coffees by price)

Users are allowed to enter a minimum price and a maximum price to search for all the coffees that fall in this price range.   Do some data validation if possible to ensure proper data are entered.  For example, Maximum Price is greater than or equal to Minimum Price.

The response page after you entered the criteria and click the Search button is listed in the following. 

4. coffeeUpdate.aspx (You need to implement this Update function)

Allow the user to enter a coffee name, the program can then retrieve the coffee record to modify all the columns except the  coffee name itself!   The modified record can be saved back to the database.  If the user enter a coffee name that does not exit, an error message should be displayed to indicate the error. 

 

For the requirements of the complete solution , please run the sample solution to get a feel of it. 

1.  Update function need to be implemented

2.  The Supplier ID in Insert and Update functions should be implemented using an Drop Down List with data populated from the Coffee.mdb

3.  Any additional error checking.