O'Reilly logo

ASP.NET Cookbook by Geoffrey T. LeBlond, Michael A Kittel

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

1.14. Editing Data Within a DataGrid

Problem

You want to allow the user to edit the data within the table displayed by a DataGrid.

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.

DataGrid with editing—normal mode

Figure 1-17. DataGrid with editing—normal mode

DataGrid with editing—row edit mode

Figure 1-18. DataGrid with editing—row edit mode

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.

Tip

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
}

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required