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

Switch View

Walkthrough: Displaying a Drop-Down...

Collapse All

Language Filter

This page is specific to

Microsoft Visual Studio 2008/.NET Framework 3.5

Other versions are also available for the following:

Visual Web Developer

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

Updated: August 2008

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 listed at the end of the topic.

Prerequisites

In order to complete this walkthrough, you will need:

Microsoft Visual Web Developer (Visual Studio).
SQL Server Express Edition. 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.

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

Creating the Web Site and Page

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

To create a file system Web site

Open Visual Web Developer.
On the File menu, click New, and then click Web Site. If you are using Visual Web Developer Express, on the File menu, click New Web Site.
The New Web Site dialog box appears.
Under Visual Studio installed templates, click ASP.NET Web Site.
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\DropDownEdit.
In the Language list, click the programming language you prefer to work in.
Click OK.
Visual Web Developer creates the folder and a new page named Default.aspx.

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

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.

In the Database file name field, enter or browse to the location where you installed the Northwind sample database.
Select the Northwnd.mdf database file and click Open.
Select the Save my Password check box.
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

Switch to or open the Default.aspx file
Switch to Design view.
In the Toolbox, from the Data group, drag a GridView control onto the page.
On the GridView Tasks menu, in the Choose Data Source box, click <New data source>.
The Data Source Configuration wizard appears.
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.
In the Specify an ID for the data source box, a default data source control name, SqlDataSource1, is displayed. You can leave this name.
Click OK.
The Configure Data Source wizard is displayed.
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.
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.
Under Specify columns from a table or view, in the Name box, click Employees.

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.

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.
Click Next.
Click Test Query to make sure that you are retrieving the data that you want.
Click Finish.
Right-click the GridView control and select Show Smart Tag. From the GridView Tasks menu, select the Enable Editing box.

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

You can now test the page.

To test the page

Press CTRL+F5 to run the page.
The GridView control is displayed with EmployeeId, LastName, FirstName, HireDate, and City columns.
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.
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.
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

From the Data node of the Toolbox, drag a SqlDataSource control onto the page.
A new data source control named SqlDataSource2 is created.
In the SqlDataSource Tasks menu, select Configure Data Source.
In the Which data connection should your application use to connect to the database? box, enter the connection that you created earlier.
Click Next.
On the Configure the Select Statement page, select Specify columns from a table or view, and then in the Name box, click Employees.
Select only the City column, and then select the Return only Unique Rows check box. Click Next.
Click Test Query to preview the data, and then click Finish.
Right-click the GridView control and select Show Smart Tag. In the GridView Tasks menu, select Edit Columns.
In the Fields dialog box, select City from the Selected fields list.
Click Convert this field into a TemplateField link.
Click OK to close the Fields dialog box.
Right-click the GridView control and select Show Smart Tag. In the GridView Tasks menu, select Edit Templates.
Select EditItemTemplate in the Display drop-down list.
Right-click the default TextBox control in the template and select Delete to remove it.
From the Standard tab of the Toolbox, drag a DropDownList control onto the template.
Right-click the DropDownList control and select Show Smart Tag. In the DropDownList Tasks menu, select Choose Data Source.
Select SqlDataSource2.
Click OK.
In the DropDownList Tasks menu, select Edit DataBindings. The SelectedValue property of the DropDownList control is selected in the DataBindings dialog box.
Click the Field Binding radio button and select City for Bound To.
Select the Two-way databinding check box.
Click OK.

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

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

Press CTRL+F5 to run the page.
The GridView control is displayed with EmployeeId, LastName, FirstName, HireDate, and City columns.
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 source code for the page that you create in this walkthrough. Only the markup is shown, because you did not make any changes to the code-behind file.

Code

Visual Basic

<%@ 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 >
<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 >
<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.

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

Change History

Date	History	Reason
August 2008	Added an Example section with the full listing for this walkthrough.	Customer feedback.

Date

History

Reason

August 2008

Added an Example section with the full listing for this walkthrough.

Customer feedback.