Share via


Walkthrough: Modifying Data Using the ListView Web Server Control

The ASP.NET ListView control has built-in functionality that enables users to insert, edit, or delete records without requiring programming.

This walkthrough shows you how to display and update data by using the ListView control. This walkthrough uses a SqlDataSource control to retrieve results from the data source and to manage updates. The SqlDataSource control acts as the data source for the ListView control.

Tasks illustrated in this walkthrough include:

  • Displaying data that is returned from a database in the ListView control.

  • Adding the ability to edit, insert, and delete records to the ListView control.

Prerequisites

In order to complete this walkthrough, you will need:

  • Visual Studio 2008 or Visual Web Developer 2008 Express Edition.

  • Access to the SQL Server AdventureWorks database. For information about how to download and install the SQL Server sample AdventureWorks database, see Installing Sample Databases for Express Editions on the Microsoft SQL Server Web site.

    Note

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

  • A user name and password for a SQL Server account that has access to the AdventureWorks database.

Creating the Web Site

If you have already created a Web site (for example, by completing Walkthrough: Creating a Basic Web Page in Visual Web Developer), you can use that Web site and go to the next section. Otherwise, create a new Web site and page by following these steps.

To create a file system Web site

  1. Open Visual Studio 2008 or Visual Web Developer 2008 Express Edition.

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

    The New Web Site dialog box is displayed.

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

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

    For example, enter the folder name C:\WebSites\ModifyData.

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

  6. Click OK.

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

Enabling Users to Modify Data by Using the ListView Control

In this section, you will add a ListView control to the page and configure it to display and modify data from the Department table of the AdventureWorks database.

To display and modify data in the ListView control

  1. If the Web site does not have an App_Data folder, in Solution Explorer, right-click the project, click Add ASP.NET Folder, and then click App_Data.

  2. In Solution Explorer, right-click the App_Data folder, and then click Add Existing Item.

    The Add Existing Item dialog box is displayed.

  3. Enter the location of the AdventureWorks database file (AdventureWorks_Data.mdf) was installed.

    By default, the .mdf file is installed in the path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf.

    Note

    This procedure will create a copy of the database file in the project. The database file is large. If it is impractical to make a copy of the database, you can connect to it by using an alternative method, such as attaching the database file directly. However, the procedure for doing this is not covered in this walkthrough.

  4. Switch to or open the Default.aspx file.

  5. Switch to Design view.

  6. From the Data tab of the Toolbox, drag a ListView control onto the page.

  7. In the CommonListView Tasks menu, in the ChooseData Source drop-down list, click <New data source…>. The following illustration shows the CommonListView Tasks menu.

    The Data Source Configuration wizard is displayed. The following illustration shows the Data Source Configuration wizard.

    Data Source Configuration Wizard

  8. Click Database.

    This specifies that you want to get data from a database that supports SQL statements, which includes SQL Server and other OLE-DB–compatible databases.

  9. In the Specify an ID for the data source box, a default data source control name, SqlDataSource1, is displayed. You can leave this name.

  10. Click OK.

    The Configure Data Source wizard is displayed.

  11. Under Which data connection should your application use to connect to a database?, select AdventureWorks_Data.mdf from the list.

  12. Click Next.

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

    • It can be more secure than storing it in the page.

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

  13. Make sure that the Yes, save this connection as check box is selected, and then click Next. You can leave the default connection string name.

    The Configure Data Source wizard is displayed, where you can specify the data that you want to retrieve from the database.

  14. Select the Specify a custom SQL statement or stored procedure option. The following illustration shows the Configure Data Source wizard.

    Configure the Select Statement

    Note

    Typically, you would use the option Specify columns from a table or view. However, because the AdventureWorks database has schema names, in this walkthrough you will create a custom SQL statement.

  15. Click Next.

  16. In the Define Custom Statements or Stored Procedures page, enter the following SQL query to retrieve department data from the AdventureWorks database.

    SELECT  DepartmentID, Name, GroupName 
    FROM    HumanResources.Department
    

    You can also click the Query Builder and use Query Builder to create a query and validate it by using the Execute Query button.

  17. Click the UPDATE tab and then enter the following SQL query to update department data in the AdventureWorks database.

    UPDATE HumanResources.Department 
    SET    Name = @Name, GroupName = @GroupName 
    WHERE  (DepartmentID = @DepartmentID)
    
  18. Click the INSERT tab and then enter the following SQL query to insert department data in the AdventureWorks database.

    INSERT INTO HumanResources.Department(Name, GroupName)
    VALUES (@Name, @GroupName)
    
  19. Click the DELETE tab and then enter the following SQL query to delete department data from the AdventureWorks database.

    DELETE FROM HumanResources.Department WHERE (DepartmentID = @DepartmentID)
    
  20. Click Next.

  21. Click Test Query to make sure that you are retrieving the data that you want.

  22. Click Finish.

    The wizard creates a SqlDataSource control and adds it to the page. The ListView control that you added earlier is bound to the SqlDataSource control.

  23. Right-click the ListView control, click Show Smart Tag.

  24. In the ListView Tasks menu, click Configure ListView.

    The Configure ListView dialog box is displayed.

  25. Under Options, select the Enable Editing, Enable Inserting, and Enable Deleting check boxes. The following illustration shows the Configure ListView dialog box.

    Configure ListView

    Note

    You might also want to select a different style, which can make it easier to view the data. To do this, under Select a Style, select a style such as Colorful.

  26. Click OK. The configured ListView control might look like the following illustration.

    ListView Control

You can now test the ListView control.

To test the page

  1. Press CTRL+F5 to run the page.

    The ListView control is displayed with DepartmentID, Name, and GroupName columns.

  2. Click the Edit button to modify a record in the table.

  3. Change the values and then click Update, or click Cancel to cancel the edit operation.

  4. Enter values for the Name and GroupName fields in the bottom of the page and click Insert to insert a new record.

  5. Click the Delete button of the record you just inserted to delete the record from the database.

Next Steps

This walkthrough has shown you the basic steps for using a ListView control to display and edit data records by using a custom layout. The ListView control enables you to perform more sophisticated formatting than what you have done in this walkthrough. To explore other possible scenarios with the ListView control, see ListView Web Server Control Overview.

See Also

Tasks

Walkthrough: Displaying, Paging, and Sorting Data Using the ListView Web Server Control

How To: Secure Connection Strings when Using Data Source Controls

Walkthrough: Basic Data Access in Web Pages

Concepts

ListView Web Server Control Overview