Created 2/21/2007
Copyright 2007 Kip Irvine, All rights reserved.
Editing rows in the GridView control is fairly easy if you use the TextBox controls provided for you. But if the field being edited is an ID number rather than a name, users will have a hard time remembering the right ID values. The Products table in the Northwind database, for example, contains a Product ID. Let's look at a grid that permits the user to edit this table.
Before the user clicks the Edit button, the Category ID appears in each row:
When the user clicks the Edit button, the Category ID is displayed in a TextBox:
It would be much nicer when editing to display a DropDownList control containing Category names. The user could select a name, and the grid would take care of updating the data source. We will now lead you through the steps required to do this:
- Create an SqlDataSource Control
- Attach a GridView control to the SqlDataSource
- Add another SqlDataSource Control for the Categories Table
- Create an EditItemTemplate
- Bind the DropDownList to the CategoryID column
- Display the Category Name in the GridView
Step 1: Create an SqlDataSource Control
Insert an SqlDataSource control and connect it to the Northwind database. Select the ProductID, ProductName, CategoryID, QuantityPerUnit, and UnitPrice columns from the Products table:
- Click the ORDER BY button and sort by ProductName.
- Click the Advanced button and select Generate INSERT, UPDATE, and DELETE statements:
- Rename the SqlDataSource control to ProductsDataSource.
Step 2: Attach a GridView Control to the SqlDataSource
- Add a GridView control to the form and name it ProductsGrid.
- Set its Width property to 95%.
- Set its AutoFormat property to Professional.
- Set its EditRowStyle.BackColor property to Blue.
- Set its EditRowStyle.ForeColor proprty to White.
- Attach it to ProductsDataSource.
- Select the Enable Editing checkbox in the GridView Tasks popup window.
- Run the application and verify that you can edit the category ID of any row.
Step 3: Add another SqlDataSource Control for the Categories Table
Add a new SqlDataSource control to the form and bind it to the CategoryID and CategoryName of the Categories table. Order it by CategoryName.
Save it and name it CategoriesDataSource.
Step 4: Create an EditItemTemplate
- From the GridView Tasks window, select Edit Columns.
- Remove the ProductID column.
- Select the CategoryID column and click the link that says Convert this field into a TemplateField:
- Click the OK button to close the Fields window.
- Again in the GridView Tasks window, select Edit Templates.
- Select EditItemTemplate from the smart tag popup (GridView tasks).
- Replace the TextBox control with a DropDownList, and name it ddlCategories.
- Set its Width to 200 pixels.
- Connect it to CategoriesDataSource so that it displays the CategoryName and assigns the CategoryID to the DataValueField.
- Run the program, select the Edit button for a product, and notice how the DropDownList in the CategoryD column fills with category names. Click the Cancel button to end the editing. (If you were to click the Update button, you would insert a blank value into the CategoryID column.)
Step 5: Bind the DropDownList to the CategoryID column
This is the least intuitive part of the process. You must connect the DropDownList to the CategoryID column in the Products table, to ensure that the user's selections during editing will be written to the database.
- While editing the EditItemTemplate of ProductsGrid, select Edit DataBindings:
- In the DataBindings window, Bind the SelectedValue property to the CategoryID field:
- Click the OK button to save the changes.
- End the template editing.
- Test the program again, this time editing one of the product categories and clicking the Update button. If you watch carefully, you will see that the CategoryID value changes. The only problem is, users will prefer to view the Category name rather than the Category ID.
Step 6: Display the Category Name in the GridView
Next, you will modify the Category ID column so it displays the CategoryName value.
- Edit the ProductsDataSource control and add the Categories table to it. You will have to select the option that says Specify a custom SQL statement or stored procedure:
- Continue to the Query Builder window, where you will add the Categories table to the SELECT query. Select the CategoryName column:
- When prompted by the following dialog, click the No button:
- Edit the GridView template again and select ItemTemplate.
- Widen the Label control in the ItemTemplate to 200 pixels.
- Click the Label's smart tag and select Edit DataBindings.
- Bind the Text property to the CategoryName column:
- Click OK to close the window.
- Run the application again, and you should see the Category Name field in the Category ID column. Edit the column definitions and replace the column heading with "Category".
EditItemTemplate Definition
This is how the grid's EditItemTemplate looks in the Web page's source view:
<EditItemTemplate>
<asp:DropDownList ID="ddlCategories" runat="server"
DataSourceID="CategoriesDataSource"
DataTextField="CategoryName" DataValueField="CategoryID"
SelectedValue='<%# Bind("CategoryID") %>'
Width="200px">
</asp:DropDownList>
</EditItemTemplate>
Notice in particular how the Bind expression is used to assign a value to the SelectedValue property of the DropDownList control. Using this technique, you can bind any property of a control to any database field, as long as their data types are compatible. View the complete code listing.
Extra Practice
Add a Supplier ID column to the grid and create an EditItemTemplate that lets the user select a supplier name from a DropDownList control. Connect the DropDownList to the Suppliers table. Bind the control to the SupplierID column in the Products table.