Walkthrough: Displaying a Drop-Down List While Editing in the GridView Web Server Control

  • Article

The GridView control displays text boxes for editing by default. You can use the ASP.NET GridView control's built-in advanced functionality to add a drop-down list to the editing display. This can be done on a Web form without having to write any code behind the form. Tasks illustrated in this walkthrough include the following:

  • Configuring a GridView control to display SQL data.

  • Displaying data in the GridView control.

  • Displaying a drop-down list while editing in the GridView control.

A full listing for the page that you create in this walkthrough is available at the end of the topic. A Visual Studio project with source code is available to accompany this topic: Download.

Prerequisites

In order to complete this walkthrough, you will need:

  • Visual Studio or Visual Web Developer Express or installed on your computer.

    Note

    If you are using Visual Studio, the walkthrough assumes that you selected the Web Development collection of settings when you started Visual Studio the first time. For more information, see How to: Select Web Development Environment Settings.

  • SQL Server Express. If you have SQL Server installed, you can use that instead, but you must make small adjustments to some of the procedures.

  • The Northwind sample database installed on your computer. For information about how to download and installing the SQL Server sample Northwind database, see Installing Sample Databases on the Microsoft SQL Server Web site.

    Note

    There are many alternative methods that you might use to connect to the Northwind database.

Creating the Web Site and Page

This walkthrough uses a Web site project. You could use a Web application project instead. For information about the difference between these Web project types, see Web Application Projects versus Web Site Projects.

To create a file system Web site

  1. Open Visual Studio or Visual Web Developer Express.

  2. In the File menu, click New Web Site.

    The New Web Site dialog box is displayed.

  3. Under Installed Templates, click Visual Basic or Visual C# and then select ASP.NET Web Site.

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

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

  5. Click OK.

    Visual Studio creates a Web site project that includes prebuilt functionality for layout (a master page, the Default.aspx and About.aspx content pages, and a cascading style sheet), for Ajax (client script files), and for authentication (ASP.NET membership).

Creating a Connection to an SQL Data Source

To start, you must create a connection to the Northwind database.

To create a connection to SQL Server

  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 Database File (SqlClient), click Change, and in the Change Data Source dialog box, select Microsoft SQL Server Database File (SqlClient).

    • 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 then 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 View menu, click Database Explorer.

  2. In the Database file name field, enter or browse to the location where you installed the Northwind sample database.

  3. Select the Northwnd.mdf database file and click Open.

  4. Select the Save my Password check box.

  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.

Configuring a GridView Control to Display Database Data

In this part of the walkthrough, you dynamically populate the grid with data.

To enable simple sorting

  1. Switch to or open the Default.aspx file

  2. Switch to Design view.

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

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

    The Data Source Configuration wizard appears.

  5. 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.

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

  7. Click OK.

    The Configure Data Source wizard is displayed.

  8. 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 in which 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.

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

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

  10. Under Specify columns from a table or view, in the Name box, click Employees.

  11. Under Columns, select the EmployeeId, LastName, FirstName, HireDate, and City check boxes.

    The wizard displays the SQL statement that you are creating in a box at the bottom of the page.

    Note

    The wizard allows you to specify selection criteria (WHERE clauses) and other SQL query options. For this walkthrough, you will create a simple statement with no selection or sort options.

  12. 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 that is 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.

  13. Click Next.

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

  15. Click Finish.

  16. Right-click the GridView control and select Show Smart Tag. From the GridView Tasks menu, select the Enable Editing box.

You can now test the page.

To test the page

  1. Press CTRL+F5 to run the page.

    The GridView control is displayed with EmployeeId, LastName, FirstName, HireDate, and City columns.

  2. Click the Edit link next to a row.

    The row selected for editing is displayed with LastName, FirstName, HireDate, and City columns as an editable text box. EmployeeId is not displayed in a text box, because it is a key field and not editable.

  3. Change a field such as LastName and click Update.

    The GridView control is displayed with EmployeeId, LastName, FirstName, HireDate, and City columns, with LastName updated with the new value.

  4. Close the browser.

Displaying a Drop-Down List While Editing in the GridView Control

In this part of the walkthrough, you can add a drop-down list to select from while editing the rows in the grid.

To display a drop-down list while editing

  1. From the Data node of the Toolbox, drag a SqlDataSource control onto the page.

    A new data source control named SqlDataSource2 is created.

  2. In the SqlDataSource Tasks menu, select Configure Data Source.

  3. In the Which data connection should your application use to connect to the database? box, enter the connection that you created earlier.

  4. Click Next.

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

  6. Select only the City column, and then select the Return only Unique Rows check box. Click Next.

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

  8. Right-click the GridView control and select Show Smart Tag. In the GridView Tasks menu, select Edit Columns.

  9. In the Fields dialog box, select City from the Selected fields list.

  10. Click Convert this field into a TemplateField link.

  11. Click OK to close the Fields dialog box.

  12. Right-click the GridView control and select Show Smart Tag. In the GridView Tasks menu, select Edit Templates.

  13. Select EditItemTemplate in the Display drop-down list.

  14. Right-click the default TextBox control in the template and select Delete to remove it.

  15. From the Standard tab of the Toolbox, drag a DropDownList control onto the template.

  16. Right-click the DropDownList control and select Show Smart Tag. In the DropDownList Tasks menu, select Choose Data Source.

  17. Select SqlDataSource2.

  18. Click OK.

  19. In the DropDownList Tasks menu, select Edit DataBindings. The SelectedValue property of the DropDownList control is selected in the DataBindings dialog box.

  20. Click the Field Binding radio button and select City for Bound To.

  21. Select the Two-way databinding check box.

  22. Click OK.

  23. Right-click the GridView control and select Show Smart Tag. In the GridView Tasks menu, click End Template Editing.

    Security noteSecurity 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 have to explicitly check for script or HTML elements in user input. For more information, see Script Exploits Overview.

You can now test the page.

To test the page

  1. Press CTRL+F5 to run the page.

    The GridView control is displayed with EmployeeId, LastName, FirstName, HireDate, and City columns.

  2. Click the Edit link next to a row.

    The current City value is preselected in the drop-down list.

    Select a different City value from the drop-down list and click Update.

    The City field is updated using the value selected in the drop down list.

Example

Description

The following example shows the markup for the page that you create in this walkthrough.

Code

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="VB_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
            DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:CommandField ShowEditButton="True" />
                <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" 
                    InsertVisible="False" ReadOnly="True" SortExpression="EmployeeID" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" 
                    SortExpression="LastName" />
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" 
                    SortExpression="FirstName" />
                <asp:BoundField DataField="HireDate" HeaderText="HireDate" 
                    SortExpression="HireDate" />
                <asp:TemplateField HeaderText="City" SortExpression="City">
                    <EditItemTemplate>
                        <asp:DropDownList ID="DropDownList1" runat="server" 
                            DataSourceID="SqlDataSource2" DataTextField="City" DataValueField="City" 
                            SelectedValue='<%# Bind("City") %>'>
                        </asp:DropDownList>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("City") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>" 
            DeleteCommand="DELETE FROM [Employees] WHERE [EmployeeID] = @EmployeeID" 
            InsertCommand="INSERT INTO [Employees] ([LastName], [FirstName], [HireDate], [City]) VALUES (@LastName, @FirstName, @HireDate, @City)" 
            SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [HireDate], [City] FROM [Employees]" 
            UpdateCommand="UPDATE [Employees] SET [LastName] = @LastName, [FirstName] = @FirstName, [HireDate] = @HireDate, [City] = @City WHERE [EmployeeID] = @EmployeeID">
            <DeleteParameters>
                <asp:Parameter Name="EmployeeID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="LastName" Type="String" />
                <asp:Parameter Name="FirstName" Type="String" />
                <asp:Parameter Name="HireDate" Type="DateTime" />
                <asp:Parameter Name="City" Type="String" />
                <asp:Parameter Name="EmployeeID" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="LastName" Type="String" />
                <asp:Parameter Name="FirstName" Type="String" />
                <asp:Parameter Name="HireDate" Type="DateTime" />
                <asp:Parameter Name="City" Type="String" />
            </InsertParameters>
        </asp:SqlDataSource>

    </div>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
        ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>" 
        SelectCommand="SELECT DISTINCT [City] FROM [Employees]"></asp:SqlDataSource>
    </form>
</body>
</html>

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
            DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:CommandField ShowEditButton="True" />
                <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" 
                    InsertVisible="False" ReadOnly="True" SortExpression="EmployeeID" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" 
                    SortExpression="LastName" />
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" 
                    SortExpression="FirstName" />
                <asp:BoundField DataField="HireDate" HeaderText="HireDate" 
                    SortExpression="HireDate" />
                <asp:TemplateField HeaderText="City" SortExpression="City">
                    <EditItemTemplate>
                        <asp:DropDownList ID="DropDownList1" runat="server" 
                            DataSourceID="SqlDataSource2" DataTextField="City" DataValueField="City" 
                            SelectedValue='<%# Bind("City") %>'>
                        </asp:DropDownList>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("City") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>" 
            DeleteCommand="DELETE FROM [Employees] WHERE [EmployeeID] = @EmployeeID" 
            InsertCommand="INSERT INTO [Employees] ([LastName], [FirstName], [HireDate], [City]) VALUES (@LastName, @FirstName, @HireDate, @City)" 
            SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [HireDate], [City] FROM [Employees]" 
            UpdateCommand="UPDATE [Employees] SET [LastName] = @LastName, [FirstName] = @FirstName, [HireDate] = @HireDate, [City] = @City WHERE [EmployeeID] = @EmployeeID">
            <DeleteParameters>
                <asp:Parameter Name="EmployeeID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="LastName" Type="String" />
                <asp:Parameter Name="FirstName" Type="String" />
                <asp:Parameter Name="HireDate" Type="DateTime" />
                <asp:Parameter Name="City" Type="String" />
                <asp:Parameter Name="EmployeeID" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="LastName" Type="String" />
                <asp:Parameter Name="FirstName" Type="String" />
                <asp:Parameter Name="HireDate" Type="DateTime" />
                <asp:Parameter Name="City" Type="String" />
            </InsertParameters>
        </asp:SqlDataSource>

    </div>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
        ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>" 
        SelectCommand="SELECT DISTINCT [City] FROM [Employees]"></asp:SqlDataSource>
    </form>
</body>
</html>

Next Steps

Although this walkthrough shows how to display a drop-down list populated with values from the same column as the one bound to the control, there are many alternate methods for populating a drop-down list. See the example for DataItem to see how to populate a drop-down list with values from a different table.

See Also

Tasks

Walkthrough: Basic Data Access in Web Pages

How To: Secure Connection Strings when Using Data Source Controls

Reference

GridView Web Server Control Overview

DataItem