1.14.1 Problem You want to allow the user to edit the data within the table displayed by a DataGrid . 1.14.2 Solution Add an EditCommandColumn column type to the DataGrid control's display to enable editing of the data fields of each record. A typical example of normal display mode output is shown in Figure 1-17, and an example of edit mode output is shown in Figure 1-18. Example 1-39 through Example 1-41 show the .aspx and code-behind files for the application that produces this result. Figure 1-17. DataGrid with editing ”normal mode 
Figure 1-18. DataGrid with editing ”row edit mode 
1.14.3 Discussion This recipe uses the built-in editing facilities of the DataGrid control, in particular the EditCommandColumn column type, which provides Edit command buttons for editing data items in each row of a DataGrid . The EditText , CancelText , and UpdateText properties define the text to be output for the Edit command button's Edit, Cancel, and Update hyperlinks , respectively. <asp:EditCommandColumn ButtonType="LinkButton" EditText="Edit" CancelText="Cancel" UpdateText="Update" />
The ButtonType attribute defines the type of button to output. You can specify LinkButton , which provides hyperlinked text, or PushButton , which outputs an HTML button.  | The Edit command button's EditText , CancelText , and UpdateText properties can also be set to HTML. For example, to output an image for the links, you can use <img src="images/buttons/editButton.gif " border="0"> . | |
In our example that implements this solution, three columns are defined for the DataGrid . The first uses an asp:BoundColumn element with the ReadOnly attribute set to True to prevent users from editing the field contents: <asp:BoundColumn DataField="SectionNumber" ItemStyle-HorizontalAlign="Center" HeaderText="Section" ReadOnly="True" />
The second column uses an asp:TemplateColumn element to define a layout template for normal display ( ItemTemplate ) and edit mode display ( EditItemTemplate ). The EditItemTemplate property defines an asp:TextBox control to control the size and other aspects of the field contents. Both templates are bound to the " SectionHeading " data. <asp:TemplateColumn HeaderText="Section Heading"> <ItemTemplate> <%# DataBinder.Eval(Container.DataItem, _ "SectionHeading") %> </ItemTemplate> <EditItemTemplate> <asp:TextBox id="txtSectionHeading" runat="server" size="55" cssClass="TableCellNormal" text='<%# DataBinder.Eval(Container.DataItem, _ "SectionHeading") %>' /> </EditItemTemplate> </asp:TemplateColumn>
Like the second column, the third column also uses an asp:TemplateColumn tag. In this case, however, the EditItemTemplate property defines an asp:DropDownList control, allowing the user to select only from valid choices for the column. This column is bound to the yesNoSelections ArrayList created in the code-behind. The selection in the drop-down list is initialized to the current value in the database by binding the SelectedIndex to the index of the value in the ArrayList . <asp:TemplateColumn HeaderText="VB Example" ItemStyle-HorizontalAlign="Center"> <ItemTemplate> <%# yesNoSelections.Item(Cint(DataBinder.Eval(Container.DataItem, _ "HasVBExample"))) %> </ItemTemplate> <EditItemTemplate> <asp:DropDownList id="selHasVBSample" runat="server" DataSource="<%# yesNoSelections %>" DataTextField="Text" DataValueField="Value" SelectedIndex='<%# CInt(DataBinder.Eval(Container.DataItem, _ "HasVBExample")) %>' /> </EditItemTemplate> </asp:TemplateColumn>
Note that the Protected yesNoSelections As ArrayList declaration is added at the class level in the code-behind to provide access to the ArrayList from the code in the .aspx file. Page_Load just calls bindData , as is typical in this chapter's recipes. However, bindData is a bit different from the norm in two ways. First, the ArrayList is built with the selections that are applicable for the user to select from when changing the value of the "Has VB Example" column. Second, the line dgProblems.DataKeyField = " EditProblemID " is added to have the DataGrid maintain the primary key value for each row without having to add it to the grid as a column (hidden or visible). This approach stores the primary key value for each row in the view state only so that it can be recovered when needed on the server side. It also has the advantage of hiding the value from prying eyes. The dgProblems_EditCommand method handles the event generated when the user clicks the Edit link within a row. It simply sets the EditItemIndex to the selected row, which causes ASP.NET to use the Edit Template when the data for the row is rebound along with the Cancel and Update links in the edit command column. The dgProblems_CancelCommand method handles the event generated when the user clicks the Cancel link in the row being edited. It simply sets the EditItemIndex to -1 to display the DataGrid in normal mode when the data is rebound. The dgProblems_UpdateCommand method handles the event generated when the user clicks the Update link in the row being edited. It extracts the edited data, updates the data in the database, and resets the DataGrid to normal mode when the data is rebound (see comments in the code for more details). Example 1-39. DataGrid with editing (.aspx) <%@ Page Language="vb" AutoEventWireup="false" Codebehind="CH01DataGridWithEditingVB.aspx.vb" Inherits="ASPNetCookbook.VBExamples.CH01DataGridWithEditingVB" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <title>DataGrid With Editing</title> <link rel="stylesheet" href="css/ASPNetCookbook.css"> </head> <body leftmargin="0" marginheight="0" marginwidth="0" topmargin="0"> <form id="frmDatagrid" method="post" runat="server"> <table width="100%" cellpadding="0" cellspacing="0" border="0"> <tr> <td align="center"> <img src="images/ASPNETCookbookHeading_blue.gif"> </td> </tr> <tr> <td class="dividerLine"> <img src="images/spacer.gif" height="6" border="0"></td> </tr> </table> <table width="90%" align="center" border="0"> <tr> <td><img src="images/spacer.gif" height="10" border="0"></td> </tr> <tr> <td align="center" class="PageHeading"> DataGrid With Editing (VB) </td> </tr> <tr> <td><img src="images/spacer.gif" height="10" border="0"></td> </tr> <tr> <td align="center"> <asp:DataGrid id="dgProblems" runat="server" BorderColor="000080" BorderWidth="2px" AutoGenerateColumns="False" width="100%"> <HeaderStyle HorizontalAlign="Center" ForeColor="#FFFFFF" BackColor="#000080" Font-Bold=true CssClass="TableHeader" /> <ItemStyle BackColor="#FFFFE0" cssClass="TableCellNormal" /> <AlternatingItemStyle BackColor="#FFFFFF" cssClass="TableCellAlternating" /> <Columns> <asp:BoundColumn DataField="SectionNumber" ItemStyle-HorizontalAlign="Center" HeaderText="Section" ReadOnly="True" /> <asp:TemplateColumn HeaderText="Section Heading"> <ItemTemplate> <%# DataBinder.Eval(Container.DataItem, _ "SectionHeading") %> </ItemTemplate> <EditItemTemplate> <asp:TextBox id="txtSectionHeading" runat="server" size="55" cssClass="TableCellNormal" text='<%# DataBinder.Eval(Container.DataItem, _ "SectionHeading") %>' /> </EditItemTemplate> </asp:TemplateColumn> <asp:TemplateColumn HeaderText="VB Example" ItemStyle-HorizontalAlign="Center"> <ItemTemplate> <%# yesNoSelections.Item(Cint(DataBinder.Eval(Container.DataItem, _ "HasVBExample"))) %> </ItemTemplate> <EditItemTemplate> <asp:DropDownList id="selHasVBSample" runat="server" DataSource="<%# yesNoSelections %>" DataTextField="Text" DataValueField="Value" SelectedIndex='<%# CInt(DataBinder.Eval(Container.DataItem, _ "HasVBExample")) %>' /> </EditItemTemplate> </asp:TemplateColumn> <asp:EditCommandColumn ButtonType="LinkButton" EditText="Edit" CancelText="Cancel" UpdateText="Update" /> </Columns> </asp:DataGrid> </td> </tr> </table> </form> </body> </html>
Example 1-40. DataGrid with editing code-behind (.vb) Option Explicit On Option Strict On '----------------------------------------------------------------------------- ' ' Module Name: CH01DataGridWithEditingVB.aspx.vb ' ' Description: This class provides the code behind for ' CH01DataGridWithEditingVB ' '***************************************************************************** Imports Microsoft.VisualBasic Imports System Imports System.Collections Imports System.Configuration Imports System.Data Imports System.Data.OleDb Imports System.Web.UI.WebControls Namespace ASPNetCookbook.VBExamples Public Class CH01DataGridWithEditingVB Inherits System.Web.UI.Page 'controls on form Protected WithEvents dgProblems As System.Web.UI.WebControls.DataGrid 'The following variable contains the list of yes/no selections used in 'the dropdown lists and is declared protected to provide access to the 'data from the aspx page Protected yesNoSelections As ArrayList '************************************************************************* ' ' ROUTINE: Page_Load ' ' DESCRIPTION: This routine provides the event handler for the page load ' event. It is responsible for initializing the controls ' on the page. '------------------------------------------------------------------------- Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load If (Not Page.IsPostBack) Then bindData( ) End If End Sub 'Page_Load '************************************************************************* ' ' ROUTINE: dgProblems_EditCommand ' ' DESCRIPTION: This routine provides the event handler for the edit ' command click event. It is responsible for setting the ' edit item index to the selected item and rebinding ' the data. '------------------------------------------------------------------------- Private Sub dgProblems_EditCommand(ByVal source As Object, _ ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) _ Handles dgProblems.EditCommand dgProblems.EditItemIndex = e.Item.ItemIndex bindData( ) End Sub 'dgProblems_EditCommand '************************************************************************* ' ' ROUTINE: dgProblems_CancelCommand ' ' DESCRIPTION: This routine provides the event handler for the cancel ' command click event. It is responsible for resetting the ' edit item index to no item and rebinding the data. '------------------------------------------------------------------------- Private Sub dgProblems_CancelCommand(ByVal source As Object, _ ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) _ Handles dgProblems.CancelCommand dgProblems.EditItemIndex = -1 bindData( ) End Sub 'dgProblems_CancelCommand '************************************************************************* ' ' ROUTINE: dgProblems_UpdateCommand ' ' DESCRIPTION: This routine provides the event handler for the update ' command click event. It is responsible for updating ' the contents of the database with the date entered for ' the item currently being edited. '------------------------------------------------------------------------- Private Sub dgProblems_UpdateCommand(ByVal source As Object, _ ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) _ Handles dgProblems.UpdateCommand Dim dbConn As OleDbConnection Dim dCmd As OleDbCommand Dim sectionHeading As String Dim hasVBSample As Integer Dim strConnection As String Dim strSQL As String Dim rowsAffected As Integer Try 'get the edited section heading and "has vb sample" data 'NOTE: This can be done by using the FindControl method of the edited ' item because EditItemTemplates were used and the controls in the ' templates were given IDs. If a standard BoundColumn was used, ' the data would have to be acccessed using the cells collection ' (e.g. e.Item.Cells(0).Text would access the section number ' column in this example. sectionHeading = CType(e.Item.FindControl("txtSectionHeading"), _ TextBox).Text( ) hasVBSample = CInt(CType(e.Item.FindControl("selHasVBSample"), _ DropDownList).SelectedItem.Value) 'get the connection string from web.config and open a connection 'to the database strConnection = _ ConfigurationSettings.AppSettings("dbConnectionString") dbConn = New OleDbConnection(strConnection) dbConn.Open( ) 'update data in database 'NOTE: The primary key used to uniquely identify the row being edited ' is accessed through the DataKeys collection of the DataGrid. strSQL = "UPDATE EditProblem " & _ "SET SectionHeading='" & sectionHeading & "'" & _ ",HasVBExample=" & hasVBSample & _ " WHERE EditProblemID=" & _ dgProblems.DataKeys(e.Item.ItemIndex).ToString( ) dCmd = New OleDbCommand(strSQL, dbConn) rowsAffected = dCmd.ExecuteNonQuery( ) dbConn.Close( ) 'TODO: production code should check the number of rows affected here to 'make sure it is exactly 1 and output the appropriate success or 'failure information to the user. 'reset the edit item and rebind the data dgProblems.EditItemIndex = -1 bindData( ) Finally 'cleanup If (Not IsNothing(dbConn)) Then dbConn.Close( ) End If End Try End Sub 'dgProblems_UpdateCommand '************************************************************************* ' ' ROUTINE: bindData ' ' DESCRIPTION: This routine queries the database for the data to ' displayed and binds it to the DataGrid '------------------------------------------------------------------------- Private Sub bindData( ) Dim dbConn As OleDbConnection Dim da As OleDbDataAdapter Dim ds As DataSet Dim strConnection As String Dim strSQL As String Try 'get the connection string from web.config and open a connection 'to the database strConnection = _ ConfigurationSettings.AppSettings("dbConnectionString") dbConn = New OleDbConnection(strConnection) dbConn.Open( ) 'build the query string and get the data from the database strSQL = "SELECT EditProblemID, SectionNumber" & _ ", SectionHeading, HasVBExample " & _ "FROM EditProblem " & _ "ORDER BY SectionNumber" da = New OleDbDataAdapter(strSQL, dbConn) ds = New DataSet da.Fill(ds) 'build the arraylist with the acceptable responses to the '"Has VB Sample" field yesNoSelections = New ArrayList(2) yesNoSelections.Add(New ListItem("No", "0")) yesNoSelections.Add(New ListItem("Yes", "1")) 'set the source of the data for the datagrid control and bind it dgProblems.DataSource = ds dgProblems.DataKeyField = "EditProblemID" dgProblems.DataBind( ) Finally 'cleanup If (Not IsNothing(dbConn)) Then dbConn.Close( ) End If End Try End Sub 'bindData End Class 'CH01DataGridWithEditingVB End Namespace
Example 1-41. DataGrid with editing code-behind (.cs) //---------------------------------------------------------------------------- // // Module Name: CH01DataGridWithEditingCS.aspx.cs // // Description: This class provides the code behind for // CH01DataGridWithEditingCS.aspx // //**************************************************************************** using System; using System.Collections; using System.Configuration; using System.Data; using System.Data.OleDb; using System.Web.UI.WebControls; namespace ASPNetCookbook.CSExamples { public class CH01DataGridWithEditingCS : System.Web.UI.Page { // controls on form protected System.Web.UI.WebControls.DataGrid dgProblems; // The following variable contains the list of yes/no selections used in // the dropdown lists and is declared protected to provide access to the // data from the aspx page protected ArrayList yesNoSelections; //************************************************************************ // // ROUTINE: Page_Load // // DESCRIPTION: This routine provides the event handler for the page // load event. It is responsible for initializing the // controls on page. //------------------------------------------------------------------------ private void Page_Load(object sender, System.EventArgs e) { // wire edit events this.dgProblems.CancelCommand += new DataGridCommandEventHandler(this.dgProblems_CancelCommand); this.dgProblems.EditCommand += new DataGridCommandEventHandler(this.dgProblems_EditCommand); this.dgProblems.UpdateCommand += new DataGridCommandEventHandler(this.dgProblems_UpdateCommand); if (!Page.IsPostBack) { bindData( ); } } // Page_Load //************************************************************************ // // ROUTINE: dgProblems_EditCommand // // DESCRIPTION: This routine provides the event handler for the edit // command click event. It is responsible for setting the // edit item index to the selected item and rebinding // the data. //------------------------------------------------------------------------ private void dgProblems_EditCommand(Object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { dgProblems.EditItemIndex = e.Item.ItemIndex; bindData( ); } // dgProblems_EditCommand //************************************************************************ // // ROUTINE: dgProblems_CancelCommand // // DESCRIPTION: This routine provides the event handler for the cancel // command click event. It is responsible for resetting the // edit item index to no item and rebinding the data. //------------------------------------------------------------------------ private void dgProblems_CancelCommand(Object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { dgProblems.EditItemIndex = -1; bindData( ); } // dgProblems_CancelCommand //************************************************************************ // // ROUTINE: dgProblems_UpdateCommand // // DESCRIPTION: This routine provides the event handler for the update // command click event. It is responsible for updating // the contents of the database with the date entered for // the item currently being edited. //------------------------------------------------------------------------ private void dgProblems_UpdateCommand(Object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { OleDbConnection dbConn = null; OleDbCommand dCmd = null; String sectionHeading = null; int hasCSSample; String strConnection = null; String strSQL = null; int rowsAffected; DropDownList ddl = null; try { // get the edited section heading and "has vb sample" data // NOTE: This can be done by using the FindControl method of the edited // item because EditItemTemplates were used and the controls in // the templates were given IDs. If a standard BoundColumn was // used, the data would have to be acccessed using the cells // collection (e.g. e.Item.Cells(0).Text would access the section // number column in this example. sectionHeading = ((TextBox)(e.Item.FindControl("txtSectionHeading"))).Text; ddl =(DropDownList)(e.Item.FindControl("selHasCSSample")); hasCSSample = Convert.ToInt32(ddl.SelectedItem.Value); // get the connection string from web.config and open a connection // to the database strConnection = ConfigurationSettings.AppSettings["dbConnectionString"]; dbConn = new OleDbConnection(strConnection); dbConn.Open( ); // update data in database // NOTE: The primary key used to uniquely identify the row being edited // is accessed through the DataKeys collection of the DataGrid. strSQL = "UPDATE EditProblem " + "SET SectionHeading='" + sectionHeading + "'" + ",HasCSExample=" + hasCSSample + " WHERE EditProblemID=" + dgProblems.DataKeys[e.Item.ItemIndex].ToString( ); dCmd = new OleDbCommand(strSQL, dbConn); rowsAffected = dCmd.ExecuteNonQuery( ); dbConn.Close( ); // TODO: production code should check the number of rows affected here to // make sure it is exactly 1 and output the appropriate success or // failure information to the user. // reset the edit item and rebind the data dgProblems.EditItemIndex = -1; bindData( ); } finally { //cleanup if (dbConn != null) { dbConn.Close( ); } } } // dgProblems_UpdateCommand //************************************************************************ // // ROUTINE: bindData // // DESCRIPTION: This routine queries the database for the data to // displayed and binds it to the DataGrid //------------------------------------------------------------------------ private void bindData( ) { OleDbConnection dbConn = null; OleDbDataAdapter da = null; DataSet ds = null; String strConnection = null; String strSQL =null; try { // get the connection string from web.config and open a connection // to the database strConnection = ConfigurationSettings.AppSettings["dbConnectionString"]; dbConn = new OleDbConnection(strConnection); dbConn.Open( ); // build the query string and get the data from the database strSQL = "SELECT EditProblemID, SectionNumber" + ", SectionHeading, HasCSExample " + "FROM EditProblem " + "ORDER BY SectionNumber"; da = new OleDbDataAdapter(strSQL, dbConn); ds = new DataSet( ); da.Fill(ds); // build the hashtable with the acceptable responses to the // "Has VB Sample" field yesNoSelections = new ArrayList(2); yesNoSelections.Add(new ListItem("No", "0")); yesNoSelections.Add(new ListItem("Yes", "1")); // set the source of the data for the datagrid control and bind it dgProblems.DataSource = ds; dgProblems.DataKeyField = "EditProblemID"; dgProblems.DataBind( ); } // try finally { //clean up if (dbConn != null) { dbConn.Close( ); } } // finally } // bindData } // CH01DataGridWithEditingCS }
| 