GridView Examples for ASP.NET 2.0: Editing the Underlying Data in a GridView
Click here to return to the TOC.
In addition to deleting a GridView's underlying data, another common need is to allow end users to edit the data displayed in a GridView. With ASP.NET 1.x's DataGrid control, editing the data is certainly possible, but requires creating three event handlers and writing a dozen or so lines of code. With the GridView and ASP.NET 2.0, it is possible to create an editable GridView without writing a single line of code! All of the necessary functionality is encapsulated within the GridView.
The GridView allows editing on a row-by-row basis. An editable GridView contains an additional column with an Edit button in each row. When the end user clicks on an Edit button that row becomes editable, causing the Edit button to change to Update and Cancel buttons and the other columns to become TextBoxes. The end user can then update one or more column values and click Update to save their changes.
In this section we'll examine four examples. Our first demo will look at how to create an editable GridView whose data comes from a SqlDataSource. Next, we'll see how to create an editable GridView from an ObjectDataSource. As you may have guessed, when creating an editable GridView whose data comes from an ObjectDataSource, the underlying data access layer class must provide an appropriate Update method. We'll look at the required signature for the Update method and see how to configure the ObjectDataSource to use this method. In the last two demos we'll see how to customize the editing interface for a GridView column. By default, the TextBoxes have no validation controls associated with them, but your business rules might require certain validation. A particular field may be required or might need to be entered in a specific format. Additionally, the standard TextBox as an editing interface might not be acceptable; we'll also look at an example that replaces the TextBox with a databound DropDownList.
Editing GridView Data That Comes From a SqlDataSource
Creating an editable GridView whose data comes from a SqlDataSource is amazingly easy. To start, the GridView's SqlDataSource must contain an UpdateCommand with appropriate parameters. As we saw in the Deleting a GridView's Underlying Data section, creating such a SqlDataSource is as simple as checking the Generate Insert, Update, and Delete Statements from the dialog box shown back in Figure 35. (Recall that this dialog box is accessed through the second step of the SqlDataSource wizard, by clicking the Advanced button.)
As with the delete example, checking the Generate Insert, Update, and Delete Statements will create not only an UPDATE statement, but INSERT and DELETE statements as well. These can be safely removed if you are creating a GridView that's limited to just editing the existing data. Once you have configured a SqlDataSource with an UPDATE statement, creating an editable GridView is a breeze. Just add a new GridView to the page and, from its Smart Tag, click the Enable Editing checkbox (see Figure 39). This will add a CommandField to the GridView with an Edit button.
.gif "Click here for larger image.")
Figure 39 (Click on the graphic for a larger image)
That's all there is to it! Figures 40 and 41 show screenshots of the editable GridView in action. Figure 40 shows the GridView in its pre-editable state. Figure 41 shows the screen after an Edit button has been clicked; note that the clicked Edit button gives way to Update and Cancel buttons, while the columns in the editable row are rendered as TextBoxes.
.gif "ms972948.gridview_fg40(en-us,MSDN.10).gif")
Figure 40
.gif "ms972948.gridview_fg41(en-us,MSDN.10).gif")
Figure 41
In Figure 41 notice how the ProductID column is not editable. As the ASP.NET page's declarative syntax below shows, you can mark a GridView column as non-editable by settings its ReadOnly property to True. When poking through the markup below, be sure to take note of the following:
- The SqlDataSource contains an UpdateCommand property and an <UpdateParameters> section, which describes the required parameters in the UPDATE statement. If you forget to add this UpdateCommand to the SqlDataSource you will not be able to create an editable GridView.
- The <asp:Parameter> tags in the <UpdateParameters> section will, by default, convert blank strings entered into the GridView to NULLs. You can override this behavior by setting the ConvertEmptyStringToNull property to False for whatever parameters needed.
- The ProductID column is non-editable due to the ReadOnly="True" setting in the associated BoundField.
- This example demonstrates the GridView's default editing interface, which renders all editable columns as TextBoxes.
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="productDataSource" Runat="server"
SelectCommand="SELECT [ProductName], [ProductID],
[UnitPrice], [UnitsInStock] FROM [Products]"
UpdateCommand="UPDATE [Products] SET [ProductName] =
@ProductName, [UnitPrice] = @UnitPrice, [UnitsInStock] =
@UnitsInStock WHERE [ProductID] = @original_ProductID"
ConnectionString="<%$ ConnectionStrings:NWConnectionString %>">
<UpdateParameters>
<asp:Parameter Type="String"
Name="ProductName"></asp:Parameter>
<asp:Parameter Type="Decimal"
Name="UnitPrice"></asp:Parameter>
<asp:Parameter Type="Int16"
Name="UnitsInStock"></asp:Parameter>
<asp:Parameter Type="Int32" Name="ProductID"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" Runat="server"
DataSourceID="productDataSource" DataKeyNames="ProductID"
AutoGenerateColumns="False" AllowPaging="True"
BorderWidth="1px" BackColor="White"
CellPadding="4" BorderStyle="None" BorderColor="#3366CC">
<FooterStyle ForeColor="#003399"
BackColor="#99CCCC"></FooterStyle>
<PagerStyle ForeColor="#003399" HorizontalAlign="Left"
BackColor="#99CCCC"></PagerStyle>
<HeaderStyle ForeColor="#CCCCFF" Font-Bold="True"
BackColor="#003399"></HeaderStyle>
<Columns>
<asp:CommandField ShowEditButton="True"></asp:CommandField>
<asp:BoundField ReadOnly="True" HeaderText="ProductID"
InsertVisible="False" DataField="ProductID"
SortExpression="ProductID"></asp:BoundField>
<asp:BoundField HeaderText="Product"
DataField="ProductName"
SortExpression="ProductName"></asp:BoundField>
<asp:BoundField HeaderText="Unit Price"
DataField="UnitPrice" SortExpression="UnitPrice">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Units In Stock"
DataField="UnitsInStock" SortExpression="UnitsInStock">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
</Columns>
<SelectedRowStyle ForeColor="#CCFF99" Font-Bold="True"
BackColor="#009999"></SelectedRowStyle>
<RowStyle ForeColor="#003399" BackColor="White"></RowStyle>
</asp:GridView>
</div>
</form>
</body>
</html>
The above example was created without checking the Use optimistic concurrency checkbox. If you checked that along with the Generate Insert, Update, and Delete Statements checkbox, the UpdateCommand for the SqlDataSource would have included a more thorough WHERE clause. Instead of just using WHERE [ProductID] = @original\_ProductID, with optimistic concurrency the WHERE clause would have read:
WHERE [ProductID] = @original_ProductID AND [ProductName] = @original_ProductName AND [UnitPrice] = @original_UnitPrice AND [UnitsInStock] = @original_UnitsInStock
This would prevent an UPDATE from happening if, between the time the user clicked a GridView row's Edit button and having clicked the Update button, another user had modified that particular row.
Editing GridView Data That Comes from an ObjectDataSource
Like deleting data from a GridView bound to an ObjectDataSource, creating an editable GridView bound to an ObjectDataSource requires very few changes in the creation of the ASP.NET page. Most of the work is required in adding a method to the data access layer class to handle the update.
As with the Delete example, the signature for the Update method depends on the ObjectDataSource's ConflictDetection property. If you are using the default behavior—OverwriteChanges—then the Update method needs to accept the original primary key field(s) along with parameters for the non-primary key updated values. If, on the other hand, CompareAllValues is used, the Update method must accept both the updated and original non-primary key field values along with the original primary key field(s).
To help clarify any confusion, let's look at a concrete example. Let's create a GridView that displays fields from the Northwind Products table, just like we did in the previous demo. For this demo, though, we'll need to add an Update method to the ProductDAL class. (Recall that we created the ProductDAL class back in the Accessing Data with the DataSource Controls section when examining the ObjectDataSource.) Assuming that we're using the OverwriteChanges behavior, the Update method would look like:
The UpdateProduct Method (Visual Basic)
Public Class ProductDAL
...
Public Shared Sub UpdateProduct(ByVal original_ProductID As Integer, _
ByVal productName As String, ByVal unitPrice As Decimal, _
ByVal unitsInStock As Integer)
'Updates the Products table
Dim sql As String = "UPDATE Products SET ProductName = " & _
" @ProductName, UnitPrice = @UnitPrice, UnitsInStock = " & _
"@UnitsInStock WHERE ProductID = @ProductID"
Using myConnection As _
New SqlConnection( _
ConfigurationManager.ConnectionStrings("NWConnectionString").ConnectionString)
Dim myCommand As New SqlCommand(sql, myConnection)
myCommand.Parameters.Add( _
New SqlParameter("@ProductName", productName))
myCommand.Parameters.Add(New SqlParameter("@UnitPrice", _
unitPrice))
myCommand.Parameters.Add(New SqlParameter("@UnitsInStock", _
unitsInStock))
myCommand.Parameters.Add(New SqlParameter("@ProductID", _
original_ProductID))
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Using
End Sub
End Class
The UpdateProduct Method (C#)
public class ProductDAL
{
...
public static void UpdateProduct(int original_ProductID,
string productName, decimal unitPrice, int unitsInStock)
{
// Updates the Products table
string sql = "UPDATE Products SET ProductName = " +
"@ProductName, UnitPrice = @UnitPrice, UnitsInStock = " +
"@UnitsInStock WHERE ProductID = @ProductID";
using (SqlConnection myConnection =
new
SqlConnection(ConfigurationManager.ConnectionStrings["NWConnectionString"].ConnectionString))
{
SqlCommand myCommand = new SqlCommand(sql, myConnection);
myCommand.Parameters.Add(new SqlParameter("@ProductName",
productName));
myCommand.Parameters.Add(new SqlParameter("@UnitPrice",
unitPrice));
myCommand.Parameters.Add(new SqlParameter("@UnitsInStock",
unitsInStock));
myCommand.Parameters.Add(new SqlParameter("@ProductID",
original_ProductID));
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
}
}
}
As you can see, the Update method, UpdateProducts(), takes in the original_ProductID along with each of the editable columns in the GridView: productName, unitPrice, and unitsInStock. If you had configured the ObjectDataSource to CompareAllValues you'd need an Update method with a signature like:
' Visual Basic .NET
Public Shared Sub UpdateProduct(ByVal original_ProductID as Integer, _
ByVal original_productName as String, _
ByVal decimal original_unitPrice as Decimal, _
ByVal original_unitsInStock as Integer, _
ByVal productName as String, ByVal unitPrice as Decimal, _
ByVal unitsInStock as Integer)
...
End Sub
// C#
public static void UpdateProduct(int original_ProductID,
string original_productName, decimal original_unitPrice,
int original_unitsInStock, string productName, decimal unitPrice,
int unitsInStock)
{
...
}
If you use the CompareAllValues approach, it is your responsibility as the DAL developer to take whatever steps are needed to determine if the underlying data has been during the user's edits and, if so, how to resolve the issue.
Once the DAL class has been given an appropriate Update method, the final step is configuring the ObjectDataSource to utilize this Update method. To accomplish this, from the ObjectDataSource's wizard navigate to the UPDATE tab and select the appropriate method (see Figure 42).
.gif "ms972948.gridview_fg42(en-us,MSDN.10).gif")
Figure 42
That's all there is to it! From the end user's perspective, updating a GridView that is bound to a SqlDataSource is no different than updating a GridView that is bound to an ObjectDataSource. You can refer back to Figures 40 and 41 to see the editable GridView in action. The following shows the declarative syntax for the ASP.NET page. As with the SqlDataSource example, note that there's no code necessary in the ASP.NET page.
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ObjectDataSource ID="productDataSource"
Runat="server" TypeName="ProductDAL"
SelectMethod="GetProducts" EnablePaging="True"
SelectCountMethod="TotalNumberOfProducts"
UpdateMethod="UpdateProduct">
<UpdateParameters>
<asp:Parameter Type="Int32" Name="ProductID"></asp:Parameter>
<asp:Parameter Type="String"
Name="productName"></asp:Parameter>
<asp:Parameter Type="Decimal"
Name="unitPrice"></asp:Parameter>
<asp:Parameter Type="Int32"
Name="unitsInStock"></asp:Parameter>
</UpdateParameters>
</asp:ObjectDataSource>
<asp:GridView ID="GridView1" Runat="server"
DataSourceID="productDataSource"
DataKeyNames="ProductID"
AutoGenerateColumns="False" AllowPaging="True"
BorderWidth="1px" BackColor="White"
CellPadding="4" BorderStyle="None" BorderColor="#3366CC">
<FooterStyle ForeColor="#003399"
BackColor="#99CCCC"></FooterStyle>
<PagerStyle ForeColor="#003399" HorizontalAlign="Left"
BackColor="#99CCCC"></PagerStyle>
<HeaderStyle ForeColor="#CCCCFF" Font-Bold="True"
BackColor="#003399"></HeaderStyle>
<Columns>
<asp:CommandField ShowEditButton="True"></asp:CommandField>
<asp:BoundField ReadOnly="True"
HeaderText="ProductID" InsertVisible="False"
DataField="ProductID"
SortExpression="ProductID"></asp:BoundField>
<asp:BoundField HeaderText="Product"
DataField="ProductName" SortExpression="ProductName"></asp:BoundField>
<asp:BoundField HeaderText="Unit Price"
DataField="UnitPrice" SortExpression="UnitPrice">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Units In Stock"
DataField="UnitsInStock"
SortExpression="UnitsInStock">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
</Columns>
<SelectedRowStyle ForeColor="#CCFF99" Font-Bold="True"
BackColor="#009999"></SelectedRowStyle>
<RowStyle ForeColor="#003399" BackColor="White"></RowStyle>
</asp:GridView>
</div>
</form>
</body>
</html>
Customizing the Editing Interface
The past two examples illustrate how easy it is to create an editable GridView. One disadvantage of the default editing capabilities of the GridView, though, is that the editing interface is likely suboptimal. By default, all editable columns in the editable GridView row are rendered as TextBoxes, and none of them have any sort of validation logic associated with them. In real-world situations, though, oftentimes we'll need to add validation logic to the editable inputs, or perhaps even use editing controls other than the TextBox.
Regardless of how you want to alter the GridView's editing interface, the way it's accomplished is through using TemplateFields as opposed to BoundFields. A BoundField will always display a TextBox in edit mode. With a TemplateField, however, you can specify precisely how the editing interface should function.
In this section we'll look at two examples of customizing the GridView's editing interface. In the first, we'll see how to add validation controls; in the second, we'll look at replacing the standard TextBox with a DropDownList instead.
Adding Validation Controls to the Editing Interface
Since the GridView's default editing interface does not provide any validation logic, an end user can enter any sort of data into the editable row's TextBoxes. Looking back at our last two editable GridView examples, consider what would happen if the user entered a value of, say, Sam for the Unit Price of a product. Such actions would result in an exception, since the database cannot set a decimal field to a string value. Similarly, we might want to impose certain business rules, such as the Unit Price is greater than 0, or that Product Name is a required field.
The GridView's editing interface can be manipulated on a column-by-column level. This is accomplished by using TemplateFields in place of BoundFields, and specifying the editable interface to be used. Let's examine how this is done by tweaking the earlier editable GridView bound to a SqlDataSource example to utilize validation controls in the Product, Unit Price, and Units In Stock columns.
The first step is to transform those three columns into TemplateFields. The simplest way to do this it to go to the Design view and click the Edit Columns link in the GridView's Smart Tag. Selecting a BoundField from the column list in the bottom left hand corner, you will see a Convert this field into a TemplateField link (see Figure 43). Go ahead and turn the Product, Unit Price, and Units In Stock columns into TemplateFields.
.gif "ms972948.gridview_fg43(en-us,MSDN.10).gif")
Figure 43
As we saw earlier, when a GridView row is made editable, the BoundFields turn into TextBoxes. The TemplateFields, on the other hand, render whatever HTML markup and Web control syntax you specify in the TemplateField's EditItemTemplate. To edit a TemplateField's EditItemTemplate, choose the Edit Templates link from the GridView's Smart Tag. This will allow you to select what Template for what column you want to edit.
Note If a TemplateField lacks an EditItemTemplate the GridView column will be non-editable.
Notice that by converting the BoundFields into TemplateFields through the Design view, the TemplateField will contain an EditItemTemplate with a TextBox. Figure 44 shows editing the Product column's EditItemTemplate. The TextBox present was not added by myself, but placed there automatically when converting to the TemplateField.
.gif "ms972948.gridview_fg44(en-us,MSDN.10).gif")
Figure 44
To add validation controls to an EditItemTemplate, simply drag and drop the appropriate validation controls from the Toolbox into the EditItemTemplate. Let's add a RequiredFieldValidator in the Product EditItemTemplate, and CompareValidators in the Unit Price and Units In Stock columns to ensure that the data entered is of the right type. Finally, add a ValidationSummary control to the page to display information about invalid data to the user.
Once you have added the validation controls your ASP.NET page's declarative syntax should look similar to the following:
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="productDataSource"
Runat="server"
ConnectionString="<%$ ConnectionStrings:NWConnectionString %>"
UpdateCommand="UPDATE [Products] SET [ProductName] =
@ProductName, [UnitPrice] = @UnitPrice,
[UnitsInStock] = @UnitsInStock WHERE [ProductID] = @original_ProductID"
SelectCommand="SELECT [ProductName], [ProductID],
[UnitPrice], [UnitsInStock] FROM [Products]">
<UpdateParameters>
<asp:Parameter Type="String"
Name="ProductName"></asp:Parameter>
<asp:Parameter Type="Decimal"
Name="UnitPrice"></asp:Parameter>
<asp:Parameter Type="Int16"
Name="UnitsInStock"></asp:Parameter>
<asp:Parameter Type="Int32"
Name="ProductID"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" Runat="server"
BorderColor="#3366CC" BorderStyle="None"
CellPadding="4" BackColor="White"
BorderWidth="1px" AllowPaging="True"
AutoGenerateColumns="False"
DataKeyNames="ProductID" DataSourceID="productDataSource">
<FooterStyle ForeColor="#003399"
BackColor="#99CCCC"></FooterStyle>
<PagerStyle ForeColor="#003399" HorizontalAlign="Left"
BackColor="#99CCCC"></PagerStyle>
<HeaderStyle ForeColor="#CCCCFF"
Font-Bold="True" BackColor="#003399"></HeaderStyle>
<Columns>
<asp:CommandField
ShowEditButton="True"></asp:CommandField>
<asp:BoundField ReadOnly="True"
HeaderText="ProductID" InsertVisible="False"
DataField="ProductID"
SortExpression="ProductID"></asp:BoundField>
<asp:TemplateField SortExpression="ProductName"
HeaderText="Product"><EditItemTemplate>
<asp:TextBox ID="editProductName"
Runat="server"
Text='<%# Bind("ProductName") %>'></asp:TextBox>
<asp:RequiredFieldValidator
ID="RequiredFieldValidator1"
Runat="server"
ErrorMessage="You must provide a Product Name."
ControlToValidate="editProductName">
*</asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label Runat="server"
Text='<%# Bind("ProductName") %>'
ID="Label3"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField SortExpression="UnitPrice"
HeaderText="Unit Price"><EditItemTemplate>
<asp:TextBox ID="editUnitPrice" Runat="server"
Text='<%# Bind("UnitPrice", "{0:#,##0.00}") %>'
Columns="6"></asp:TextBox>
<asp:CompareValidator ID="CompareValidator1"
Runat="server"
ErrorMessage="You must provide a valid currency value for the Unit Price."
ControlToValidate="editUnitPrice"
Operator="DataTypeCheck" Type="Currency">
*</asp:CompareValidator>
</EditItemTemplate>
<ItemStyle HorizontalAlign="Right"></ItemStyle>
<ItemTemplate>
<asp:Label Runat="server"
Text='<%# Bind("UnitPrice", "{0:c}") %>'
ID="Label1"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField SortExpression="UnitsInStock"
HeaderText="Units In Stock"><EditItemTemplate>
<asp:TextBox ID="editUnitsInStock"
Runat="server"
Text='<%# Bind("UnitsInStock") %>'
Columns="4"></asp:TextBox>
<asp:CompareValidator ID="CompareValidator2"
Runat="server"
ErrorMessage="You must provide a valid integer for Units In Stock."
ControlToValidate="editUnitsInStock"
Operator="DataTypeCheck" Type="Integer">
*</asp:CompareValidator>
</EditItemTemplate>
<ItemStyle HorizontalAlign="Right"></ItemStyle>
<ItemTemplate>
<asp:Label Runat="server"
Text='<%# Bind("UnitsInStock") %>'
ID="Label2"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<SelectedRowStyle ForeColor="#CCFF99"
Font-Bold="True"
BackColor="#009999"></SelectedRowStyle>
<RowStyle ForeColor="#003399" BackColor="White"></RowStyle>
</asp:GridView>
</div>
<div>
<asp:ValidationSummary ID="ValidationSummary1" Runat="server" />
</div>
</form>
</body>
</html>
When editing a GridView row you may be wondering how the TextBox is populated with the correct field value for the editable row. As you can see in the TextBox Web control's declarative syntax, the magic is in the Bind() method. Setting the TextBox's Text property to <%# Bind(fieldName) %> displays the value of the field fieldName in the TextBox. This same syntax is also used in the Label Web controls in the TemplateFields' ItemTemplates.
Figure 45 shows a screenshot of the enhanced editable GridView. Note that if invalid values are entered into the fields the underlying data is not updated and the validation summary at the bottom of the GridView indicates what problems exist.
.gif "Click here for larger image.")
Figure 45 (Click on the graphic for a larger image)
Using an Alternative Web Control for the Editing Interface
For certain scenarios the TextBox might not be the ideal editing interface. For example, when editing a date field, users will be less prone to error selecting the date using a Calendar Web control, rather than having to type in the date in a TextBox. Similarly, for lookup data—data that is restricted to a set of legal values, which are commonly defined in a separate database table—the best editing interface is typically a DropDownList.
The Northwind database's Products table contains a CategoryID field that associates a product with a category from the Categories table. This is a prime case where a DropDownList would be suited for the editing interface. Modifying the editing interface to provide a DropDownList is remarkably easy, requiring no source code at all.
To start, create a SqlDataSource that's configured to return a list of products along with the products' associated categories. To accomplish this you'll need to use a custom SQL SELECT statement in the DataSource wizard, one that uses a join to retrieve the correct CategoryName for a product's CategoryID. The SQL query should look something like:
SELECT dbo.Products.ProductID, dbo.Products.ProductName,
dbo.Products.CategoryID, dbo.Categories.CategoryName
FROM dbo.Products
INNER JOIN dbo.Categories ON
dbo.Products.CategoryID = dbo.Categories.CategoryID
Remember when creating the SqlDataSource to include an UpdateCommand. If you forget to specify an UpdateCommand the GridView cannot be made editable.
Next, add a GridView, bind it to the SqlDataSource, and make it editable. Feel free to remove the ProductID and CategoryID columns from the GridView, leaving just the ProductName and CategoryName fields.
Our next task is to customize the editing interface for the CategoryName column so that it uses a DropDownList of categories as opposed to a TextBox. As we saw in the previous section, a TemplateField is needed to create a customized editing interface, so convert the CategoryName column into a TemplateField. Next, from the Design view, edit the CategoryName column's EditItemTemplate.
The CategoryName EditItemTemplate should contain a TextBox. Go ahead and remove this and add the necessary controls to have a DropDownList of the available categories. Simply drag and drop a SqlDataSource onto the page configured to select the CategoryID and CategoryName fields from the Categories table. Next, add a DropDownList to the EditItemTemplate and bind it to this SqlDataSource (see Figure 46).
.gif "ms972948.gridview_fg46(en-us,MSDN.10).gif")
Figure 46
If you visit the ASP.NET page in a browser at this point, you'll find that when editing a row in the GridView the CategoryName column displays a DropDownList of all categories, but regardless of the row's category, the first category in the DropDownList is always the one selected. We need to improve our page so that when a row becomes editable, the DropDownList's SelectedValue is set to the row's CategoryID value. This can be accomplished using the Bind() method we saw in the previous example, as shown in the TemplateField's markup below:
<asp:TemplateField SortExpression="CategoryName" HeaderText="CategoryName">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1" Runat="server"
DataSourceID="categoryDataSource"
DataTextField="CategoryName" DataValueField="CategoryID"
SelectedValue='<%# Bind("CategoryID") %>'>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:Label Runat="server" Text='<%# Bind("CategoryName") %>'
ID="Label1"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
With this final addition—setting the DropDownList's SelectedValue='<%# Bind("CategoryID") %>—the example is complete. Figure 47 shows a screenshot of the GridView as it's being edited.
.gif "ms972948.gridview_fg47(en-us,MSDN.10).gif")
Figure 47
The following shows the complete declarative syntax for the ASP.NET page. Note that this entire exercise was accomplished without writing a line of code.
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="productsDataSource"
Runat="server" SelectCommand="SELECT dbo.Products.ProductID,
dbo.Products.ProductName, dbo.Products.CategoryID,
dbo.Categories.CategoryName FROM dbo.Products INNER JOIN dbo.Categories ON
dbo.Products.CategoryID = dbo.Categories.CategoryID"
UpdateCommand="UPDATE [Products] SET [ProductName] =
@ProductName, [CategoryID] = @CategoryID WHERE [ProductID] =
@original_ProductID"
ConnectionString="<%$ ConnectionStrings:NWConnectionString %>">
<UpdateParameters>
<asp:Parameter Type="String"
Name="ProductName"></asp:Parameter>
<asp:Parameter Type="Int32" Name="CategoryID"></asp:Parameter>
<asp:Parameter Name="original_ProductID"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="categoryDataSource" Runat="server"
SelectCommand="SELECT [CategoryID], [CategoryName] FROM
[Categories] ORDER BY [CategoryName]"
ConnectionString="<%$ ConnectionStrings:NWConnectionString %>">
</asp:SqlDataSource>
<asp:GridView ID="GridView1" Runat="server"
DataSourceID="productsDataSource" DataKeyNames="ProductID"
AutoGenerateColumns="False" AllowPaging="True"
BorderWidth="1px" BackColor="White"
CellPadding="3" BorderStyle="None" BorderColor="#CCCCCC">
<FooterStyle ForeColor="#000066" BackColor="White"></FooterStyle>
<PagerStyle ForeColor="#000066" HorizontalAlign="Left"
BackColor="White"></PagerStyle>
<HeaderStyle ForeColor="White" Font-Bold="True"
BackColor="#006699"></HeaderStyle>
<Columns>
<asp:CommandField ShowEditButton="True"></asp:CommandField>
<asp:BoundField ReadOnly="True" HeaderText="ProductID"
InsertVisible="False" DataField="ProductID"
SortExpression="ProductID">
<ItemStyle HorizontalAlign="Center"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="ProductName"
DataField="ProductName"
SortExpression="ProductName"></asp:BoundField>
<asp:TemplateField SortExpression="CategoryName"
HeaderText="CategoryName"><EditItemTemplate>
<asp:DropDownList ID="DropDownList1"
Runat="server"
DataSourceID="categoryDataSource"
DataTextField="CategoryName"
DataValueField="CategoryID"
SelectedValue='<%# Bind("CategoryID") %>'>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:Label Runat="server"
Text='<%# Bind("CategoryName") %>'
ID="Label1"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<SelectedRowStyle ForeColor="White"
Font-Bold="True" BackColor="#669999"></SelectedRowStyle>
<RowStyle ForeColor="#000066"></RowStyle>
</asp:GridView>
</div>
</form>
</body>
</html>