Walkthrough: Editing and Inserting Data in Web Pages with the DetailsView Web Server Control

Microsoft Visual Web Developer allows you to create data entry forms for updating records and inserting new ones into a database with only a few lines of code. By using a combination of a data source control that encapsulates data access and DetailsView and GridView controls that display records in an editable format, you can create a data entry page that allows users to edit existing records or insert new ones, all without any code.

During this walkthrough, you will learn how to:

• Create a data entry page.

• Configure a data source control with information required to make database updates and inserts.

• Use the DetailsView control to view individual records, change them, and insert new ones.

• Use a GridView control to enable database modification from the data entry page.

Prerequisites

In order to complete this walkthrough, you will need:

• Visual Web Developer.

• Microsoft Data Access Components (MDAC) version 2.7 or later.

  If you are using the Microsoft Windows XP or Windows Server 2003 operating systems, you already have MDAC 2.7. However, if you are using the Microsoft Windows 2000 Server operating system, you might need to upgrade the MDAC already installed on your computer. To download the current MDAC version, see the Data Access and Storage Developer Center.

• Access to the SQL Server Northwind database. For information about downloading and installing the SQL Server sample Northwind database, see Installing Sample Databases on the Microsoft SQL Server Web site.

  Note

  If you need information about how to log on to the computer running SQL Server, contact the server administrator.

• Permission to modify data in the sample Northwind database.

Creating the Web Site and Page

Create a new Web site and page by following these steps.

To create a new file system Web site

1. Open Visual Web Developer.

2. On the File menu, click New, and then click Web Site. If you are using Visual Web Developer Express, on the File menu, click NewWeb Site.

   The New Web Site dialog box appears.

3. Under Visual Studio installed templates, click ASP.NET Web Site.

4. In the Location box, select File System, and enter the name of the folder where you want to keep the pages of your Web site.

   For example, type the folder name C:\WebSites\EditData.

5. In the Language list, click the programming language you prefer to work in.

   The programming language you choose will be the default for your Web site, but you can set the programming languages for each page individually.

6. Click OK.

   Visual Web Developer creates the folder and a new page named Default.aspx.

Connecting to SQL Server

The next step is to establish a connection to the SQL Server database.

To create a connection to a SQL Server database

1. In Server Explorer, right-click Data Connections, and then click Add Connection. If you are using Visual Web Developer Express, use Database Explorer.

   The Add Connection dialog box appears.

   • If the Data source list does not display Microsoft SQL Server (SqlClient), click Change, and in the Change Data Source dialog box, select Microsoft SQL Server.

   • If the Choose Data Source page appears, in the Data source list, select the type of data source you will use. For this walkthrough, the data source type is Microsoft SQL Server. In the Data provider list, click .NET Framework Data Provider for SQL Server, and the click Continue.

   Note

   If the Server Explorer tab is not visible in Visual Web Developer, in the View menu, click Server Explorer. If the Database Explorer tab is not visible in the Express edition, in the View menu, click Database Explorer.

2. In the Add Connection box, enter your server name in the Server Name box.

3. For the Log on to the server section, select the option that is appropriate to access the running the SQL Server database (integrated security or specific ID and password) and, if required, enter a user name and password. Select the Save my Password check box if you entered a password.

4. Under Select or enter a database name, enter Northwind.

5. Click Test Connection, and when you are sure that it works, click OK.

   Your new connection has been created under Data Connections in Server Explorer.

Creating the Data Entry Page

In this section, you will create a data entry page and configure a DetailsView control so that you can view employee data that is stored in the Employees table of the Northwind database. To handle the data access for the page, you will configure a SQL data source control.

To create a data entry page and configure a DetailsView control

1. On the Website menu, click Add New Item.

   The Add New Item dialog box appears.

2. Under Visual Studio installedtemplates, click Web Form, and then in the Name box, type EditEmployees.aspx.

3. Click Add.

4. Open the EditEmployees.aspx page.

5. Switch to Design view.

6. Type Edit Employees, select the text, and then format the text as a heading.

7. In the Toolbox, from the Datagroup, drag a DetailsView control onto the page.

8. Right-click the DetailsView control, click Properties, and then set AllowPaging to true.

   This will allow you to page through individual employee entries when they are displayed.

The next step is to create and configure a data source control that can be used to query the database. There are a number of ways to create a data source control, including dragging data elements from Server Explorer or Database Explorer onto the page. In this walkthrough, you will start with the DetailsView control and configure the data source control from there.

To configure a data source control

1. Right-click the DetailsView control and click Show Smart Tag.

2. On the DetailsView Tasks menu, in the Choose Data Source box, click <New data source>.

   The Data Source Configuration Wizard dialog box appears.

3. Under Select a data source type, click Database.

4. Leave the default name of SqlDataSource1, and then click OK.

   The Configure Data Source wizard displays the Choose a connection page.

5. In the Which data connection should your application use to connect to the database? box, enter the connection that you created in "To create a connection to SQL Server," and then click Next.

   The wizard displays a page where you can choose to store the connection string in a configuration file. Storing the connection string in the configuration file has two advantages:

   • It is more secure than storing it in the page.

   • You can use the same connection string in multiple pages.

6. Select the Yes, save this connection as check box, and then click Next.

   The wizard displays a page where you can specify what data you want to retrieve from the database.

7. On the Configure the Select Statement page, select Specify columns from a table or view, and then in the Name box, click Employees.

8. Under Columns, select the EmployeeID, LastName, FirstName, and HireDate check boxes, and then click Next.

9. Click Test Query to preview the data, and then click Finish.

You can now test the employee records display page.

To test displaying the employee records

1. Press CTRL+F5 to run the page.

   The first employee record is displayed in the DetailsView control.

2. Click the page number links to see additional employee records.

3. Close the browser.

Allowing Editing in a GridView Control

At this point, you can view employee records, but you cannot edit them. In this section, you will add a GridView control and configure it so that you can edit individual records.

To support editing, you must configure the data source control you created earlier (SqlDataSource1) with SQL statements that perform updates.

To add a GridView control to allow editing

1. In the Toolbox, from the Data group, drag a GridView control onto the page.

2. Right-click the GridView control, click Show Smart Tag, and then on the GridView Tasks menu, in the Choose Data Source box, click SqlDataSource1.

3. On the GridView Tasks menu, click Configure Data Source.

4. Click Next to advance to the Configure the Select Statement page of the wizard.

5. On the Configure the Select Statement page, click Advanced, select the Generate INSERT, UPDATE, and DELETE statements check box, and then click OK.

   This generates Insert, Update, and Delete statements for the SqlDataSource1 control based on the Select statement that you configured earlier.

   Note

   Alternatively, you could manually create the statements by selecting Specify a custom SQL statement or stored procedure and entering SQL queries.

6. Click Next, and then click Finish.

   The SqlDataSource control is now configured with additional SQL statements.

   Note

   You can examine the statements generated by the wizard by selecting the SqlDataSource control and viewing the DeleteQuery, InsertQuery, and UpdateQuery properties. You can also view the updated control properties by switching to Source view and examining the markup of the control.

7. On the GridView Tasks menu, select the Enable Paging and Enable Editing check boxes.

   Security Note:
   User input in an ASP.NET Web page can include potentially malicious client script. By default, ASP.NET Web pages validate user input to make sure input does not include script or HTML elements. As long as this validation is enabled, you do not need to explicitly check for script or HTML elements in user input. For more information, see Script Exploits Overview.

You can now test the editing of employee records.

To test the editing in the GridView control

1. Press CTRL+F5 to run the page.

   The GridView control is displayed with the data in text boxes.

2. Choose a row in the GridView control and click Edit.

3. Make a change to the record, and then click Update.

   The updated data now appears in both the GridView control and the DetailsView control.

4. Close the browser.

Allowing Editing, Deleting, and Inserting Using a DetailsView Control

The GridView control allows you to edit records, but it does not allow you to insert data. In this section, you will modify the DetailsView control so that you can see records individually, as well as delete, insert, and update records.

To use a DetailsView control to allow deleting, inserting, and updating

1. Right-click the DetailsView control and then click Show Smart Tag.

2. On the DetailsView Tasks menu, select the Enable Inserting, Enable Editing, and Enable Deleting check boxes.

   When you used the DetailsView control earlier in the walkthrough, the options for enabling editing, inserting, and deleting were not available. The reason is that the SqlDataSource1 control that the DetailsView control is bound to did not have the necessary SQL statements. Now that you have configured the data source control to include update statements, the update options are available on the DetailsView control.

You can now test deleting, inserting, and updating in the DetailsView control.

To test the updating, inserting, and deleting in the DetailsView control

1. Press CTRL+F5 to run the page.

   The DetailsView control displays an employee record.

2. In the DetailsView control, click Edit.

   The DetailsView control now displays the data in text boxes.

3. Make a change to the record, and then click Update.

   The updated record is displayed in the control.

4. In the DetailsView control, click New.

   The control now displays blank text boxes for each column.

5. Enter values for each column.

   The Employees table has an auto-increment key column, so the value for EmployeeID is assigned automatically when you save the record.

6. When you are finished, click Insert.

   The new record is added as the last record.

   Note

   Inserting a new record in the Employees table in this walkthrough will not generate any errors. However, when you work with production data, the tables might have constraints (such as a foreign key constraint) that you must be aware of when configuring the DetailsView control.

7. Use paging in the DetailsView control to navigate to the last record, and then click Delete.

   The new record is removed.

   Note

   As with inserting data, you must always be aware of any constraints that apply to the data row when configuring a DetailsView control to allow deletion.

Next Steps

This walkthrough has illustrated the basics of creating a Web page that allows users to modify data records. You might want to explore additional data capabilities of ASP.NET Web pages. For example, you might want to do the following:

• Work with records in related tables. For details, see Walkthrough: Creating Master/Detail Web Pages in Visual Studio.

• Use a middle-tier component for data access. For details, see Walkthrough: Data Binding to a Custom Business Object.

• Use other ways to configure data access in a Web page. For details, see Walkthrough: Basic Data Access in Web Pages.

See Also

Tasks

Walkthrough: Creating a Web Page to Display Access Database Data

Concepts

Data Source Controls Overview

Binding to Data Using a Data Source Control