iLab 5 of 8: Maintaining the Food Information Guide Database
Submit your assignment to the Dropbox located on the silver tab at the top of this page.
(See Syllabus/”Due Dates for Assignments & Exams” for due dates.)

i L A B O V E R V I E W
Scenario/Summary
You have been asked to add a form to the Healthy Eating Web site that will allow an administrator to maintain the database used by the Food Information Guide feature.
You will create an ASP.NET Web form to allow the administrator to find, view, edit, and delete food records, as well as add new food records to the Microsoft Access database.
Although you will create the Web form for this lab in a folder named “Secure”, you will not actually restrict access to the folder to administrators only at this time. That will be performed in a future iLab.
Deliverables
1. Maintain Food Information Web form (Secure/Admin.aspx) added to the Healthy Eating Web site on the Web server.
2. Word document submitted to Dropbox with screenshots of Maintain Food Information Web form displayed in browser (Your Name BIS450 Lab5 Screenshots.docx). Screenshots must show the complete browser window including the URL of the page.
• Screen shot showing display of an existing record.
• Screen shot showing adding a new record.
• Screen shot of new record after a successful add.
• Screen shot showing editing a record.
• Screen shot of updated record after a successful edit.

Grading Rubric

Criteria Points %
Step 2: Create Maintain Food Information Web Form (Secure/Admin.aspx)
• Secure folder created on the web server
• Admin.aspx created in Secure folder
• Has standard page header, navigation menu, and page footer
• Form title “Maintain Food Information” in bold
• Input controls: Textbox for food
• Button for Find Food Info
• AccessDataSource control
• DetailsView control 10 40%
Step 3: Configure AccessDataSource Control
• Connected to HealthyEating.mdb in database folder
• Retrieves NDB_No, Food, Amount, and Calories columns from Foods table
• Parameter query with LIKE operator bound to txtFood textbox
• INSERT, UPDATE and DELETE statements 5 20%
Step 4: Configure DetailsView Control
• Bound to AccessDataSource control on form
• Displays columns NDB_No, Food, Amount, Calories
• Classic AutoFormat applied 5 20%
Step 5: Test, Capture Screenshots, and Submit (Student Name BIS450 Lab5 Screenshots.docx)
• Word file submitted to dropbox with the following screen shots:
o Fourth “beets” record displayed in DetailsView
o In process of adding a record
o Showing new record after successful add
o In process of editing a record
o Showing updated record after a successful edit 5 20%
Total 25 100%

i L A B S T E P S
STEP 1: Open Web Site on the DeVry Web Server

1. Launch Microsoft Visual Studio 2010.
You must use Visual Studio 2010 in the Citrix environment.
2. Pull down the File menu and select Open, then select Web Site. In the Open Web site dialog, select FTP Site in the left column. The connection information you used in the previous lab should be displayed:
• Server: bisweb.devry.edu
• Port: 21
• Directory: coursefolder/yourname, where coursefolder = folder on the web server for your course (provided by your professor), and yourname = your first initial and last name, (e.g. jsmith for student John Smith).
• Passive Mode and Anonymous Login: Both unchecked.
• Username: acad\Dnnnnnnnn, where Dnnnnnnnn = your DSI number.
• Password: Enter the same password as you use for Citrix iLab (must be re-entered each time).
Click Open.
STEP 2: Create Maintain Food Information Web Form

1. In the Solution Explorer window, click on the Web site root (ftp://bisweb.devry.edu/coursefolder/yourname) to select it; then pull down the Website menu and click New Folder. Enter Secure as the name of the new folder.
2. In the Solution Explorer window, click on the Secure folder to select it; then add a new Web form named Admin.aspx to the folder.
TIP: Putting this page in a separate folder on the site will make it easier to restrict its use to administrators only, later.
3. Set the Title property of the Document to Maintain Food Information.
4. Attach StyleSheet.css to the Web form. (Remember that since the page you are working on is inside the Secure folder, you will have to navigate up one level in the Select Style Sheet dialog to locate StyleSheet.css.)
5. Because this form will use several elements that were also used in the Food Information Guide form, it will be more efficient to copy the body of that form as a starting point, rather than copy from Default.htm as we have done in some earlier iLabs. Open FoodInfo.aspx and copy the contents of its element (in HTML, everything between the

tags, but not the

tags themselves). Paste these contents inside the div in Admin.aspx (in HTML, in between the

tags).
6. Change the “Food Information Guide” heading to read Maintain Food Information.
7. Delete the GridView control.
8. Drag a DetailsView control from the Data section of the Toolbox and drop it onto the form where the GridView control was. Select and drag the right edge of the control to stretch it across the form.

Your Web form should now look like the following in Design View:

9. Save the Web form.
STEP 3: Configure AccessDataSource Control

1. Click the AccessDataSource1 control and, if necessary, click the > button in the upper right corner to display the control’s smart tag. Click the Configure Data Source task in the smart tag.

2. In the first screen of the Configure Data Source wizard, add ../ in front of the Microsoft Access data file, so that it reads in full ../database/HealthyEating.mdb (this is the correct relative path to the database file from inside the Secure folder). Then click Next >.

TIP: As in last week’s iLab, typing in the path to the database file works more reliably than browsing to the database, given the way your personal site on the Web server is configured.
3. In the second screen of the Configure Data Source wizard, select the Specify columns from a table or view option. In the list of columns, place checks beside NDB_No, Food, Amount, and Calories. Click the WHERE… button.

TIP: In the previous lab, NDB_No was not selected because it was not meaningful to display it to the user. It is selected in this week’s lab because you will be allowing the user to update as well as view the records in the table. When a data source will be used for updates, the primary key of the table must always be selected.
4. In the Add WHERE Clause dialog, select Food from the Column drop-down list. Select LIKE from the Operator drop-down list. Select Control from the Source drop-down list. Under Parameter Properties, set the Control ID to txtFood. Click the Add button and review the SQL WHERE clause expression that the wizard has built. Click OK.

5. Back on the Configure the Select Statement screen of the Configure Data Source wizard, click the Advanced button. In the Advanced SQL Generation Options dialog, check the option Generate INSERT, UPDATE, and DELETE statements. (These extra SQL statements are needed because you want to allow the administrator to add, edit, and delete records through this data source.) Click OK.

TIP: Although it is very convenient to have Visual Studio generate the INSERT, UPDATE, and DELETE statements for you, unfortunately it does not always do so correctly! So, you should always check the generated statements to be sure they are correct. In this lab, you will be checking and correcting these statements in a later step. This is why, even with “smart” tools like Visual Studio, it is still important to know the basics of SQL.
6. Click Next > to move to the next screen of the Configure Data Source wizard.
7. On the Test Query screen of the Configure Data Source wizard, click Test Query.

In the Parameter Values Editor, enter apple in the Value column and click OK.

You should see a list of foods containing the word “apple”, with their NDB_Nos, amounts and calories. Click Finish.

8. Now you need to check for errors in the INSERT, UPDATE, and DELETE SQL statements that you asked the configuration wizard to generate for you earlier. Click the AccessDataSource control to make sure it is selected. In the Properties window, click the InsertQuery property and then click the button with three dots […] that appears. The INSERT command should look like this:

This is correct, so click Cancel without making any changes.
9. To check the UPDATE command, click the UpdateQuery property and then the […] button. The UPDATE command that the wizard generated may look like this:

Unfortunately, this is NOT quite correct; there is some extra “junk” code in the WHERE clause. Carefully select the part of the statement that is highlighted in the above image and delete it. Take care to delete only one of the two closing right parentheses. After you delete the “junk” your UPDATE command should look like:

Click the OK button to save your changes to the UPDATE command.
10. To check the DELETE command, click the DeleteQuery property and then the […] button. The original DELETE command looks like:

Delete the highlighted “junk” portion, again taking care to delete only one of the two closing right parentheses. The resulting command should be:

Click the OK button to save your changes to the DELETE command.
11. Save the Web form.
STEP 4: Configure DetailsView Control

1. Click the DetailsView control to select it. If necessary, click the > at the upper right to display the control’s smart tag.
2. Beside Choose Data Source, select AccessDataSource1. Check the boxes for Enable Paging, Enable Inserting, Enable Editing, and Enable Deleting. Click Auto Format.

3. In the AutoFormat dialog, choose the Classic scheme and click OK.

Your Web form should now look like this in Design View:

4. Save the Web form.
STEP 5: Test, Capture Screenshot, and Submit

1. To test the Food Information Guide form, in the Solution Explorer window, right-click on Admin.aspx and select View in Browser. Initially your form should look like this:

2. Enter beets in the textbox and click the Find Food Info button. You should see the following display:

3. The numbers at the bottom of the DetailsView control indicate there are 9 records in the database containing the word “beets.” Unlike the GridView, which showed all matching records at once, the DetailsView shows only one at a time. Click on some of the numbers to display different records.
4. Capture a screen shot of your Web form with the fourth record containing “beets” displayed. Be sure to include the full browser window with the URL of the page. Paste this into a Word document.
5. To test entering a new record, click the New button. A blank form should be displayed. For the NDB_No, enter 98765. For the Food value, enter YourName and a food item. (For example, if your name is Jane and you like cupcakes, enter Jane’s Cupcakes.) Enter an amount for consumption and a calorie count for the amount. Capture a screen shot showing your entry and paste it into the same Word document. Then click Insert.

6. Enter the food you just added (YourName’s YourFood, e.g. Jane’s Cupcakes) in the Find Food textbox and click Find Food Info. The food record you added should be displayed. Capture a screen shot of this and paste it into the same Word document.
7. To test editing a record, click the Edit button. The record should be displayed for editing. Notice that NDB_No, the primary key, cannot be changed. Change the Amount to 1 oz and the Calories to 456. Capture a screen shot and paste it into the same Word document. Click Update.

8. Capture a screen shot showing your record displayed after completing the update, and paste it into the same Word document.
9. To test deleting a record, click the Delete button. The record should disappear from the screen. Try clicking the Find Food Info button to search for your food again. Nothing should be displayed, indicating the record was deleted.
10. Save your Word document containing 5 screenshots as Your Name BIS450 Lab5 Screenshots.docx. (Make sure that the browser URL is visible in all your screenshot.) Submit this file to the Week 5 iLab Dropbox.