Lesson 13:
Displaying and Updating Data

Introduction

In Chapter 13: Displaying and Updating Data you will learn to use server controls to access and update the data in a newly created PlanetWrox database (do NOT use the sample database for the rest of the CSIS786 assignments).

In this chapter's assignment you will be creating and updating a new PlanetWrox database on the IA.MSJC.EDU Student SQL server and publishing a new folder named Management and its contents to the student Web server. Whereas last week's task were mostly for practice, this week you will be running a SQL script to add the Genre and Review tables to your database on the Student SQL server and building three new management pages which will allow you to easily manage and make changes to your Web site's database tables

Before you begin completing all of the Try It Outs in Chapter 13 please read the Instructions below - there are some changes from the textbook instructions.

Assignment

In this assignment you will get an opportunity to use server controls to manipulate the data in a new database in which will you will build new versions of the Genre & Reviews tables you were using during last chapter's warm-up with your sample database. In order to be ready for the next chapter's assignment submission you will need to have the following tasks completed by the due date for Assignment 13 (see Syllabus):

• Read all of Chapter 13: Displaying and Updating Data in your ASP.NET text book
• Complete all of the steps below to create the Reviews and Genre tables in your Student SQL server database and populate them with sample data.
• Complete all the steps in all 7 of the Try It Outs in Chapter 13 on pages 443 - 446, 449 - 450, 453 - 456, 457 - 458, 460 - 465, 467 - 470, and 471 - 477 as described in the instructions below.
• Post URL for your site's Management folder in Canvas for Assignment 13.

Instructions

Use the following step-by-step instructions for completing this assignment. Remember to use C# when copying code snippets from your text book and adding new items to your site which use code-behind files.

IMPORTANT! Connect to the AWS VPN using your Canvas credentials before you complete the following steps.

Step 1 Using the SQL Server Management Studio (SSMS) or SQL Server Object Explorer (SSOE) in Visual Studio, login to your Student SQL server database instance on the Student SQL server by using the following credentials:

Server Name: ia.msjc.edu

Authentication: SQL Server Authentication

User Name/Login: your first name + last name

Password: your first initial + last initial + 7-digit student ID number + !

Step 2 Once you are connected to the Student SQL server, twirl open your student database to reveal its contents. Right-click on the icon for your database on the Student SQL server and choose New Query... from the context menu that appears.

Step 3 Here is a link to the sql script file you will need to complete this step (click it) - Complete Planet Wrox Database. After extracting the SQL file from the zip folder, open the SQL file in an ASCII editor like Notepad or Notepad++. Then, copy the entire contents of the SQL file and paste it into the Sql Query file you created in Step 2.

Step 4 Click on the Execute button (the green arrow) on the left end of the SQL Query window toolbar to process the SQL script text you pasted in Step 3 (keyboard shortcut: Ctrl + Shift + E). This will create two new tables, Genre and Review in your database and populate them with sample data as you did in Chapter 12 when you first created your sample Planet Wrox database on your computer.

Step 5 Modify the statement for PlanetWroxConnectionString1 in your Web.config file to the following:

Then save your Web.config file.

Step 6 Complete all steps in the first Try It Out in Chapter 13 on pages 443 - 446 to do the following:

• Create a new MasterPage containing two hypertext links and a ContentPlaceHolder to base your new management pages on.
• Create a new Management folder off the root of your site.
• Add a new Web.config file to your Management folder.
• Create two new Web forms - one named Default.aspx and one named Genres.aspx in your Management folder.
• Add a GridView control, and a SqlDataSource control to your Genres.aspx page.

Note: On page 444 in steps 6 and 7 of the Try It Out, you are instructed to double-click the PlanetWrox.mdf file - ignore this instruction. Instead, open the Server Explorer in Visual Studio using, View menu --> Other Windows, then select Server Explorer from the flyout menu that appears.

In the Server Explorer window, open your database on the ia.msjc.edu SQL server using your SQL server credentials.

The Server Explorer is where you should drag-and-drop your Genre table from into your Genres page in Design View in step 7 of the Try-It-Out instructions.

Step 7 Complete all steps in the second Try It Out in Chapter 13 on pages 449 - 450 to add a DetailsView control to your Genres.aspx page configured to insert new records into your Genre table.

Note: On page 450, Step 5, your author displays code for the DetailsView control that differs from what you will see. Here is the correct code:

Step 8 Complete all steps in the third Try It Out in Chapter 13 on pages 453 - 456 to do the following:

• Create a new Web form named Reviews.aspx
• Update the hypertext links in your Management.master masterpage
• Add and configure a DropDownList control display the genre names from your Genres table.

Step 9 Complete all steps in the fourth Try It Out in Chapter 13 on pages 457 - 458 to configure the GridView control in your Reviews.aspx page to filter the records displayed by the choice selected from the DropDownList control.

Step 10 Complete all steps in the fifth Try It Out in Chapter 13 on pages 460 - 465 to customize your GridView control's columns using the Fields editor.

Step 11 Complete all steps in the sixth Try It Out in Chapter 13 on pages 467 - 470 to create an AddEditReview.aspx page that you created a link for earlier in the Reviews page where you will be able to manage your Web site's reviews using a DetailsView control.

Step 12 Complete all steps in the seventh Try It Out in Chapter 13 on pages 471 - 477 to customize the DetailsView control of your AddEditReviews.aspx page to dynamically populate the UpdateDateTime column and to use a DropDownList control for selecting a genre when inserting a new record into the Review table.

Step 14 Publish the following files and folders to the Student Web server:

• Web.config file off the root of your Web site.
• Management.master page from the MasterPages folder off the root of your Web site.
• Management folder off the root of your Web site.
• Management folder in your App_Themes folder.
• Reviews folder of the root of your Web site.

Step 15 Validate that the pages in your Management and Reviews folders are working on the Student Web server.

Step 16 Post the URL for your Management folder's default page to Canvas for scoring by the due date listed in the Syllabus. Example: http://IA.MSJC.EDU/7-digit student ID number/Management

Instructor's Note: Everything MUST be completed before the due date listed in the syllabus for this assignment.

Please email me if you have any questions at all!

⇑Table of Contents

Lesson Summary

In this lesson you learned how to:

• Display, insert, edit, and delete data using controls such as GridView, DetailsView, and SqlDataSource
• Create a rich interface that enables a user to insert and edit data while maintaining data integrity with the ASP.NET validation controls
• Best store your connection strings in your application so they are easily updateable

Congratulations, that's it for this assignment. You can get a head start on your next lesson by reading chapter 14 in your textbook.

⇑Table of Contents