Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

An Extensive Examination of the DataGrid Web Control - ASP.NET

RSS
Modified on Wed, Jan 11, 2012, 12:20 PM by Administrator Categorized as ASP·NET Web Forms
{outline||<1> - |.<1> - }

Introduction

These notes were condensed from the following article on the 4 Guys From Rolla website: http://aspnet.4guysfromrolla.com/articles/040502-1.aspx. Note that the <asp:DataGrid> control has been deprecated in ASP.NET 2.0 by the <asp:GridView> control. A similar article on the <asp:GridView> control can be found here.

Table of Contents [Hide/Show]


   Basics
   Display Properties
      Specifying DataGrid Formatting Options
      DataGrid Styles
      Specifying what Columns Should Appear
   Custom Events
      Building on a Foundation
      Responding to a Button Click
      Determining What Row's Button was Clicked
   Custom Sorting
      Preparing a DataGrid for Sorting
      Creating a Sort Event Handler
      Creating a Sorting Function
      Specifying the Sort Order Using a Stored Procedure
   Template Columns
      Using a TemplateColumn
      TemplateColumn versus a BoundColumn
   Default Editing
      First Things First: Displaying the Data
      Allowing the End User to Edit a Row
      Selecting a Row to be Edited
      The Cancel Button
      The Update Button
   Custom Editing
      The EditItemTemplate
      Specifying the DropDownList's DataSource
      Selecting the Correct Item in the DropDownList
      Retrieving the DropDownList's Value
   Deleting Records
      Introduction
      Creating a Delete Button
      Alternate Method of Determining the Clicked Row's Primary Key
      Adding a Client-Side Confirmation
   Setting Focus
      Setting the Focus to a Control
      Declaring our DataGrid
      Dynamically Adding Client-Side Code
   Automatic Filtering
      Changing the BindData Subroutine
      Displaying Hyperlinks for Each FAQ Category
      Removing the Hardcoded LinkButtons
   Radio Buttons
      Introduction
      Why the TemplateColumn Approach Won't Work
      Making Each Radio Button's Name Identical
      Creating a Column of Radio Buttons in a DataGrid
      Creating a Column of CheckBoxes in a DataGrid
   Bi-Directional Sorting
      Introduction
      Displaying the Contents of the Authors Table
      Writing the DataGrid's Sort Event Handler
   Displaying a Sum in a Footer
      Computing the Sum of a Column
      Displaying the Sum in the DataGrid's Footer
   Master/Detail Grids
      Introduction
      Listing the FAQ Categories
      Displaying the FAQs for a Particular Category
      Filtering Data with a DataView
   Data Paging
      Introduction
      Paging Support in the DataGrid
      Understanding Paging
      Working with Default Paging
      Customizing the Navigational Interface
   Editable Grid With Cascading DropDownLists
      Introduction
      Examining a Sample Data Model
      Displaying Employee Information in a DataGrid
      Creating the EditItemTemplate
      Populating the DropDownLists
   Fully Editable Grid
      Introduction
      Making the Entire DataGrid Editable
      Saving All Changes to the Database
   Bi-Directional Sorting Revisited
      Introduction
      Storing Sorting Information in the ViewState
      Improving the Appearance
      Conclusion (and Making this Approach Reusable)


Basics

  • To place a DataGrid on an ASP.NET Web page you simply need to add the following code. The id you choose will be the name of the DataGrid you'll use when referring to it in your server-side code.

<asp:datagrid runat="server" id="ID_of_DataGrid" /> 

  • In order to have the DataGrid display anything useful we need to bind the DataGrid to some collection of information. This collection of information can be any object that supports the IEnumerable interface, including DataReaders. (For more information on reading database results into DataReaders using ADO.NET be sure to read: Efficiently Iterating Through Results from a Database Query using ADO.NET.)

  • The following example displays the database contents in a plane-Jane HTML table - it's pretty ugly.

<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
< script language="vb" runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)
    BindData()
  End Sub	
	
  Sub BindData()
    '1. Create a connection
    Dim myConnection as New SqlConnection(
          ConfigurationSettings.AppSettings("connectionString"))

    '2. Create the command object, passing in the SQL string
    Const strSQL as String = "sp_Popularity"
    Dim myCommand as New SqlCommand(strSQL, myConnection)

    'Set the datagrid's datasource to the datareader and databind
    myConnection.Open()
    dgPopularFAQs.DataSource = myCommand.ExecuteReader(
                              CommandBehavior.CloseConnection)
    dgPopularFAQs.DataBind()	
  End Sub
</script>

<asp:datagrid id="dgPopularFAQs" runat="server" />

Display Properties

Specifying DataGrid Formatting Options

We have two options on how to specify formatting options for a DataGrid. The first option is to set the setting programmatically in the server-side script block. For example, to set the background color of the DataGrid to red you could use the following server-side code:

<%@ Import Namespace="System.Drawing" %>

< script runat="server">
  sub Page_Load(sender as Object, e as EventArgs)
    ... 
    DataGridID.BackColor = Color.Red
    ...
  end sub
</script>

The other method you can use to set display properties is to specify them in the DataGrid Web control tag. For example, the following code has the same effect as the above code:

<asp:datagrid runat="server" BackColor="Red" /> 

For the server-side code approach note that we needed to import the System.Drawing namespace and refer to the color as Color.Red.

Let's look at some of the useful formatting settings we can apply to our DataGrid.
  • BackColor - specifies the background color.
  • Font - specifies the font information for the DataGrid. Font information includes things like the font family, the point size, if its bold, italicized, etc.
  • CellPadding - specifies the cellpadding of the HTML table
  • CellSpacing - specifies the cellspacing of the HTML table
  • Width - specifies the width of the HTML table (can be in pixels, percentages, etc.)
  • HorizontalAlign - specifies how the table should be aligned (Left, Right, Center, NotSet)

An example of using these display preference properties to create a nicer looking DataGrid can be seen below. Note that the Font property of the DataGrid is, in actuality, an object reference to the FontInfo class, which contains properties like Size, Name, Bold, Italic, etc. In order to set a property of one of the classes represented by the Font we have to use a hyphen (-). This is synonymous to the "dot" in languages like VB.NET and C# when referencing an object's property.

<asp:DataGrid runat="server" id="dgFAQs" 
                BackColor="#eeeeee" Width="85%"
                HorizontalAlign="Center"
                Font-Bold="True" Font-Name="Verdana"
                Font-Size="10pt" />

DataGrid Styles

The DataGrid Web control contains a number of "styles" that you will find quite useful in customizing the look and feel of the DataGrid. There are four available styles in DataGrids:

  • HeaderStyle - Specifies the style information for the header (the top row of the table that lists the column names; you must have the DataGrid's ShowHeader property set to true (the default).)
  • FooterStyle - Specifies the style information for the footer (the bottom row of the table that lists the column names; you must have the DataGrid's ShowFooter property set to true (the default is false).)
  • ItemStyle - Specifies the style information for each row in the table.
  • AlternatingItemStyle - Specifies the style information for alternate rows in the table. You can set the ItemStyle and AlternatingItemStyle to different values for an easier-to-read DataGrid (see the below demo for an example).

Use special style blocks within the definition of the Web control tag, like so:

<asp:DataGrid runat="server">
  <HeaderStyle BackColor="Red" />
</asp:DataGrid>

The following example shows how to pretty-up our earlier example.

<asp:DataGrid runat="server" id="dgFAQs" 
                BackColor="#eeeeee" Width="85%"
                HorizontalAlign="Center"
                Font-Name="Verdana"
                Font-Size="10pt">
  <HeaderStyle BackColor="Black" ForeColor="White" 
               Font-Bold="True" HorizontalAlign="Center" />
  <AlternatingItemStyle BackColor="White" />
</asp:datagrid>

Specifying what Columns Should Appear

By default the DataGrid created a column in the HTML table for every column returned by the SQL query. If you don't want to display all of the columns from the database query in the DataGrid, you have to explicitly specify all of the columns you do wish to include. The first step is to set the DataGrid's AutoGenerateColumns property to False. Once you've done this, you need to specify the columns to show using the BoundColumn Web controls like so:

<asp:DataGrid runat="server" AutoGenerateColumns="False">
  <Columns>
    <asp:BoundColumn DataField="DatabaseColumnName1" ... />
    <asp:BoundColumn DataField="DatabaseColumnName2" ... />
    ...
    <asp:BoundColumn DataField="DatabaseColumnNameN" ... />
  </Columns>
</asp:datagrid>

For each column that you wish to display you need to specify an tag with the DataField property specifying the database column to display. All of these BoundColumn tags need to appear between the Column tags. Note that only those columns specified by BoundColumn tags will appear in the DataGrid and they will appear in the order you specify them!

BoundColumn controls contain some formatting properties, such as:
  • HeaderText - Specifies the text that should appear in the column's header.
  • FooterText - Specifies the text that should appear in the column's footer. Set ShowFooter to True if you want to display a footer in your DataGrid.
  • HeaderStyle / FooterStyle / ItemStyle - Contains all of the properties that the DataGrid's styles did. Useful for centering columns, specifying fore/background color, etc.
  • DataFormatString - Specifies formatting instructions.

As an example:
  • we want to not show the FAQID or FAQCategoryID columns
  • we'd like to perform formatting for the number column (ViewCount), as well as the date/time column (DateEntered
  • we'd like to have numeric columns have their values centered.

<asp:DataGrid runat="server" id="dgPopularFAQs" 
                BackColor="#eeeeee" Width="85%"
                HorizontalAlign="Center"
                Font-Name="Verdana" CellPadding="4"
                Font-Size="10pt" AutoGenerateColumns="False">
  <HeaderStyle BackColor="Black" ForeColor="White" 
             Font-Bold="True" HorizontalAlign="Center" />
  <AlternatingItemStyle BackColor="White" />
	  
  <Columns>
    <asp:BoundColumn DataField="CatName" HeaderText="Category Name"  />
    <asp:BoundColumn DataField="Description" HeaderText="FAQ Description" />
    <asp:BoundColumn DataField="ViewCount" DataFormatString="{0:#,###}" 
           HeaderText="Views" ItemStyle-HorizontalAlign="Center" />
    <asp:BoundColumn DataField="SubmittedByName" HeaderText="Author"  />
    <asp:BoundColumn DataField="SubmittedByEmail" HeaderText="Author's Email"  />
    <asp:BoundColumn DataField="DateEntered" HeaderText="Date Added"
			DataFormatString="{0:MM-dd-yyyy}"  />    
  </Columns>
</asp:datagrid>

  • The DataFormatString property looks a bit funny. It should always look like: {0:format string} . The {0:...} specifies to format the first argument (the value of the particular column returned by the DataReader) using the format string specified by .... In my example I used the format string #,### to format a number with commas after every three digits, and MM-dd-yyyy to format the date/time field to show the month, day, and year. Review the docs for more information on formatting strings.

Custom Events

Building on a Foundation

To place a button in the DataGrid we can use the ButtonColumn tag much in the same way as we used the BoundColumn tags. The following source code shows how to place a button in a DataGrid.

<form runat="server">
  <asp:DataGrid runat="server" id="dgPopularFAQs" 
                BackColor="#eeeeee" Width="85%"
                HorizontalAlign="Center"
                Font-Name="Verdana" CellPadding="4"
                Font-Size="10pt" AutoGenerateColumns="False">
    <HeaderStyle BackColor="Black" ForeColor="White" Font-Bold="True" 
                 HorizontalAlign="Center" />
    <AlternatingItemStyle BackColor="White" />
	  
    <Columns>
	  <asp:ButtonColumn Text="Details" HeaderText="FAQ Details" />
	  <asp:BoundColumn DataField="FAQID" Visible="False" />
      <asp:BoundColumn DataField="Description" HeaderText="FAQ Description" />
    </Columns>
  </asp:datagrid>
</form>

Note that to get this to work we needed to place the DataGrid within a server-side form (the bolded shown above). This is because in order to track the button that was clicked and the associated action that should occur, the ASP.NET page needs to be able to reconstruct the page and series of buttons in the DataGrid. To do this it needs the page's ViewState. A thorough discussion of ViewState is beyond the scope of this article; for more information read Form ViewState.

  • The ButtonType property specifies the type of button to use. The default is a textual link button. If you want to display a standard button, you can simply specify: ButtonType="PushButton" in the ButtonColumn tag.
  • The HeaderText property specifies the text that should go in the header of the DataGrid's column in which the button appears.
  • The Text property specifies the textual display for each button.
  • As with the BoundColumn tag, the ButtonColumn tag can have each button's text be the value of some column in the DataGrid's DataSource - simply omit the Text property in the ButtonColumn class and set the DataTextField property to the name of the database column whose value you wish to have displayed as the button's text.

Responding to a Button Click

Whenever a ButtonColumn button in the DataGrid is clicked, the ItemCommand event is fired. This event handler must have the following definition:

Sub eventHandlerName(sender as Object, e as DataGridCommandEventArgs) 
...
End Sub 

Once you define this function in your server-side script block (or code-behind page) you can tie the actual DataGrid event to this event handler by adding the OnItemCommand property in the DataGrid's tag, like so:

<asp:datagrid runat="server"
...
OnItemCommand="eventHandlerName">
...
</asp:datagrid> 

Determining What Row's Button was Clicked

The DataGridCommandEventArgs class contains an Item property that returns the item containing the source of the object that caused the event to fire. This item is an instance of the TableRow class corresponding to the row in the DataGrid that was clicked. You can access the columns of the TableRow class by using the Cells property, specifying the ordinal value of the column whose cell value you wish to obtain. That is, imagine that we had a DataGrid whose Columns collection was defined as follows:

<asp:DataGrid runat="server" ... >
  <Columns>
    <asp:ButtonColumn Text="Details" HeaderText="FAQ Details" CommandName="details" />
    <asp:BoundColumn DataField="FAQID" HeaderText="FAQ ID" />
    <asp:BoundColumn DataField="Description" HeaderText="FAQ Description" />
  </Columns>
</asp:datagrid>

Then, in the button click event handler, you could reference the values of the columns of the clicked row like so:

Sub detailsClicked(sender as Object, e As DataGridCommandEventArgs)
    Dim buttonColumn as TableCell = e.Item.Cells(0)
    Dim FAQIDColumn as TableCell = e.Item.Cells(1)
    Dim DescColumn as TableCell = e.Item.Cells(2)
    
    Dim buttonColText as String = buttonColumn.Text
    Dim FAQIDColText as String = FAQIDColumn.Text
    Dim DescColText as String = DescColumn.Text
End Sub

Custom Sorting

Preparing a DataGrid for Sorting

Adding support for sorting to a DataGrid can be broken down into the following three steps.

  1. Set the AllowSorting property of the DataGrid to True. If the DataGrid's AutoGenerateColumns property is set to False, specify the SortExpression property in the BoundColumn controls that represent the rows you'd like the user to be able to sort on.
  2. Create a sort event handler that repopulates the DataGrid with the sorted data, and specify the name of this sort event handler in the DataGrid's OnSortCommand event.
  3. Create a function that will allow you to grab the data from your data store in sorted format.

For the first step, you need to specify the AllowSorting property in your DataGrid, like so:

<asp:DataGrid runat="server" id="id" 
            AllowSorting="True"
            ... >
  ...
</asp:DataGrid> 

If you have the AutoGenerateColumns property set to True (the default) you've completed step 1. If, however, you have the AutoGenerateColumns property set to False, then you will need to decide what column(s) you want to allow the user to sort on. For all the columns you want to let the user sort, you must add a SortExpression property to the BoundColumn, giving the name of the column in the database.

<asp:DataGrid runat="server" id="id" 
              AutoGenerateColumns="False"
              AllowSorting="True">  
  <Columns>
    <asp:BoundColumn DataField="dbField"  />
    <asp:BoundColumn DataField="dbField2" 
                  SortExpression="dbField2"  />
  </Columns>
</asp:datagrid>

In the above example the DataGrid will have two columns, one binding to the database column dbField and the other to dbField2. The latter column will be able to be sorted while the first will not (since the latter has a SortExpression property specified while the former does not).

Creating a Sort Event Handler

By adding the AllowSorting property to True, the DataGrid, when rendered, will have in its header for each column a hyperlink. When clicked, a postback occurs and the sort event is raised. When this event is raised we'd like to have a sort event handler fire that requeries the data store, getting the data in the specified sorted order, and rebinds it to the DataGrid. In order to have this happen we need to do two things: create a sort event handler and specify the event handler for the DataGrid's sort event.

First, when creating the event handler you must use the following signature.

Sub SortEventHandler(sender as Object, e as DataGridSortCommandEventArgs)
   ...
End Sub

  • Inside your event handler you can retrieve the name of the column that was sorted via e.SortExpression.
  • If you explicitly specified the SortExpression property in a BoundColumn, then the value of e.SortExpression is the value of the SortExpression property; if you did not specify a SortExpression property (because you had AutoGenerateColumns set to True), then the value of e.SortExpression is the value of the database column name that represents the column clicked.
  • To associate your sort event handler with the DataGrid's sort event, simply set the DataGrid's OnSortCommand property to the name of the sort event handler you created, like this.

<asp:DataGrid runat="server" id="id" 
                AllowSorting="True"
                OnSortCommand="NameOfSortEventHandler">
  ...
</asp:DataGrid>

Creating a Sorting Function

We would like to have a function that we can pass a sort expression to, that then retrieves the data in sorted order and binds the data directly to the DataGrid.

Sub SortData(sender as Object, e as DataGridSortCommandEventArgs)
    BindData(e.SortExpression)
End Sub 

When the page is first loaded we need to sort the DataGrid's data based on some default value. Hence, in the Page_Load event handler we need to call BindData("default-sort-expression"), but only on the first page load. Realize that the data is rebound whenever the user opts to sort by a particular column (which induces a postback), so we don't want to also bind the data in the Page_Load event handler.

Specifying the Sort Order Using a Stored Procedure

If you have a stored procedure written that returns the rows of a particular table, how can you indicate to the stored procedure to reorder the results differently? Read Dynamic ORDER BY Statements in Stored Procedures for information on how to accomplish this task.

Template Columns

Using a TemplateColumn

With the DataGrid you can specify TemplateColumns on a per-column basis. As with BoundColumns, to use TemplateColumns you need to set the DataGrid's AutoGenerateColumns to False first. Then, you can place a TemplateColumn control in the tag for each templated column you wish to display. Note that you can also have BoundColumns in the tag as well; that is, a DataGrid may be made up of both BoundColumns and TemplateColumns. A TemplateColumn control generates one column for each row in the DataGrid. The HTML output for that row is specified by tags inside of the TemplateColumn control.

The TemplateColumn control itself has some UI-related properties

  • HeaderText - specifies the HTML content to appear in the column's header
  • Visible - specifies if the column is rendered or not.

There are four tags can appear inside of the TemplateColumn.

  • ItemTemplate - specifies the template for each row for the particular column the TemplateColumn represents.
  • HeaderTemplate - specifies the template for the column's header.
  • FooterTemplate - specifies the template for the column's footer.
  • EditItemTemplate - specifies the template for the a cell of a particular row that has been selected for editing. This can occur when using the DataGrid's Edit/Update/Delete features - see below for details on these features.

These templates can contain vanilla HTML as well as data-bound values. For example, a very simple TemplateColumn might simply output the value of a database column in bold text. To output a dynamic data value from the DataGrid's DataSource, use the following syntax:

<%# DataBinder.Eval(Container.DataItem, "ColumnName") %> 

This is known as a "databinding" command. Essentially it says, "Find the ColumnName column in the DataItem (the DataItem being the current row of the DataGrid's DataSource. Hence, our DataGrid's TemplateColumn would contain an ItemTemplate with a single line of code (the databinding statement shown above), which would look like:

<asp:TemplateColumn HeaderText="FAQ Information">
  <ItemTemplate>
    <b><%# DataBinder.Eval(Container.DataItem, "ColumnName") %></b>
  </ItemTemplate>
</asp:TemplateColumn>

TemplateColumn versus a BoundColumn

Let's look at a more complex example. Imagine that I wanted to have the DataGrid's output to have just two columns in total. The first column would list the FAQ's ID (FAQID), while the second column would contain inside of it an HTML table of its own, which would display the Category Name, Description, and ViewCount of the particular FAQ. Using TemplateColumns, this is fairly simple, as can be seen by the code below:

<asp:datagrid id="dgPopularFAQs" runat="server"
	AutoGenerateColumns="False">	
  <Columns>
    <asp:BoundColumn DataField="FAQID" ItemStyle-Width="10%" 
			ItemStyle-HorizontalAlign="Center" HeaderText="FAQ ID" />
	    
    <asp:TemplateColumn HeaderText="FAQ Information">
      <ItemTemplate>
        <table border="0">
          <tr>
            <td align="right"><b>Description:</b></td>
            <td><%# DataBinder.Eval(Container.DataItem, "Description") %></td>
          </tr>
          <tr>
            <td align="right"><b>Category Name:</b></td>
            <td><%# DataBinder.Eval(Container.DataItem, "CatName") %></td>
          </tr>
          <tr>
            <td align="right"><b>View Count:</b></td>
            <td><%# DataBinder.Eval(Container.DataItem, "ViewCount", "{0:#,###}") %>
            </td>
          </tr>
        </table>
      </ItemTemplate>
    </asp:TemplateColumn>
  </Columns>	
</asp:datagrid>

Note that the last data-bound statement uses an alternate form of DataBinder.Eval - it included an optional third parameter that specifies a format string for the column. The formatting string #,### specifies that ever three digits should be separated by a comma. (The {0:formatString} syntax may seem a bit confusing - it simply states that the formatting string formatString should be applied to the 0th argument (i.e., the value housed in the current DataItem's ViewCount column).

Default Editing

First Things First: Displaying the Data

For this example I will be using the GrocerToGo database, specifically displaying (and allowing the user to edit) the data in the Products table. From the Products table the DataGrid will display the following columns: ProductID, UnitPrice, ProductName, and ProductDescription. Some UI-formatting code is also employed to make the DataGrid look nice. The following code displays the Products table in the DataGrid:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
< script runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)
   If Not Page.IsPostBack
      BindData()
   End If
  End Sub


  Sub BindData()
    '1. Create a connection
    Const strConnStr as String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                        "Data Source=C:\GrocerToGo.mdb"
    Dim objConn as New OleDbConnection(strConnStr)
    objConn.Open()
      
    '2. Create a command object for the query
    Const strSQL as String = "SELECT * FROM Products"
    Dim objCmd as New OleDbCommand(strSQL, objConn)
      
    '3. Create/Populate the DataReader
    Dim objDR as OleDbDataReader
    objDR = objCmd.ExecuteReader()    
      
    dgProducts.DataSource = objDR
    dgProducts.DataBind()   
  End Sub
</script>

<asp:DataGrid id="dgProducts" runat="server"
    AutoGenerateColumns="False" CellPadding="4"
    HeaderStyle-BackColor="Black"
    HeaderStyle-ForeColor="White"
    HeaderStyle-HorizontalAlign="Center"
    HeaderStyle-Font-Bold="True">
        
    <Columns>
        <asp:BoundColumn HeaderText="Product ID" DataField="ProductID" />
        <asp:BoundColumn HeaderText="Price" DataField="UnitPrice"
                ItemStyle-HorizontalAlign="Right"
                DataFormatString="{0:$#,###.##}" />
        <asp:BoundColumn HeaderText="Name" DataField="ProductName" />
        <asp:BoundColumn HeaderText="Description" 
                DataField="ProductDescription" />                        
    </Columns>        
</asp:DataGrid>

Notice the Page_Load Event Handler!

Notice that the Page_Load event handler for the editable DataGrid only calls the BindData() subroutine when the page is first visited. That is, it is not called on subsequent postbacks. This is vital! If you change the Page_Load event handler so that it calls BindData() every time, your edited values will not be saved in the database. For a thorough explanation as to why this is the case, be sure to read the FAQ: Why Your DataGrid's Updates Don't Show Up.

Allowing the End User to Edit a Row

To allow the user to edit a row in the DataGrid, the DataGrid provides a control called EditCommandColumn, which displays an "Edit" button next to each row in the DataGrid. When clicked, the button causes a postback and the EditCommand event to be fired. As you can see in the code below, you simply add the EditCommandColumn control like you would a BoundColumn or ButtonColumn control.

<asp:DataGrid id="dgProducts" runat="server"
   AutoGenerateColumns="False" CellPadding="4"
   HeaderStyle-BackColor="Black"
   HeaderStyle-ForeColor="White"
   HeaderStyle-HorizontalAlign="Center"
   HeaderStyle-Font-Bold="True">
        
   <Columns>
    <asp:EditCommandColumn EditText="Edit Info" 
          ButtonType="PushButton"
          UpdateText="Update" CancelText="Cancel" />

       <asp:BoundColumn HeaderText="Product ID" DataField="ProductID"
               ReadOnly="True" />                
       <asp:BoundColumn HeaderText="Price" DataField="UnitPrice"
               ItemStyle-HorizontalAlign="Right"
               DataFormatString="{0:$#,###.##}" />
       <asp:BoundColumn HeaderText="Name" DataField="ProductName" />
       <asp:BoundColumn HeaderText="Description" 
               DataField="ProductDescription" />                        
   </Columns>        
</asp:DataGrid>

The EditCommandColumn control has a number of optional properties.

  • ButtonType - specifies if a hyperlink (LinkButton, the default) or push button (PushButton) should be used
  • EditText, UpdateText, and CancelText - the text for the Edit, Update, and Cancel buttons, respectively.
  • HeaderText, ItemStyle, etc.

The EditCommandColumn control provides an Edit button for each row except for the row that is being currently edited. For that row, the EditCommandColumn control shows two buttons, an "Update" and "Cancel" button.

Selecting a Row to be Edited

The DataGrid contains a property called EditItemIndex, which specifies what row (zero-based) of the DataGrid is the row being edited. By default the DataGrid is not editing any row, so by default the EditItemIndex has a value of -1. Since we want to mark a row for editing when its Edit button is clicked, we simply need to write some code in the EditCommand event handler to set the EditItemIndex property to the row whose Edit button was clicked and then rebind the DataGrid data (by calling BindData()). The code for this event handler can be seen below.

Sub dgProducts_Edit(sender As Object, e As DataGridCommandEventArgs)
    dgProducts.EditItemIndex = e.Item.ItemIndex
    BindData()
End Sub

To wire this event handler up to the EditCommand event, simply specify this in your DataGrid control, like so:

<asp:DataGrid id="dgProducts" runat="server"
   ...   
   OnEditCommand="dgProducts_Edit"
   ... >
        
   <Columns>
       ...
   </Columns>        
</asp:DataGrid>

Note that with this code when the user clicks the "Edit" button the Web page will be posted back and the DataGrid row whose "Edit" button was clicked will have, in place of the "Edit" button, an "Update" and "Cancel" button. Furthermore, the data values in the cells in the DataGrid for the edited row have automatically changed from a textual value to a value in an editable textbox.

For situations where you want to make certain parts of the data un-editable (as with a primary key column), simply set the ReadOnly property to True for the particular BoundColumn control.

<asp:DataGrid id="dgProducts" runat="server"
   ... >
        
   <Columns>
      <asp:BoundColumn HeaderText="Product ID" DataField="ProductID" 
            ReadOnly="True" />
      ...
   </Columns>        
</asp:DataGrid>

You may have also noticed in the screenshot that the row being edited is shaded a different color than the rest. You can specify all sorts of different stylistic features for the row being edited by using the EditItemStyle property of the DataGrid. Simply set this property in the DataGrid control like you would for the HeaderStyle or ItemStyle, that is.

<asp:DataGrid id="dgProducts" runat="server"
   ...   
   EditItemStyle-BackColor="#eeeeee"
   ... >
        
   <Columns>
       ...
   </Columns>        
</asp:DataGrid>

The Cancel Button

The intention of the "Cancel" button is to return the DataGrid to its non-editing state without saving any changes. When clicked, the "Cancel" button fires the CancelCommand event. The event handler for this event will simply need to set the DataGrid back to its pre-editing state by setting the EditItemIndex property back to -1 and rebind the DataGrid (via BindData()):

Sub dgProducts_Cancel(sender As Object, e As DataGridCommandEventArgs)
    dgProducts.EditItemIndex = -1
    BindData()
End Sub

The Update Button

When clicked, the "Update" button fires the UpdateCommand event. The "Update" button's event handler must read in the values the user has entered into the textboxes and then execute an applicable stored procedure or SQL statement. The "Update" button's event handler accepts two incoming parameters, an Object and a DataGridCommandEventArgs. The DataGridCommandEventArgs parameter contains a property Item, which is an instance of the DataGridItem that corresponds to the DataGrid row whose "Update" button was clicked. This DataGridItem object contains a Cells collection, which can be interrogated to retrieve the text or controls at the various columns of the DataGrid. We can use this DataGridItem object to determine the values of the edited row's ProductID as well as the values the user has entered into the textboxes for the price, name, and description.

Sub dgProducts_Update(sender As Object, e As DataGridCommandEventArgs)
   'Read in the values of the updated row
   Dim iProductID as Integer = e.Item.Cells(1).Text
   Dim dblPrice as Double = CType(e.Item.Cells(2).Controls(0), TextBox).Text
   Dim strName as String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
   Dim strDesc as String = CType(e.Item.Cells(4).Controls(0), TextBox).Text

    ...

Note that the value of the ProductID, which was not a textbox in the edited mode (since it was marked ReadOnly), can be retrieved by the Text property of e.Item.Cells(1). Cells(1) is used to get the second TableCell in the DataGrid row. The first TableCell (which would be referenced via Cells(0)) is the column that contains the "Update" and "Cancel" buttons.

Retrieving the price, name, and description is a bit more difficult due to the fact that the values we are after are the textual content inside the textboxes inside the table cells. Hence we use the CType function to cast the first control (Controls(0)) in the appropriate TableCell to a TextBox Web control; then, we simply interrogate the TextBox control's Text property. Once we have these values we can construct our SQL UPDATE statement.

...
        
    'Construct the SQL statement using Parameters
    Dim strSQL as String = _
      "UPDATE [Products] SET [ProductName] = @ProdName, " & _
      "[UnitPrice] = @UnitPrice, [ProductDescription] = @ProdDesc " & _
      "WHERE [ProductID] = @ProductID"

    Const strConnString as String = _
       "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\GrocerToGo.mdb"
    Dim objConn as New OleDbConnection(strConnString)
    objConn.Open()

    Dim myCommand as OleDbCommand = new OleDbCommand(strSQL, objConn)
    myCommand.CommandType = CommandType.Text

    ' Add Parameters to the SQL query
    Dim parameterProdName as OleDbParameter = _
               new OleDbParameter("@ProdName", OleDbType.VarWChar, 75)
    parameterProdName.Value = strName
    myCommand.Parameters.Add(parameterProdName)

    Dim parameterUnitPrice as OleDbParameter = _
               new OleDbParameter("@UnitPrice", OleDbType.Currency)
    parameterUnitPrice.Value = dblPrice
    myCommand.Parameters.Add(parameterUnitPrice)

    Dim parameterProdDesc as OleDbParameter = _
               new OleDbParameter("@ProdDesc", OleDbType.VarWChar)
    parameterProdDesc.Value = strDesc
    myCommand.Parameters.Add(parameterProdDesc)

    Dim parameterProdID as OleDbParameter = _
               new OleDbParameter("@ProductID", OleDbType.Integer)
    parameterProdID.Value = iProductID
    myCommand.Parameters.Add(parameterProdID)

    myCommand.ExecuteNonQuery()   'Execute the UPDATE query
    
    objConn.Close()   'Close the connection
  
  ...

Now that the database has been updated with the user's edits, all that remains is to return the DataGrid to its pre-editing form (by setting EditItemIndex to -1) and then rebinding the DataGrid (via BindData()).

...
   
   'Finally, set the EditItemIndex to -1 and rebind the DataGrid
    dgProducts.EditItemIndex = -1
    BindData()        
End Sub

Finally, to wrap up the code, you'll need to specify that the CancelCommand and UpdateCommand events be wired up to the appropriate event handlers by inserting the following code into your DataGrid control:

<asp:DataGrid id="dgProducts" runat="server"
   ...   
   OnUpdateCommand="dgProducts_Update"
   OnCancelCommand="dgProducts_Cancel"
   ... >
        
   <Columns>
       ...
   </Columns>        
</asp:DataGrid>

Custom Editing

The EditItemTemplate

In order to customize the DataGrid's editing interface you have to be using a TemplateColumn. If you use a BoundColumn to represent a column in the DataGrid, the editing interface will be the default TextBox.

In this article we will work through a real-world example. In the previous articles in this series, we've used live demos against the FAQ database over at ASPFAQs.com. This database is comprised of a number of tables, the main one being tblFAQ, which has a row for each FAQ. One of the columns in the tblFAQ table is called FAQCategoryID, and is a foreign key to the tblFAQCategory table, which has a row for each FAQ category (these categories include Array, Application Object, Email, ASP.NET, Forms, etc.). Specifically, the important parts of these tables are defined as follows. (Remaining columns have been ommited for brevity.)

tblFAQ
ColumnData TypeDescription
FAQIDintegerprimary key
Descriptionvarchar(255)the "question" part of the FAQ
FAQCategoryIDintegerforeign key to the tblFAQCategory table

tblFAQCategory
ColumnData TypeDescription
FAQCategoryIDintegerprimary key
Namevarchar(255)the "title" of the category

    <asp:TemplateColumn HeaderText="Category">
      <ItemTemplate>
        <%# DataBinder.Eval(Container.DataItem, "CategoryName") %>
      </ItemTemplate>
      
      <EditItemTemplate>
        <asp:DropDownList runat="server" id="lstCategories"
                DataValueField="FAQCategoryID"
                DataTextField="Name"
                DataSource="???" />
       </EditItemTemplate>                
    </asp:TemplateColumn>

Since this DropDownList will contain a list of the existing FAQ categories, we will be using databinding to bind the DropDownList to a DataSource that is comprised of the contents of the tblFAQCategory table. When using a databound DropDownList, you need to specify the what column from the DataSource you wish to have displayed as the text for each DropDownList option, and what value you want tied to these textual labels. Since we want to display a DropDownList item for each row in the tblFAQCategory table, it makes sense to have the name of the category (the Name column) displayed as the textual part of the DropDownList item, and the actual FAQCategoryID as the value of the DropDownList item.

Specifying the DropDownList's DataSource

Of course, realize that while we've specified the columns to bind to the DropDownList, we've yet to specify where the DropDownList's DataSource is coming from. Essentially, we need to construct a DataSet with the rows from the tblFAQCategory table.

Essentially, we want to use our familiar databinding syntax to specify the DropDownList's DataSource. Specifically, we'll use a function that returns a DataSet that is full of the contents of the tblFAQCategory table. Our databinding syntax looks like:

    <asp:TemplateColumn HeaderText="Category">
      <ItemTemplate>
        <%# DataBinder.Eval(Container.DataItem, "CategoryName") %>
      </ItemTemplate>
      
      <EditItemTemplate>
        <asp:DropDownList runat="server" id="lstCategories"
                DataValueField="FAQCategoryID"
                DataTextField="Name"
                DataSource="<%# GetCategories() %>" />
       </EditItemTemplate>               
    </asp:TemplateColumn>

The GetCategories() function, which we've yet to write, will simply need to return a DataSet full of the rows from the tblFAQCategory table. This function is shown below.

<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
< script language="vb" runat="server">
  'Create a connection
  Dim myConnection as New SqlConnection(connString)
  Dim ddlDataSet as DataSet = New DataSet()

  Function GetCategories() as DataSet
    'Populate the ddlDataSet
    Const strSQLDDL as String = _
         "SELECT FAQCategoryID, Name FROM tblFAQCategory ORDER BY Name"    
    
    Dim myDataAdapter as SqlDataAdapter = New _
                          SqlDataAdapter(strSQLDDL, myConnection)    
    
    myDataAdapter.Fill(ddlDataSet, "Categories")

    Return ddlDataSet
  End Function

  ...

Be sure to take note that both the connection and the DataSet ddlDataSet are defined globally to the page, meaning that any function in this ASP.NET Web page can access these objects. The reason the connection object is defined globally is because we have two functions now - GetCategories() and BindData() - that grab database information. Rather than creating, opening, and closing separate connection objects for each of the two functions, we work with just the one, globally-defined one. Note that the connection closes itself once it goes out of scope when the page has completed rendering. Don't worry if the rationale behind the globally-defined DataSet isn't immediately apparent - it will become clear soon enough!

Our GetCategories() function is fairly straightforward. We simply fill the ddlDataSet DataSet with the results from a simple SQL query that grabs the entire contents of the tblFAQCategory table, and return the filled DataSet.

Selecting the Correct Item in the DropDownList

In order to ensure that the DropDownList has the correct selected item when it enters edit mode, we need to programmatically set the DropDownList's SelectedIndex property. For this we have to use the databinding syntax we used to specify the DataSource property. Specifically, we need to call a function and pass in the value of the selected FAQ's FAQCategoryID. This is accomplished by using the following markup in the EditItemTemplate:

<asp:TemplateColumn HeaderText="Category">
  <ItemTemplate>
    <%# DataBinder.Eval(Container.DataItem, "CategoryName") %>
  </ItemTemplate>
      
  <EditItemTemplate>
    <asp:DropDownList runat="server" id="lstCategories"
         DataValueField="FAQCategoryID"
         DataTextField="Name"
         DataSource="<%# GetCategories() %>" 
         SelectedIndex='<%# GetSelIndex(Container.DataItem("FAQCategoryID")) %>'
    />
   </EditItemTemplate>               
</asp:TemplateColumn>

The GetSelIndex() function, which we've yet to write, accepts the FAQCategoryID value from the row that's being edited — this is why in our SQL statement that populates the DataGrid we get the FAQCategoryID column from the tblFAQ table, even though we don't display it in the DataGrid — and returns an integer that corresponds to the item in the DropDownList that needs to be selected. Since we know that the DataSet ddlDataSet is used to populate the DropDownList, we know that there is a one-to-one correspondence between the index of the rows in the ddlDataSet DataSet and the item indexes in the DropDownList. Therefore, our GetSelIndex() function simply needs to iterate through this DataSet, comparing each DataSet row's value to the passed in FAQCategoryID. Once it finds a match, it can then return the row's index, which will then be assigned to the DropDownList's SelectedIndex property.

Function GetSelIndex(CatID as String) as Integer
  Dim iLoop as Integer
  
  'Loop through each row in the DataSet
  Dim dt as DataTable = ddlDataSet.Tables("Categories")
  For iLoop = 0 to dt.Rows.Count - 1
    If Int32.Parse(CatID) = _
          Int32.Parse(dt.Rows(iLoop)("FAQCategoryID")) then
      Return iLoop
    End If
  Next iLoop
End Function

Retrieving the DropDownList's Value

In order to update the database with the changes made during the editing phase you will need to provide an event handler for the OnUpdateCommand event that updates the database with the values entered by the user. To retrieve the user-entered value from the DropDownList we need to get an instance of the DropDownList control via the FindControl() method. We then need to cast it to a DropDownList instance; at this point we can access the SelectedItem.Value property, which has the FAQCategoryID of the DropDownList option selected by the user. You'll likely note that the code is very similar to the code used for extracting the value out of the default editing interface TextBoxes. The code for the OnUpdateCommand event handler follows.

  Sub dgPopFAQs_Update(sender As Object, e As DataGridCommandEventArgs)
    'Determine what category was selected
    Dim strCategoryID as String, strCategoryName as String    
    strCategoryID = CType(e.Item.FindControl("lstCategories"), _
                          DropDownList).SelectedItem.Value
    strCategoryName = CType(e.Item.FindControl("lstCategories"), _
                            DropDownList).SelectedItem.Text
    
    ... Make a SQL call to update the database ...
  
    'Return the DataGrid to its pre-editing state
    dgPopularFAQs.EditItemIndex = -1
    BindData()
  End Sub

Note that you will also need to add a line to your DataGrid Web control to wire up the OnUpdateCommand to the above event handler.

Deleting Records

Introduction

In addition to allowing a user to edit data, one may want to allow the user to delete data. This can be accomplished by adding a ButtonColumn control that contains a Delete button, which will add a Delete button to each row of the DataGrid. When a user clicks the Delete button for a particular row, that row will then be deleted from the database. To accomplish this we have to perform the following tasks:

  1. Create a ButtonColumn that contains a Delete button.
  2. Somehow be able to determine when the Delete button has been clicked and have some server-side code ready to execute.
  3. Be able to determine the primary key field value for the row whose Delete button has been clicked. We need to primary key field value so that we can issue a SQL statement to delete the selected row.

In addition to examining how to accomplish the above three steps, this article will also look at how to add a client-side confirm dialog box to the Delete button. That is, when a user clicks the Delete button a client-side messagebox will appear, asking the user if they are sure if they want to delete the item. If they click OK, the row will be deleted; if they click cancel, nothing will happen.

Creating a Delete Button

While we could place our delete code in the ItemCommand event handler, the DataGrid control offers a special event handler for delete buttons: the DeleteCommand event handler. In order to create a ButtonColumn that triggers the DeleteCommand event handler you must set the ButtonColumn's CommandName property to "Delete". Once you do this, you will want to create an event handler for the DeleteCommand event. This event handler takes the form:

Sub eventHandlerName(sender as Object, e as DataGridCommandEventArgs) 
...
End Sub 

In this event handler we'll place the code to make a database call to delete the specified DataGrid item. Finally, we must set the DataGrid's OnDeleteCommand property to the event handler in the DataGrid's declaration like so: OnDeleteCommand="eventHandlerName".

Below you can see a simple example that illustrates adding a Delete button to a DataGrid, adding an event handler for the DeleteCommand event, and wiring up this event handler to the DataGrid's DeleteCommand event:

< script language="vb" runat="server">
  ...

  Sub dgPopularFAQs_Delete(sender As Object, e As DataGridCommandEventArgs)
    ' Place code to perform delete here...
  End Sub
</script>

<form runat="server">
  <asp:datagrid id="dgPopularFAQs" runat="server"
  	  ...
      OnDeleteCommand="dgPopularFAQs_Delete">
	
    <Columns>
  	<asp:ButtonColumn Text="Delete" CommandName="Delete" />
  	<asp:BoundColumn DataField="FAQID" HeaderText="FAQ ID"
  	     ItemStyle-HorizontalAlign="Center" />
  	<asp:BoundColumn DataField="Description" HeaderText="Question" />
    </Columns>	
  </asp:datagrid>
</form>

It is important to set the ButtonColumn's CommandName property to "Delete", otherwise the DataGrid's DeleteCommand event won't fire when the command button is clicked. Rather, just the DataGrid's ItemCommand event will fire. Also note that in order to have the dgPopularFAQs_Delete event handler execute when the DataGrid's DeleteCommand event fires we had to specify OnDeleteCommand="dgPopularFAQs_Delete" in the DataGrid's declaration.

Alternate Method of Determining the Clicked Row's Primary Key

In order to issue a database command to delete the selected item from the DataGrid, we must be able to uniquely identify the selected item. Usually this takes the form of a numeric primary key field. In the live demos for this article, we're using the ASPFAQs.com database, and the primary key for each FAQ is a database field called FAQID.

The DataGrid control contains a DataKeyField property. This optional property can be used to specify the primary key field for the data being displayed in the DataGrid. If this property is set, a separate DataGrid property, DataKeys, a collection, is populated with the primary key values for each row in the DataGrid. Hence, we can access this collection programmatically in our DeleteCommand event handler. To get the proper item out of the DataKeys collection, we simply reference the index that is equal to the clicked DataGrid row's ItemIndex. This concept is illustrated below:

< script language="vb" runat="server">
  ...

  Sub dgPopularFAQs_Delete(sender As Object, e As DataGridCommandEventArgs)
    'Get the FAQID of the row whose Delete button was clicked
    Dim SelectedFAQID as String = dgPopularFAQs.DataKeys(e.Item.ItemIndex)
    
    'TODO: Delete the record from the database
    
    'TODO: Rebind the DataGrid
  End Sub
</script>

<form runat="server">
  <asp:datagrid id="dgPopularFAQs" runat="server"
  	  ...
      DataKeyField="FAQID">

    ...	
  </asp:datagrid>
</form>

Remember to recompute the DataGrid's DataSource and call the DataGrid's DataBind() method. This is needed because the DataSource has changed: a row has been deleted.

Adding a Client-Side Confirmation

To provide a level of protection, we can use some client-side script to pop up a JavaScript confirm messagebox asking the user if they really want to delete the item.

The JavaScript confirm() Function

JavaScript contains a confirm() function takes a string parameter as an input. The confirm() function has the effect of displaying a modal dialog box whose message is that of the string parameter, and whose buttons include OK and Cancel. If the user clicks OK, the confirm() function returns a value of True; a value of False is returned if the user clicks Cancel. For more information on the JavaScript confirm() function see: JavaScript Confirm Form Submissions.

To have the JavaScript confirm function run when the Delete button is clicked, we need to add the needed JavaScript code to the Delete button's client-side onclick event. The message for the confirm dialog box should read: "Are you sure you want to delete FAQ #FAQID?", where FAQID is the FAQID value for the FAQ whose Delete button was clicked.

In order to specify client-side JavaScript code in an ASP.NET Web control's client-side event set, we must use the control's Attributes collection. For example, if we had a Button control, we could use the following server-side code to add some client-side JavaScript code to the Button's client-side onmouseover event:

< script language="vb" runat="server">
  Sub Page_Load(sender As Object, e As EventArgs)
    'Set the button's client-side onmouseover event
    btnClick.Attributes("onmouseover") = "alert('Hello, World!');"
  End Sub
</script>

<form runat="server">
  <asp:button runat="server" Text="Click Me!" id="btnClick" />
</form>

Now, with our example we need a JavaScript confirm() function call to the Delete button's client-side onclick event. First, realize that the Delete button is not a Button class exactly, but a LinkButton class. The DataGrid's ButtonColumn control can be rendered as either LinkButtons (the default) or Buttons, depending on the value of the ButtonControl's ButtonType property. (To make a ButtonColumn class contain buttons, set ButtonType to PushButton.)

Also, notice that in our live demo the Button's client-side onmouseover event is added to the control's Attributes collection in the Page_Load event handler. However, for the Delete button in our example we cannot use the Page_Load event handler since the Delete buttons have not been rendered by this point. Furthermore, we need to know the FAQID value for the particular row so that we can tailor our confirm messagebox's string to include the item's FAQID value.

When the DataGrid's DataBind() method is called, the DataGrid enumerates through the items of the DataSource, creating a row for each item. The DataGrid fires the ItemDataBound event after each added DataGrid row has been databound to the particular DataSource item. We can write an event handler for this event. Since the DataGrid has just added a row, we can programmatically access the Delete LinkButton that has been generated by the ButtonColumn, tweaking the Attributes collection so to add a client-side onclick event.

To provide an event handler for the DataGrid's ItemDataBound event, we must use the following signature:

Sub eventHandlerName(sender as Object, e as DataGridItemEventArgs) 
...
End Sub 

Furthermore, we must set the DataGrid's OnItemDataBound property to the event handler name we choose. In our event handler, we need to programmatically reference the LinkButton generated by the Delete ButtonColumn control and then set its client-side onclick event via the Attributes collection:

< script language="vb" runat="server">
  ...
   
  Sub dgPopularFAQs_ItemDataBound(sender as Object, e as DataGridItemEventArgs)
    ' First, make sure we're NOT dealing with a Header or Footer row
    If e.Item.ItemType <> ListItemType.Header AND _
         e.Item.ItemType <> ListItemType.Footer then
      'Now, reference the LinkButton control that the Delete ButtonColumn 
      'has been rendered to
      Dim deleteButton as LinkButton = e.Item.Cells(0).Controls(0)

      'We can now add the onclick event handler
      deleteButton.Attributes("onclick") = "javascript:return " & _
                 "confirm('Are you sure you want to delete FAQ #" & _
                 DataBinder.Eval(e.Item.DataItem, "FAQID") & "?')"    
    End If
  End Sub
</script>

<form runat="server">
  <asp:datagrid id="dgPopularFAQs" runat="server"
      ...
      OnItemDataBound="dgPopularFAQs_ItemDataBound">
	
      ...
  </asp:datagrid>
</form>

Realize that for every row that is added to the DataGrid the ItemDataBound event is fired. This includes the Header and Footer rows of the DataGrid. Hence, in the dgPopularFAQs_ItemDataBound event handler we start by ensuring that we are not dealing with either the Header or Footer. If this is the case, we go ahead and reference the LinkButton by referring to the DataGridItem's first column (i.e., e.Items.Cells(0)), grabbing the first control in the column, the LinkButton (i.e., Controls(0)). Once we have this LinkButton referenced as a local variable we can easily set its Attributes property, as shown in the code above. Note that we add the needed JavaScript code, inserting a dynamic value (the FAQID) in the string parameter to the confirm function. We grab the FAQID field of the current DataSource item (which is stored in the variable DataGridItem's DataItem property) by using the DataBinder.Eval method.

One thing to note is that the confirm() function needs to return its value for it to have the desired semantic effect. That is, note that in the JavaScript code we don't just say: confirm('Are you sure you want ...');, but rather: return confirm('Are you sure you want ...');. If you forget this return the DataGrid item will be Deleted when the user click's the Delete button regardless of if they hit OK or Cancel on the client-side confirm() messagebox.

Setting Focus

Setting the Focus to a Control

Before we delve into how we will set the focus of a TextBox Web control in a DataGrid, we should quickly discuss how one sets focus to a Web page input field. By input field I mean some sort of HTML element generated by the HTML INPUT tag. For example, the following HTML markup creates a TextBox.

<input type="text" id="textBoxID" />

To set the focus of an input field we need to use client-side script. Specifically, we call the input field's focus() method. To have this occur when the page is loaded, we can wire up the BODY tag's onload event to some client-side event handler that contains code that calls the input field's focus() method. For example, the following simple HTML page demonstrates how to accomplish this:

<html>
<head>
  < script language="JavaScript">
    function setFocus()
    {
      frmInfo.name.focus();
    }
  </script>
</head>
<body onload="setFocus();">
  <form id="frmInfo">
    Name: <input type="text" id="name" /><br />
    Age:  <input type="text" id="age" />
  </form>
</body>
</html>

Declaring our DataGrid

In order to set the focus to a TextBox in the EditItemTemplate, we first must create a DataGrid that has editing capabilities and an EditItemTemplate with a TextBox. Our example for this article will use the ASPFAQs.com database. Specifically, the DataGrid will display an "Edit" column along with the FAQID (marked readonly) and the FAQ question. Thus our DataGrid's declaration will look like.

<form runat="server" id="frmEditFAQs">
  <asp:datagrid id="dgPopularFAQs" runat="server"
    AutoGenerateColumns="False"
    ...		
    OnEditCommand="dgPopularFAQs_Edit"
    OnUpdateCommand="dgPopularFAQs_Update"
    OnCancelCommand="dgPopularFAQs_Cancel">
	
    <Columns>
      <asp:EditCommandColumn EditText="Edit" UpdateText="Update" 
                              CancelText="Cancel" />
  	  <asp:BoundColumn DataField="FAQID" HeaderText="FAQ ID"
  	       ItemStyle-HorizontalAlign="Center" ReadOnly="True" />
  	  <asp:TemplateColumn HeaderText="Question">
        <ItemTemplate>
          <%# DataBinder.Eval(Container.DataItem, "Description") %>
        </ItemTemplate>
		  
        <EditItemTemplate>
          <asp:TextBox id="txtDesc" runat="server" Width="95%"
             Text='<%# DataBinder.Eval(Container.DataItem, "Description") %>' />
        </EditItemTemplate>
      </asp:TemplateColumn>
    </Columns>	
  </asp:datagrid>
</form>

As we'll see shortly, the ID properties of all the server controls are quite important, for they will be used to identify the control in our client-side code. Also note that the EditItemTemplate contains a TextBox Web control with an ID of txtDesc. Its Text property is set, via data binding syntax, to the value of the Description field from the DataGrid's DataSource.

In order to have the EditItemTemplate's TextBox receive focus when the row is selected for editing, we'll need some way to add the appropriate client-side script to the ASP.NET Web page.

Dynamically Adding Client-Side Code

When one of the rows of the DataGrid is selected for editing, we need some way to insert client-side code. Recall that we want to do this all within a client-side JavaScript function. One way to accomplish this is to use the Page class's RegisterStartupScript method. This method takes two string parameters, a key and the client-side script to emit. It then adds a client-side script block at the end of the ASP.NET page and includes in it the client-side script provided. With this approach we would not use an onload event handler as we did above. Rather, we'd only call the RegisterStartupScript method when the focus needed to be set, since the client-side JavaScript would only be emitted when this method call was made.

When the "Edit" button is clicked we need to emit client-side code that will set the focus to the TextBox in the EditItemTemplate. We can then provide an event handler for this EditCommand event. Inside of this event handler we want to, as always, set the DataGrid's EditItemIndex property and rebind the DataGrid. Furthermore, after we've performed these first two steps, we want to emit the necessary client-side code to set the focus to the EditItemTemplate's TextBox. In order to set the focus we need to grab the Web form's ID and the TextBox's ClientID. We can then emit JavaScript like this: FormID.TextBoxID.focus();.

Below you can see the code for the DataGrid's EditCommand event handler.

Sub dgPopularFAQs_Edit(sender as Object, e as DataGridCommandEventArgs)
  dgPopularFAQs.EditItemIndex = e.Item.ItemIndex
  BindData()
    
  ' Create a reference to the TextBox
  Dim descTB as TextBox
  descTB = dgPopularFAQs.Items(e.Item.ItemIndex).Cells(2).FindControl("txtDesc")

  'Set the script to focus and select the TextBox
  RegisterStartupScript("focus", "< script language=""JavaScript"">" & vbCrLf & _
       vbTab & "frmEditFAQs." & descTB.ClientID & ".focus();" & _
       vbCrLf & vbTab & "frmEditFAQs." & descTB.ClientID & ".select();" & _
       vbCrLf & "<" & "/script>")
End Sub

You may be wondering why we are using dgPopularFAQs.Items(e.Item.ItemIndex).Cells(2).FindControl("txtDesc") and not just e.Item.Cells(2).FindControl("txtDesc"). This is because when the e.Item DataGridItem instance was passed in, the row was not in its editing form, meaning that the TextBox specified in the EditItemTemplate is not present. After we call the BindData() method the e.Item DataGridItem instance still refers to the old, non-editable form. Hence, we have to work directly with the DataGrid's Items property, extracting the correct DataGridItem instance, which is given by e.Item.ItemIndex.

Once we have a reference to this TextBox control, we can go ahead and emit the client-side JavaScript code using the RegisterStartupScript method. Note that we have to include the actual script tags, and that the closing script must be broken up into two strings (i.e., "<" & "/script>"). If it is not, the ASP.NET parsing engine will be confused when it reaches that closing script block and generate an error.

In the RegisterStartupScript method you may have noticed that we are referencing the TextBox's ClientID property as opposed to its ID property. This is because the TextBox control is a child control of the DataGrid row and the DataGrid row is a child of the DataGrid. With each increasing child layer, ASP.NET augments the UniqueID of the controls, prefixing it with the parent's ID. For example, our TextBox's ID property is txtDesc, but it's UniqueID is something like: dgPopularFAQs:_ctrl4:txtDesc. The ClientID, which is the property we're interested in, is the actual value of the rendered HTML tag's id property, which is the UniqueID with colons replaced with underscores. That is, the ClientID for the TextBox would be something like: dgPopularFAQs__ctrl4_txtDesc.

The actual JavaScript emitted by the RegisterStartupScript method looks something like this.

< script language="JavaScript">
	frmEditFAQs.dgPopularFAQs__ctl12_txtDesc.focus();
	frmEditFAQs.dgPopularFAQs__ctl12_txtDesc.select();
</script>

The select() call is optional. It simply highlights all of the text in the TextBox. Feel free to omit this call if so desired.

Automatic Filtering

Changing the BindData Subroutine

Each demo has a BindData() subroutine that is responsible for binding the appropriate data to the DataGrid. Specifically, this subroutine performs the following tasks:

  1. Retrieves the data to display in the DataGrid from the SQL database that houses the ASPFAQs.com database, placing this data in a SqlDataReader.
  2. Specifies that the DataGrid's DataSource property should be assigned the SqlDataReader from the previous step.
  3. Calls the DataGrid's DataBind() method.

In our previous demos the SQL query performed by the BindData() subroutine was typically a call to the sp_Popularity stored procedure. In order to have the DataGrid display only those FAQs that belong to a certain FAQ category, we will need to have a parameterized SQL query of the following form:

SELECT Columns
FROM tblFAQ
WHERE FAQCategoryID = @FAQCategoryIDParameter 

where the @FAQCategoryIDParameter is a SqlParameter whose value will be set programmatically.

Since we already have the BindData() subroutine, let's concentrate on slightly altering this subroutine to provide the above SQL form (as opposed to writing a new subroutine from scratch). Since we want to only display FAQs from a particular FAQ category, let's alter the BindData() subroutine so that it accepts an input parameter: FilterOnFAQCategoryID, which will be an integer that signifies the FAQ category whose FAQs should be displayed in the DataGrid.

After this small change, all that's left to do is adjust the SQL query from a constant that always issues the sp_Popularity stored procedure to one that uses a parameterized SQL query whose parameter is based on the FilterOnFAQCategoryID input parameter. The code below shows the new BindData(FilterOnFAQCategoryID) subroutine:

Sub BindData(FilterOnFAQCategoryID as Integer)

  '2. Create the command object, passing in the SQL string
  Dim strSQL as String 
  strSQL = "SELECT FAQID, F.FAQCategoryID, F.Description, " & _
                  "FC.Name AS CategoryName " & _
           "FROM tblFAQ F " & _
             "INNER JOIN tblFAQCategory FC ON " & _
                "F.FAQCategoryID = FC.FAQCategoryID " & _
           "WHERE F.FAQCategoryID = @FAQCatID " & _
           "ORDER BY FAQID"

  'Set the datagrid's datasource to the datareader and databind
  Dim myConnection as New _
      SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
  Dim myCommand as SqlCommand = New SqlCommand(strSQL, myConnection) 
    
  Dim FAQCatIDParam as New SqlParameter("@FAQCatID", SqlDbType.Int, 4)
  FAQCatIDParam.Value = FilterOnFAQCategoryID
  myCommand.Parameters.Add(FAQCatIDParam)
    
  myConnection.Open()
  dgPopularFAQs.DataSource = myCommand.ExecuteReader()
  dgPopularFAQs.DataBind()
  myConnection.Close()
End Sub

Displaying Hyperlinks for Each FAQ Category

We need some way for the user to choose which FAQ category to view. One user interface that would allow for this would be to list, at the top of the page, each of the FAQ categories as a hyperlink or button. If the user clicks one of these FAQ category hyperlinks or buttons, the ASP.NET page would postback, the BindData(FilterOnFAQCategoryID) subroutine would be called with the proper FilterOnFAQCategoryID passed in, and the DataGrid would have the proper subset of FAQs displayed.

One way to accomplish this would be to just add a hyperlink for each FAQ category, like so.

<form runat="server">
  <asp:LinkButton runat="server" Text="Application Object"
        CommandArgument="7" OnCommand="FilterData" />
  <asp:LinkButton runat="server" Text="Arrays"
        CommandArgument="1" OnCommand="FilterData" />
  ...
  <asp:LinkButton runat="server" Text="Uploading"
        CommandArgument="19" OnCommand="FilterData" />


  <asp:DataGrid runat="server" id="dgFilteredFAQs" ...>
    ...
  </asp:DataGrid>
</form>

The CommandArgument property corresponds to the particular FAQ category's FAQCategoryID value. That is, all FAQs that fall under the "Application Object" category have a FAQCategoryID value of 7; all FAQs that fall under the "Array" category have a FAQCategoryID value of 1; etc. The DataGrid shown in the above code snippet is the actual DataGrid that the BindData(FilterOnFAQCategoryID) subroutine binds its results to - that is, it is the DataGrid that will display the FAQs for the particular FAQ category.

Given the above code, all that we would need to do is add an event handler named FilterData for the various LinkButtons' Command events. In code, it would look like this.

  Sub FilterData(sender as Object, e as CommandEventArgs)
	BindData(e.CommandArgument)
  End Sub

When a user clicks on of these LinkButtons, the ASP.NET page is posted back, and the Command event of the clicked LinkButton is raised. This triggers the FilterData event handler to run, which calls the BindData(FilterOnFAQCategoryID) subroutine, passing in the value of the clicked LinkButton's CommandArgument property as the FilterOnFAQCategoryID parameter, which has the effect of having the DataGrid display those FAQs of that particular FAQCategoryID.

Removing the Hardcoded LinkButtons

The above code that uses a hardcoded LinkButton for each FAQ category will work, but it's messy. A better approach would be to have the FAQ category LinkButtons generated dynamically. Recall from Dynamic Controls in ASP.NET that controls can be added dynamically via the Add method of the Controls collection, a collection every Web control has. To add our needed LinkButtons dynamically we'll first create a PlaceHolder control to mark where these LinkButtons should be added in the HTML content. Next, we'll perform a SQL query that will populate a SqlDataReader with the various FAQ categories. We can then iterate through this SqlDataReader adding a LinkButton for each row.

The following code provides a subroutine, AddFilterButtons(), that dynamically creates the needed LinkButtons. It adds each LinkButton to a PlaceHolder named phFilterLinkButtons, which appears in the HTML section. Also note that some HTML markup (namely, a pipe symbol) is added between each LinkButton to make it easier to read.

Sub AddFilterButtons()
  'Get a list of the categories
  '2. Create the command object, passing in the SQL string
  Dim strSQL as String 
  strSQL = "SELECT FAQCategoryID, Name AS CategoryName " & _
           "FROM tblFAQCategory " & _
           "ORDER BY CategoryName"

  'Set the datagrid's datasource to the datareader and databind
  Dim myConnection as New _
      SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
  Dim myCommand as SqlCommand = New SqlCommand(strSQL, myConnection) 
    
  myConnection.Open()
  Dim myDataReader as SqlDataReader = myCommand.ExecuteReader()
    
  'Loop through the results, creating a LinkButton for each row
  Dim filterButton as LinkButton
  While myDataReader.Read()
    'Add the LinkButton
    filterButton = New LinkButton()
    filterButton.Text = myDataReader("CategoryName")
    filterButton.CommandArgument = myDataReader("FAQCategoryID")
    AddHandler filterButton.Command, AddressOf Me.FilterData      
      
    phFilterLinkButtons.Controls.Add(filterButton)
      
    'Add some white space
    phFilterLinkButtons.Controls.Add(New LiteralControl(" | "))
  End While
    
  myDataReader.Close()
  myConnection.Close()    
End Sub

The AddFilterButtons() subroutine needs to be called in the Page_Load event handler so that the LinkButtons are created on each page load. By default, dynamically created controls are not saved to the Page's ViewState, meaning that they are lost across postbacks. If you want to save a dynamically created control in the ViewState you must do it explicitly, using ViewState("ViewStateKey") = ControlName.PropertyToSave to save one of the control's properties, and ControlName.PropertyToSave = ViewState("ViewStateKey") to restore it. Without saving the LinkButtons in the ViewState you are having to incur a database access every page view to grab the list of FAQ categories.

Radio Buttons

Introduction

There are many real-world situations in which you might like to create a DataGrid that has a column of radio buttons that serve to allow the user to select a particular row. For example, if you wanted to create some sort of online voting application, you might want to use a DataGrid to display the voting options with a radio button next to each option, so that the user can choose one and only one option.

Many developers who want to add this sort of functionality think that it is a trivial task, involving merely the addition of a TemplateColumn whose ItemTemplate has a RadioButton Web control. As we will see in this article, the task is not nearly this simple. Specifically, we will look at why using a TemplateColumn with a RadioButton Web control won't cut the mustard, and then we'll examine a free custom DataGrid Column control from Andy Smith's MetaBuilders.com Web site, which easily allows for adding a column of radio buttons to a DataGrid.

Why the TemplateColumn Approach Won't Work

The intuitive way to provide a radio button column in a DataGrid is to use a TemplateColumn where the ItemTemplate has a RadioButton control. This could be accomplished using the following DataGrid markup.

<form runat="server">
  <asp:DataGrid runat="server" id="dgPopularFAQs" ... >
    <Columns>
      <asp:TemplateColumn>
        <ItemTemplate>
          <asp:RadioButton runat="server" 
                 GroupName="ThisDoesntWork" id="NeitherDoesThis" />
        </ItemTemplate>
      </asp:TemplateColumn>
      <asp:BoundColumn ... />
      ...
    </Columns>
  </asp:datagrid>
</form>

The above code will create a column that has a radio button in each row, yes, but due to the way that the DataGrid names its containing controls, the user will be able to select multiple radio buttons. This is because when the web browser is confronted with a series of radio buttons, it considers radio buttons with the same name attribute to be of the same group of radio buttons.

The ASP.NET RadioButton Web control provides a GroupName property, that specifically sets the resulting radio button's HTML markup to have the name attribute specified by this property. Therefore, if you need to plop down a related series of RadioButton Web controls, you can simply give all of the RadioButton Web controls the same GroupName property value, which will result in radio button HTML markup where each radio button has the same name attribute, which will result in the user only being able to select one of the related radio buttons.

However, as you can see in the DataGrid example I provided, even though the GroupName is the same for all RadioButton Web controls in the column, the user can still select more than one distinct radio button. To see why this is, let's take a look at the HTML markup produced by the ASP.NET Web page when the above DataGrid example is used.

<table cellspacing="0" cellpadding="4" ...>
<tr align="Center" ...>
	<td>&nbsp;</td><td>FAQ ID</td><td>FAQ Description</td>
</tr><tr>
	<td>
        <input id="dgPopularFAQs__ctl2_NeitherDoesThis" 
               type="radio" 
               name="dgPopularFAQs:_ctl2:ThisDoesntWork" 
               value="NeitherDoesThis" />
      </td><td>144</td><td>Where can I host my ASP Web ...</td>
</tr><tr style="background-color:White;">
	<td>
        <input id="dgPopularFAQs__ctl3_NeitherDoesThis" 
               type="radio" 
               name="dgPopularFAQs:_ctl3:ThisDoesntWork" 
               value="NeitherDoesThis" />
      </td><td>115</td><td>I am using Access and ...</td>
</tr><tr>
	<td>
        <input id="dgPopularFAQs__ctl4_NeitherDoesThis" 
               type="radio" 
               name="dgPopularFAQs:_ctl4:ThisDoesntWork" 
               value="NeitherDoesThis" />
      </td><td>161</td><td>How can I convert a Recordset ...</td>
</tr>
...

The above HTML markup has been shortened considerable for brevity, and is only showing a small subset of the resulting HTML table rows produced by the DataGrid. However, the important part is still noticeable from this sample, and that is that the name property of each of the radio buttons is unique, even though we set the GroupName property to the same value!

This is because the DataGrid is being built up row-by-row it prepends the rows ID property to the GroupName property, using a colon as a delimiter. Then, it prepends the DataGrid's ID property, and this is what the radio button's name attribute is assigned. As you can see, the name for the first FAQ is: dgPopularFAQs:_ctl2:ThisDoesntWork, which, more generally, is DataGrid ID:DataGridItem ID:GroupName.

Making Each Radio Button's Name Identical

So how can we make sure that each radio button's name attribute is assigned the same value when the DataGrid prepends the DataGridItem and DataGrid ID properties to the name attribute? Use a custom DataGrid column class from which you can explicitly override the naming of the name attribute. What is a Custom DataGrid Column Class? The DataGrid has a number of built-in columns, including the BoundColumn, the TemplateColumn, and the ButtonColumn. A custom DataGrid column class is one that you create yourself. They're fairly easy to create, especially if you have a development tool like Visual Studio .NET. For more information on creating your own custom DataGrid column class, be sure to read John Dyer's Creating a Custom DataGridColumn Class.

Rather than writing our own custom DataGrid column class to perform this renaming, let's use one that is already written, is 100% free, and includes complete source code. I am talking about Andy Smith's excellent RowSelectorColumn control, which you can try out and download from http://metabuilders.com/Tools/RowSelectorColumn.aspx. Once you download the control, copy the control (the DLL file in the bin\Release directory of the downloaded ZIP file) to your Web application's /bin directory. Then, you can use Andy's control by simply adding the following code to the top of your ASP.NET Web pages that use the control.

<%@ Register TagPrefix="prefix" Namespace="MetaBuilders.WebControls"
    Assembly="MetaBuilders.WebControls.RowSelectorColumn" %> 

Creating a Column of Radio Buttons in a DataGrid

Once you have downloaded the RowSelectorColumn control and have moved it to your Web application's /bin directory, you can use the RowSelectorColumn just like you would any other DataGrid column. The RowSelectorColumn supports both columns of radio buttons and columns of checkboxes; to specify which to use, simply set the RowSelectorColumn's SelectionMode property: set it to Single for radio buttons, and Multiple for checkboxes.

The following example shows an ASP.NET Web page that displays a list of FAQs from ASPFAQs.com and allows the user to select one and click a Button. Once the Button is clicked, the ASP.NET Web page is posted back, and some server-side code is used to determine what radio button was selected.

<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
<%@ Register TagPrefix="mbrsc" Namespace="MetaBuilders.WebControls" 
         Assembly="MetaBuilders.WebControls.RowSelectorColumn" %>
< script language="vb" runat="server">
  ...

  Sub RecordVote(sender as Object, e as EventArgs)
    Dim rsc as RowSelectorColumn = RowSelectorColumn.FindColumn(dgPopularFAQs)
    
    If rsc.SelectedIndexes.Length = 0 then
      lblVoteResults.Text = "You did not select a FAQ!"
    Else
      Dim selIndex as Integer = rsc.SelectedIndexes(0)
      lblVoteResults.Text = "You voted for item " & selIndex & _
              ", which is the FAQ with FAQID " & _
              dgPopularFAQs.DataKeys(selIndex) & "<p>"
    End If
  End Sub
</script>

<form runat="server">
   <h3>Vote for Your Favorite FAQ!</h3>
   <asp:Label runat="server" id="lblVoteResults" ForeColor="Red"
       Font-Italic="True" Font-Size="14pt" />

  <asp:DataGrid runat="server" id="dgPopularFAQs"
                  ...
                  DataKeyField="FAQID"
                  AutoGenerateColumns="False">
    <Columns>
      <mbrsc:RowSelectorColumn SelectionMode="Single" />
      <asp:BoundColumn DataField="FAQID" HeaderText="FAQ ID" />
      <asp:BoundColumn DataField="Description" HeaderText="FAQ Description" />
    </Columns>
  </asp:datagrid><br />
  <asp:Button runat="server" onclick="RecordVote" text="Record Vote" />
</form>

First, note the @Register directive near the top, which specifies that the RowSelectorColumn control is referenced using the prefix mbrsc. The RowSelectorColumn then appears in the DataGrid using the mbrsc prefix as:

<mbrsc:RowSelectorColumn SelectionMode="Single" /> 

Note that the SelectionMode property is set to Single, which will display a radio button for each row.

The ASP.NET page also contains a Button Web control whose Click event is wired up to the RecordVote event handler. This event handler determines what radio button was clicked. To determine what radio button was clicked, a local variable, rsc, is assigned to the RowSelectorColumn instance of the DataGrid. This is accomplished by calling the RowSelectorColumn's static method FindColumn, passing in the DataGrid that the RowSelectorColumn appears in.

Once we have a reference to the RowSelectorColumn, we can examine the SelectedIndexes property, which is a collection of indexes that have been selected. First, we check to make sure that a radio button has been selected by seeing if the SelectedIndexes.Length property is greater than 0, and we retrieve the index of the row whose radio button was clicked via SelectedIndex(0).

Creating a Column of CheckBoxes in a DataGrid

Creating a DataGrid with a column of checkboxes with the RowSelectorColumn is strikingly similar to creating a column of radio buttons. The only difference is that the SelectionMode property is changed from Single to Multiple. Additionally, with checkboxes there is an optional AllowSelectAll property. If this is set to True, then at the top of the column of checkboxes will appear a check that, if checked, will cause all checkboxes in the column to become checked.

<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
<%@ Register TagPrefix="mbrsc" Namespace="MetaBuilders.WebControls" 
         Assembly="MetaBuilders.WebControls.RowSelectorColumn" %>
< script language="vb" runat="server">
  ...

  Sub RecordVote(sender as Object, e as EventArgs)
    Dim rsc as RowSelectorColumn = RowSelectorColumn.FindColumn(dgPopularFAQs)
    
    Dim selIndex as Integer
    For Each selIndex in rsc.SelectedIndexes
      lblVoteResults.Text &= "You chose item " & selIndex & _
              ", which is the FAQ with FAQID " & _
              dgPopularFAQs.DataKeys(selIndex) & "<br>"    
    Next
    
    lblVoteResults.Text &= "<p>"
  End Sub
</script>

<form runat="server">
  <asp:DataGrid runat="server" id="dgPopularFAQs" 
                  ...
                  DataKeyField="FAQID"
                  AutoGenerateColumns="False">
    <Columns>
      <mbrsc:RowSelectorColumn AllowSelectAll="True" SelectionMode="Multiple" />
      <asp:BoundColumn DataField="FAQID" HeaderText="FAQ ID" />
      <asp:BoundColumn DataField="Description" HeaderText="FAQ Description" />
    </Columns>
  </asp:datagrid>
  <br />
  <asp:Button runat="server" onclick="RecordVote" text="Record Vote" />
</form>

Note that the RecordVote() event handler was changed. Since there may be multiple checked checkboxes, we do not simply just grab the index from the first item in the SelectedIndexes property. Rather, we enumerate through all of the indexes.

Bi-Directional Sorting

Introduction

One of the most common user interface requirements is to be able to click on a column header and have the data toggle between being sorted in ascending order and descending order. In this article we will examine how to extend the DataGrid so that it can remember the "current sort state" of each column. Then, when the user clicks on a column the current sort order can be detected and toggled.

This part of the article series looks at one means to create a bi-directional sortable DataGrid. Bi-directional sorting is revisited in Part 18 of this article series, focusing on improving the techniques.

Displaying the Contents of the Authors Table

The example used in the article displays attributes from the authors table in the pubs database. The first order of business is to create an ASP.NET Web page that displays a DataGrid that contains the rows from the authors table of the pubs database. The following code creates the DataGrid and displays the appropriate data:

<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SQLClient" %>
< script language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
  ' Only bind the data on the first visit to the page
  If Not Page.IsPostBack
    GetAuthors("au_fname asc")
  End If
End Sub

Sub GetAuthors(sSortStr as string)
  Dim cn as SQLConnection
  Dim cmd as SQLCommand
  Dim rdr as SQLDataReader
  Dim sConnectString as String = "server=localhost;database=pubs;user id=sa"
  Dim sSql as String = "SELECT au_fname, au_lname, city, state, zip " & _
                       "from authors order by " & sSortStr
  
  ' Connect to the database
  cn = New SQLConnection(sConnectString)
  cn.open()

  ' execute the SQL 
  cmd = New SQLCommand(sSQL, cn)
  rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

  ' Associate the data grid with the data
  DispAuthors.DataSource = rdr
  DispAuthors.DataBind()
End Sub

' this method is called when user clicks on any of the column headings
Sub SortAuthors(sender As Object, e As DataGridSortCommandEventArgs)
  '... Code coming soon! :-) ...
End Sub
</script>

<form runat="server">
  <ASP:Datagrid id="DispAuthors" runat="server" AutoGenerateColumns="False" 
        AllowSorting="true" onSortCommand="SortAuthors">
    ...
    <Columns>
        <asp:BoundColumn DataField="au_fname" SortExpression="au_fname ASC" 
                                                   HeaderText="First Name" />
        <asp:BoundColumn DataField="au_lname" SortExpression="au_lname" 
                                                   HeaderText="Last Name" />
        <asp:BoundColumn DataField="city" HeaderText="City" />
        <asp:BoundColumn DataField="state" SortExpression="state" 
                                                   HeaderText="State" />
        <asp:BoundColumn DataField="zip" HeaderText="Zip" />
    </Columns>
  </asp:datagrid>
</form>

The HTML code defines a datagrid, DispAuthors, that displays authors information from the pubs database. The columns shown are author's first and last names, city, state and zip code.

Two important things to notice with the above source code:

  1. SortAuthors() is the event handler invoked when user clicks on a column header. The source code for this event handler has yet to be provided. We'll be examining the needed code soon.
  2. Only the first name, last name and state columns have their SortExpression property specified. This means that only these columns are sortable. Also note that the SortExpression property, along with the column name to sort, also contains information on the sort order (i.e., the First Name column's SortExpression property is "au_fname ASC").

The GetAuthors() subroutine retrieves the data to be displayed in the DataGrid. This method takes in a string parameter, sSortStr, which specifies how the data from the authors table should be sorted. For example, to sort the results by the au_fname column in ascending order, the argument to GetAuthors() would be "au_fname asc". Notice that when the page is first visited, the Page_Load event handler calls GetAuthors() passing in "au_fname asc"; hence, when the page is first visited, the data will be displayed alphabetically by the authors' first names.

Writing the DataGrid's Sort Event Handler

Whenever one of the DataGrid's sortable column headers is clicked, the ASP.NET Web page is posted back and the DataGrid's SortCommand event fires, which causes the specified event handler, SortAuthors, to execute. In order to be able to toggle between sorting a column in ascending and descending order, we need to be able to remember how the column was last sorted. To accomplish this we have a couple of options. One way is to store a bit of information in the ViewState for each sortable column, indicating if the column has last been sorted in ascending or descending order. Another way, and the way we will be doing it in this article, is storing this extra information in the column's SortExpression property.

In the previous code example, you may have noticed that the SortExpression property for the First Name column indicated the order of the sorting. Specifically, the First Name column's SortExpression property was set to "au_fname ASC". This demonstrates how the SortExpression property can be set so that it not only conveys the column to sort, but also the sort direction.

The code for the SortAuthors event handler is as follows.

Sub SortAuthors(sender As Object, e As DataGridSortCommandEventArgs)
  Dim SortExprs() As String
  Dim CurrentSearchMode As String, NewSearchMode As String
  Dim ColumnToSort As String, NewSortExpr as String

  '  Parse the sort expression - delimiter space
  SortExprs = Split(e.SortExpression, " ")  
  ColumnToSort = SortExprs(0)

  ' If a sort order is specified get it, else default is descending
  If SortExprs.Length() > 1 Then
      CurrentSearchMode = SortExprs(1).ToUpper()
      If CurrentSearchMode = "ASC" Then
         NewSearchMode = "Desc"
      Else
         NewSearchMode = "Asc"
      End If
  Else   ' If no mode specified, Default is descending
      NewSearchMode = "Desc"
  End If

  '  Derive the new sort expression. 
  NewSortExpr = ColumnToSort & " " & NewSearchMode

  ' Figure out the column index 
  Dim iIndex As Integer
  Select Case ColumnToSort.toUpper()
    case "AU_FNAME"
      iIndex = 0
    case "AU_LNAME"
      iIndex = 1
    case "STATE"
      iIndex = 3
  End Select

  ' alter the column's sort expression 
  DispAuthors.Columns(iIndex).SortExpression = NewSortExpr

  ' Sort the data in new order
  GetAuthors(NewSortExpr)
End Sub

The SortAuthors event handler starts by splitting the SortExpression property on a space. This returns a string array with either one or two elements - one element if the SortExpression property has just the column name to sort on, like "au_lname", and two elements if the SortExpression property has both the column name and the direction, like "au_fname ASC". In the one-element case, the one element contains the name of the column to sort on; in the two-element case, the first element contains the column to sort on, while the second element specifies the direction. The second element (if it exists) is examined and then toggled. This toggled value becomes the new SortExpression property for the column that was clicked. Finally, a call to GetAuthors(), passing in the new sort expression, displays the sorted data.

One Caveat

One thing you need to be aware of here is the Select Case statement, which is used to determine what DataGrid column's sortable header was clicked. Since this Select Case statement contains the various column named hard-coded in there, if you add a new sortable column to the DataGrid, or change the order of the sortable columns in the DataGrid, you will need to update this code.

Displaying a Sum in a Footer

Computing the Sum of a Column

There are a couple of ways to compute the sum of a DataGrid column. One way is to not actually compute the sum of the DataGrid column, per se, but to compute the sum of the data through a SQL query. For example, to compute the sum of all ASPFAQs.com FAQs, the following SQL statement could be used:

SELECT SUM(ViewCount)
FROM tblFAQs

The results of the SQL query, however, must be retrieved in a DataReader or DataSet other than the one that's being bound to the DataGrid - this means two round trips to the database server. Also, if you have, say, three DataGrid column that you want to compute sums for, then you'll need four separate SQL queries - one for all the data and then one for the sum of each of the three columns.

A simpler way to compute the sum of a DataGrid column is to do it on the fly, as the DataGrid is being constructed. When the DataGrid's DataBind() method is called, the DataSource object is enumerated over. For each record in the DataGrid's DataSource, a new DataGridItem is created. After the DataGridItem is created and the DataSource's is bound to the row, the ItemDataBound event is fired. Again, this happens for each row added to the DataGrid.

We can provide an event handler for the DataGrid's ItemDataBound event. In this event handler, all that we need to do is determine the value of the column that we want to sum and add it to a variable that holds the running total. The code for this isn't particularly difficult, and can be seen below.

< script language="VB" runat="server">
  Dim viewCountSum as Integer = 0
  
  Sub ComputeSum(sender As Object, e As DataGridItemEventArgs)
    'First, make sure we are dealing with an Item or AlternatingItem
    If e.Item.ItemType = ListItemType.Item OR _
          e.Item.ItemType = ListItemType.AlternatingItem then
      'Snip out the ViewCount
      Dim viewCount as Integer = _
         Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "ViewCount"))
      viewCountSum += viewCount
    End If
  End Sub
</script>

<asp:DataGrid runat="server" id="dgPopularFAQs"
       ...
       OnItemDataBound="ComputeSum">
         
  <Columns>
    <asp:BoundColumn DataField="Description" HeaderText="Description" />
    <asp:BoundColumn DataField="ViewCount" HeaderText="Page Views" 
              DataFormatString="{0:#,###}" ItemStyle-HorizontalAlign="Right" 
              ItemStyle-Width="15%" />
  </Columns>
</asp:DataGrid>

First, note that a variable outside of all event handlers, subroutines, and functions, viewCountSum, has been declared. This is the variable that will hold the running total. Next, in the DataGrid, the ItemDataBound event is wired up to the ComputeSum() event handler.

The ComputeSum() event handler is fairly straightforward. It first makes sure that the row we are dealing with is an Item or AlternatingItem row. Realize that the ItemDataBound event handler fires for all DataGrid rows, including the header and footer. If we omitted the check to ensure that we were working with an Item or AlternatingItem row, we'd get a runtime error when trying to access e.Item.DataItem for the header and footer rows. After ensuring that we are working with an Item or AlternatingItem row, the ViewCount item from the DataSource's current DataItem is read and stored into a variable viewCount. This variable is then used to increment viewCountSum, the running total.

Displaying the Sum in the DataGrid's Footer

Since the footer is created last, by the time the footer's ItemDataBound event fires, all of the ItemDataBound events for the databound rows would have fired, meaning that when the footer is created viewCountSum has already been computed.

All that remains is to emit the value of viewCountSum into the proper footer cell. This can be accomplished by altering the ComputeSum() event handler to contain the following code.

  Sub ComputeSum(sender As Object, e As DataGridItemEventArgs)
    'First, make sure we are dealing with an Item or AlternatingItem
    If e.Item.ItemType = ListItemType.Item OR _
          e.Item.ItemType = ListItemType.AlternatingItem then
      'Snip out the ViewCount
      Dim viewCount as Integer = _
         Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "ViewCount"))
      viewCountSum += viewCount
    ElseIf e.Item.ItemType = ListItemType.Footer then
      e.Item.Cells(1).Text = "Total: " & String.Format("{0:#,###}", viewCountSum)
    End If
  End Sub

Note that the ComputeSum() event handler has been updated to now also check to see if the ItemType being added is a Footer. If it is, then the second column's Text property is set to the value of viewCountSum (with some formatting thrown in for aesthetics). As the code illustrates, the Footer contains a column for each column in the DataGrid, and to access the i-th column, simply use: e.Item.Cells(i), where i is zero-based.

In order to display a running sum in the footer, you must indicate that the footer should be displayed by setting the ShowFooter DataGrid property to True. You can do this in the DataGrid's declaration, like so.

<asp:DataGrid runat="server" id="dgPopularFAQs"
       ...
       ShowFooter="True"
       OnItemDataBound="ComputeSum">
         
   ...
</asp:DataGrid>

Master/Detail Grids

Introduction

A master/detail report is a report type that Web developers for data-driven Web sites are commonly asked to produce. A master/detail report is one that lists each high-level item - such as each FAQ category - and beneath each high-level item lists the subitems that belong to that particular item - such as all the FAQs of a particular category. In this article we'll examine how to create such a master/detail report using the DataGrid.

Listing the FAQ Categories

Before we concern ourselves with how to list the various FAQs for each FAQ category, let's get the easy part of this task out of the way first: creating a DataGrid that lists each FAQ category. The following code lists the categories from the tblFAQCategory table. Note that our SQL query retrieves both the primary key FAQCategoryID column as well as the Name column, although only the Name column is displayed in the DataGrid. This is because later, when we want to include the FAQs for the particular FAQ category, we'll need to know the FAQCategoryID column value.

<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SQLClient" %>
< script language="VB" runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)
    BindData()
  End Sub
	
  Sub BindData()
    '1. Create a connection
    Dim myConnection as New _
       SqlConnection(ConfigurationSettings.AppSettings("connectionString"))

    '2. Create the command object, passing in the SQL string
    Dim strSQL as String = _
         "SELECT FAQCategoryID, Name FROM tblFAQCategory ORDER BY Name"
    Dim myCommand as New SqlCommand(strSQL, myConnection)

    'Set the datagrid's datasource to the datareader and databind
    myConnection.Open()
    dgMasterDetail.DataSource = _
           myCommand.ExecuteReader(CommandBehavior.CloseConnection)
    dgMasterDetail.DataBind()
    myConnection.Close()
  End Sub
</script>

<asp:DataGrid runat="server" id="dgMasterDetail"
       AutoGenerateColumns="False"
       ...>
         
 <Columns>
    <asp:BoundColumn DataField="Name" HeaderText="FAQ Category" />
    <asp:TemplateColumn HeaderText="FAQs">
      <ItemTemplate>
        <i>The FAQs for the FAQCategory will go here...
      </ItemTemplate>
    </asp:TemplateColumn>
  </Columns>
</asp:DataGrid>

Notice that the DataGrid has two columns, one for the FAQ category's name, and one for the FAQs for the particular category. In this second TemplateColumn we'll be programmatically adding a DataGrid that displays the FAQs for the particular FAQ category.

Displaying the FAQs for a Particular Category

Now that we have our DataGrid that displays the various FAQ categories, we need to enhance it so that the FAQ for each FAQ category is also displayed. To accomplish this we need to be able to programmatically add a DataGrid with the appropriate FAQs for the FAQ category. This can be accomplished by creating an event handler for the DataGrid's ItemDataBound event, which fires after each new row in the DataGrid is created and has been bounded to the appropriate row in the DataSource. At this point, we can programmatically inspect the data bound to the row (which will contain the FAQCategoryID information we need), and alter the actual row's contents. To add an event handler for the ItemDataBound event, we simply need to create an appropriate event handler and wire it up to the DataGrid's event. The following code demonstrates this.

< script language="VB" runat="server">
  ...	
	
  Sub buildFAQsDataGrid(sender as Object, e as DataGridItemEventArgs)
    If e.Item.ItemType = ListItemType.Item OR _
            e.Item.ItemType = ListItemType.AlternatingItem then
      'Build the DataGrid
      Dim dg as New DataGrid()
      
      'Find out the CategoryID
      Dim CatID as Integer = e.Item.DataItem("FAQCategoryID")
      
      'Bind the Data to the DataGrid
      dg.DataSource = GetFAQsByCategoryID(CatID)
      dg.DataBind()
      
      'Add the DataGrid to the 2nd Column
      e.Item.Cells(1).Controls.Add(dg)
    End If
  End Sub
</script>

<asp:DataGrid runat="server" id="dgFAQsByCategory"
       AutoGenerateColumns="False"
       Font-Name="Verdana" Width="85%"
       Font-Size="11pt" HorizontalAlign="Center"
       ShowFooter="True"
       OnItemDataBound="buildFAQsDataGrid">
   ...
</asp:DataGrid>

The above code shows a couple things, and has a couple holes in it. First, note that the DataGrid declaration includes: OnItemDataBound="buildFAQsDataGrid". This wires up the DataGrid's ItemDataBound event to the buildFAQsDataGrid event handler. The buildFAQsDataGrid event handler starts by checking to see the type of item being created. Note that the ItemDataBound event fires when the DataGrid header and footers are being created as well. Since we are only interested in the actual items and alternating items, we check to make sure that the new DataGridItem's ItemType is either an Item or AlternatingItem. If we are dealing with an item or alternating item, we next create a new DataGrid, dg. This DataGrid will be used to display the FAQs for the particular category. Following this, we create a local variable named CatID, and assign to it the value of the FAQCategoryID value from the DataSource. Next, we call a function - which is not shown - called GetFAQsByCategoryID(CategoryID), which presumably returns the FAQs for a particular category. We then call the DataBind() method and add the DataGrid to the TemplateColumn.

The above code, with the addition of a GetFAQsByCategoryID() function, will do the trick. However, it's horribly inefficient (assuming that the GetFAQsByCategoryID() function makes a database call that retrieves the FAQs for the particular category). Its inefficiency springs from the fact that for each FAQ category, an entire new database access must transpire. That is, if there are 15 FAQ categories, there will be 15 separate database queries. While each database query will only return the needed records, it would be much more efficient to do one large database query at the beginning, getting all of the FAQs, and then only binding the correct ones for each FAQ category row. (The reason this approach is more efficient than the former approach is because the majority of the "cost" of a database query is in setting up and tearing down the connection. We can amortize this building up/tearing down cost by doing just one large query at the beginning.)

Filtering Data with a DataView

Our new challenge is to be able to read in all of the FAQs and then selectively bind FAQs that meet certain criteria to a DataGrid. For example, when the ItemDataBound event fires for the row corresponding to the Arrays FAQ category (which has a FAQCategoryID of 1), we want to bind just those FAQs whose FAQCategoryID column equals 1 as well. Fortunately, this task is quite easy to accomplish with the use of the DataSet and a DataView.

The DataView object in the .NET Framework allows for data to be viewed in a manner other than the way it's presented. For example, if we had a DataSet with a list of FAQs, we might want to view the data as if it was sorted upon some particular column. Or we might want to only view those FAQs that meet a certain criteria. We can accomplish this using a DataView. To get a filtered view of data, we use the DataView's RowFilter property. Specifically, we specify the WHERE clause we want to apply to the data. So, to see just the FAQs whose FAQCategoryID equals 1, we could set the RowFilter property to: FAQCategoryID = 1.

A DataSet is made up of one or more DataTables. Each DataTables has a DefaultView, which is a DataView object instance. We can create a new DataView, assign it to the DataTable's DefaultView, set this new DataView's RowFilter property, and then bind the DataView to the programmatically created DataGrid. Following these sequence of steps, the "details" DataGrid created for each "master" row will contain just those "detail" subitems than belong to the "master" item.

The updated code for the buildFAQsDataGrid event handler looks as follows:

< script language="VB" runat="server">
  ...	
	
  Sub buildFAQsDataGrid(sender as Object, e as DataGridItemEventArgs)
    If e.Item.ItemType = ListItemType.Item OR _
            e.Item.ItemType = ListItemType.AlternatingItem then
      'Build the DataGrid
      Dim dg as New DataGrid()
      
      'Find out the CategoryID
      Dim CatID as Integer = e.Item.DataItem("FAQCategoryID")
      
      'Create a DataView that has only the applicable FAQs
      Dim properFAQs as DataView = FAQsDS.Tables("FAQs").DefaultView
      properFAQs.RowFilter = "FAQCategoryID=" & CatID
      
      'Bind the Data to the DataGrid
      dg.DataSource = properFAQs
      dg.DataBind()
      
      'Add the DataGrid to the 2nd Column
      e.Item.Cells(1).Controls.Add(dg)
    End If  End Sub
</script>

Note that we create a DataView, properFAQs, and assign it to the DefaultView of the FAQs table in the FAQsDS DataSet. Next, the DataView's RowFilter property is set according to the value of the FAQCategoryID. Lastly, the DataView is assigned to the DataGrid's DataSource property, the DataGrid's DataBind() method is called, and the DataGrid is programmatically added to the master DataGrid's TemplateColumn.

All that remains unanswered is where the FAQsDS DataSet comes from and how it is populated. With this improved approach to a master/detail report, we need to also rework our BindData() function. Rather than simply getting just the FAQ categories, we need to get all of the FAQ categories as well as all of the FAQs. Furthermore, we need to create a DataSet instance, FAQsDS that is accessible to all functions on this ASP.NET Web page. These aims are accomplished with the following code:

< script language="VB" runat="server">
  ...	
	
  Dim FAQsDS as New DataSet()
	
  Sub BindData()
    '1. Create a connection
    Dim myConnection as New 
        _SqlConnection(ConfigurationSettings.AppSettings("connectionString"))

    '2. Create the command object, passing in the SQL string
    Dim strCategorySQL, strFAQSQL as String 
    strCategorySQL = "SELECT FAQCategoryID, Name " & _
                     "FROM tblFAQCategory ORDER BY Name"
    
    strFAQSQL = "SELECT FAQID, FAQCategoryID, Description, ViewCount " & _
                "FROM tblFAQ ORDER BY Description"
                
    Dim myCatCommand as New SqlCommand(strCategorySQL, myConnection)
    Dim myCatDA as New SqlDataAdapter(myCatCommand)
    
    Dim myFAQCommand as New SqlCommand(strFAQSQL, myConnection)    
    Dim myFAQDA as New SqlDataAdapter(myFAQCommand)
    
    'Fill the dataset
    myConnection.Open()
    myCatDA.Fill(FAQsDS, "Categories")
    myFAQDA.Fill(FAQsDS, "FAQs")
    myConnection.Close()
    
    'Bind the Categories DataSet to the DataGrid
    dgFAQsByCategory.DataSource = FAQsDS.Tables("Categories")
    dgFAQsByCategory.DataBind()
  End Sub
</script>

First, notice that a DataSet object instance, FAQsDS, is created in global scope, so that all functions in this ASP.NET Web page can refer to the DataSet. In the BindData() function, two SQL queries are formulated: one to retrieve all of the FAQ categories, and the other to retrieve all of the FAQs. Two DataTables are added to the FAQsDS DataSet, one denoted Categories and the other denoted FAQs. The Categories DataTable is then bound to the "master" DataGrid.

Data Paging

Introduction

Large DataGrids have two main disadvantages.

  1. Readers cannot be expected to be able to digest such large blocks of information.
  2. Dumping large amounts of data in one Web page leads to large Web page sizes, which can take unbearably long to download on dial-up connections.

Paging Support in the DataGrid

The DataGrid supports two kinds of paging: default paging and custom paging. Default paging, which this article focuses on, is the simpler of the two to setup and configure. As we will see, getting default paging to work only requires a few minor modifications to the standard process of displaying data in a DataGrid. The major difference between default paging and custom paging is this: with default paging, each time you want to display a page of data in the DataGrid, you need to grab all of the data from the underlying data source. Then, the DataGrid only selectively displays part of the entire set of data, based on what page you want to display and how many records per page to display. The key thing to understand here is that everytime the page is loaded, the entire data result is retrieved. For example, imagine that you work at an eCommerce company and you want to allow the user to page through a list of the 150 products your company sells. Specifically, you want to display 10 records per page. Now, when a user visits the Web page, you will access all 150 records, and the DataGrid will display the first 10 products (products 1 to 10). Next, imagine that the user navigates to the next page of data. This will cause a postback, at which point you'll rerequest all 150 records, but this time the DataGrid will display the second set of 10 (products 11 to 20). With custom paging, you, the developer, have to do a bit more work. Rather than just being able to blindly bind database data to the DataGrid, you have to selectively retrieve only those records that should be shown for the particular page. The benefit of this is that when displaying the first page of data, you can use a SQL statement that only retrieves products 1 through 10, rather than all 150 records. However, your SQL statement has to be "clever" enough to be able to know how to just snip out the right subset of records from the 150.

The Performance Edge of Custom Paging
Realize that custom paging provides better performance than default paging because only those database records that need to be displayed are retrieved. In our products example, we assumed there were 150 products, showing 10 per page. With custom paging, if the user stepped through all 15 pages of data, precisely 150 records would have been queried from the database. With default paging, however, for each page of data 150 records would have been accessed, leading to a total number of retrieved records of 15 times 150, or 2,250!


While custom paging exhibits better performance, default paging is much easier to use. Therefore, I would encourage you to use default paging if the data you are paging through is relatively small and/or the database server is not heavily trafficked. If you have thousands of tens of thousands of records you are paging through, by all means use custom paging. However, for paging through something like the ASPFAQs.com database, which only has, currently, ~200 FAQs, default paging is sufficient.

Understanding Paging

Before we examine how to add paging support, it's imperative that we take a moment to discuss how the DataGrid implements paging. The DataGrid contains a handful of properties designed to aid in paging:

  • CurrentPageIndex - indicates what zero-based page of data to display. This property's default value is 0, so by default the first page of data will be displayed.
  • PageSize - indicates how many records to show per page.
  • {732} - indicates how many total pages of data there are. This value is computed by the ceiling of the number of total records divided by the number of records per page.
  • AllowPaging - this Boolean property indicates whether or not the DataGrid is pageable. In order to use the DataGrid's built-in paging features, you need to set this to True.

Pageable DataGrids automatically display a paging interface. This paging interface is customizable but, by default, displays a left and right arrow hyperlink for navigating to the previous and next pages. When a user clicks on one of these navigational hyperlinks, the ASP.NET Web page is posted back and the DataGrid's PageIndexChanged event fires. Essentially, to provide paging support you need to add an event handler that responds to this event. Specifically, the event handler needs to correctly adjust the DataGrid's CurrentPageIndex property and rebind the DataGrid.

Working with Default Paging

To demonstrate how to add paging support to a DataGrid, let's first take a look at code to display non-paged data first, and then examine the steps needed to "upgrade" the DataGrid to a pageable one. Here is a simple ASP.NET Web page that displays the rows from the ASPFAQs.com database:

<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
< script language="vb" runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)
    BindData()
  End Sub
	
	
  Sub BindData()
    '1. Create a connection
    Dim myConnection as New SqlConnection(connection string)

    '2. Create the command object, passing in the SQL string
    Dim strSQL as String = "SELECT FAQID, Description " & _
                           "FROM tblFAQs ORDER BY FAQID"
    Dim myCommand as New SqlCommand(strSQL, myConnection)

    'Set the datagrid's datasource to the datareader and databind
    myConnection.Open()
    dgPopularFAQs.DataSource = myCommand.ExecuteReader()
    dgPopularFAQs.DataBind()	
    
    myConnection.Close()
  End Sub
</script>

<asp:DataGrid runat="server" id="dgFAQs" 
                BackColor="#eeeeee" Width="85%"
                HorizontalAlign="Center"
                Font-Name="Verdana"
                Font-Size="10pt">
  <HeaderStyle BackColor="Black" ForeColor="White" Font-Bold="True"
		 HorizontalAlign="Center" />
  <AlternatingItemStyle BackColor="White" />
</asp:DataGrid>

Now, taking this simple example we only need to make the following additions:

  1. Set the DataGrid's AllowPaging property to True.
  2. Place the DataGrid within a Web Form (the < form runat="server" >). This is needed because navigating through each of the DataGrid's pages of data incurs a postback.
  3. Update the BindData() method to use a DataSet instead of a SqlDataReader.
  4. Add an event handler for the DataGrid's PageIndexChanged event.

The new, complete code can be seen below.

<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
< script language="vb" runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)
    BindData()
  End Sub	
	
  Sub BindData()
    '1. Create a connection
    Dim myConnection as New SqlConnection(connection string)

    '2. Create the command object, passing in the SQL string
    Dim strSQL as String = "SELECT FAQID, Description " & _
                           "FROM tblFAQs ORDER BY FAQID"
    Dim myCommand as New SqlCommand(strSQL, myConnection)

	Dim myAdapter as New SqlDataAdapter(myCommand)
	Dim ds as New DataSet()
	myAdapter.Fill(ds)
    
    'Set the datagrid's datasource to the DataSet and databind    
    dgPopularFAQs.DataSource = ds
    dgPopularFAQs.DataBind()	
    
    myConnection.Close()
  End Sub  
  
  Sub NewPage(sender As Object, e As DataGridPageChangedEventArgs) 
    dgPopularFAQs.CurrentPageIndex = e.NewPageIndex
    BindData()      
  End Sub

</script>

<form runat="server">
	<asp:DataGrid runat="server" id="dgPopularFAQs" 
	                BackColor="#eeeeee" Width="85%"
	                HorizontalAlign="Center"
	                Font-Name="Verdana"
	                Font-Size="10pt"
	                AllowPaging="True"
	                OnPageIndexChanged="NewPage">
	  <HeaderStyle BackColor="Black" ForeColor="White" Font-Bold="True" 
	                  HorizontalAlign="Center" />
	  <AlternatingItemStyle BackColor="White" />
	</asp:datagrid>
</form>

The first thing to note is the NewPage() subroutine, which is the event handler for the DataGrid's PageIndexChanged event. Notice that all it does is set the CurrentPageIndex property and rebind the database data to the DataGrid. Another thing to take note of is that we made a small change to the BindData() method, binding a DataSet to the DataGrid as opposed to a SqlDataReader. The reason we did this is because default paging requires that the DataGrid be able to determine the number of total records in the DataSource. This is possible with the DataSet, but not with the SqlDataReader, hence the change.

If You Try to Use a SqlDataReader
If you accidentally attempt to use a SqlDataReader with default paging, as opposed to a DataSet, you will get the following error message.

AllowCustomPaging must be true and VirtualItemCount must be set for a DataGrid with ID DataGridID when AllowPaging is set to true and the selected datasource does not implement ICollection.


Customizing the Navigational Interface

If you check out the live demo you'll see that, by default, the DataGrid displays the navigational interface as "<" and ">" hyperlinks, to navigate to the previous and next pages, respectively. This paging interface can be customized using the PagerStyle property of the DataGrid.

There are two built-in paging styles, specified via the Mode property.

  1. NextPrev - in this paging style, the user can move just to the immediately next or immediately previous page. This is the default.
  2. NumericPages - in this paging style, the user can jump to a particular page. You can customize this paging style by setting the NextPageText and PrevPageText properties to the text that you want to appear for the next and previous hyperlinks.

For example, the following DataGrid declaration demonstrates customizing the Next/Previous paging interface:

<form runat="server">
  <asp:DataGrid runat="server" id="dgPopularFAQs" 
               ...>
      ...
      <PagerStyle NextPageText="Next --&gt;" PrevPageText="&lt;-- Prev."
               HorizontalAlign="Right" />
  </asp:datagrid>
</form>


And the following DataGrid declaration demonstrates using the Numeric Pages interface.

<form runat="server">
  <asp:DataGrid runat="server" id="dgPopularFAQs" 
               ...>
      ...
      <PagerStyle Mode="NumericPages" HorizontalAlign="Center" />
  </asp:datagrid>
</form>

Editable Grid With Cascading DropDownLists

Introduction

In this article we'll examine how to create an editable DataGrid that has two columns with cascading values. When a row is edited, these two columns will display DropDownLists, and we'll see how the user's choice in the first DropDownList affects the choices in the second.

Examining a Sample Data Model

Before we look at the specifics of displaying an editable DataGrid with two cascading DropDownLists, let's first take a look at a data model that could be used in such a scenario. Imagine that there exists a Employees table, which includes a foreign key to the Departments table, which lists all of the departments in the company, and associates each employee with a single department. Now, since there may be hundreds of departments in a large company, the company is arranged into a number of divisions. Each division is comprised of a number of departments. This is modeled in the database via the Divisions table and the foreign key from Departments to Divisions. For example, the company might have three divisions: I.T., Sales, and Marketing. Each division would have multiple departments, and each department would have multiple employees.

Division: I.T.
Department: Internet Team
Scott
Sam
Jisun
Department: eCommerce Security Team
Ben
Dave
Chris
Bruce
Division: Sales

Displaying Employee Information in a DataGrid

Our next task is to create a DataGrid that displays information about the employees in the company. Each row of the DataGrid will need to contain information about a single employee, including the division and department they work for. To get all employees, along with their division and department names, we'll need to use a SQL query that performs an inner join, connecting the correct rows from the Departments and Divisions table for each employee. The SQL query might look something like:

SELECT 
     EmployeeID          -- Employee info
    ,e.Name
    ,d.DepartmentID      -- Department info
    ,d.Name as DeptName
    ,dv.DivisionID       -- Division info
    ,dv.Name as DivName

FROM 
    Employees e

    INNER JOIN Departments d ON
        d.DepartmentID = e.DepartmentID

    INNER JOIN Divisions dv ON
        dv.DivisionID = d.DividsionID

ORDER BY 
    e.Name

Since we'll need to use a DropDownList when editing the division and department columns in the DataGrid, we'll need to use TemplateColumns rather than BoundColumns for these two columns. The column displaying the employee's name, however, can be a BoundColumn. The following shows what the DataGrid's declarative syntax might look like.

<asp:DataGrid runat="server" ... AutoGenerateColumns="False">
  <Columns>
    <asp:EditCommandColumn ... />
    <asp:BoundColumn DataField="Name" HeaderText="Name" />
    <asp:TemplateColumn HeaderText="Division">
      <ItemTemplate>
        <%# DataBinder.Eval(Container.DataItem, "DivName") %>
      </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn HeaderText="Department">
      <ItemTemplate>
        <%# DataBinder.Eval(Container.DataItem, "DeptName") %>
      </ItemTemplate>
    </asp:TemplateColumn>
  </Columns>
</asp:DataGrid>

Such a DataGrid would have output like this.
ActionNameDivisionDepartment
EditBenI.T.eCommerce Security Team
EditCharlesSalesSales Force Management Team
EditChrisI.T.eCommerce Security Team
EditScottI.T.Internet Team

Creating the EditItemTemplate

The next step is to create the EditItemTemplate in the two TemplateColumn DataGrid columns. We want the EditItemTemplate to include a DropDownList Web control:

  <asp:TemplateColumn HeaderText="Division">
    <ItemTemplate>
      <%# DataBinder.Eval(Container.DataItem, "DivName") %>
    </ItemTemplate>
    <EditItemTemplate>
      <asp:DropDownList runat="server" id="ddlDivision" AutoPostBack="True"
              OnSelectedIndexChanged="ddlDivision_SelectedIndexChanged"
              DataTextField="Name" DataValueField="DivisionID" />
    </EditItemTemplate>
  </asp:TemplateColumn>
  <asp:TemplateColumn HeaderText="Division">
    <ItemTemplate>
      <%# DataBinder.Eval(Container.DataItem, "DeptName") %>
    </ItemTemplate>
    <EditItemTemplate>
      <asp:DropDownList runat="server" id="ddlDepartment"
               DataTextField="Name" DataValueField="DepartmentID" />
    </EditItemTemplate>
  </asp:TemplateColumn>

As you can see, the EditItemTemplate contains a DropDownList for both TemplateColumns; both DropDownLists have their DataTextField and DataValueField set to the appropriate fields in the respective tables. The Division TemplateColumn, however, has its AutoPostBack property set to True, along with its SelectedIndexChanged event wired up to the ddlDivision_SelectedIndexChanged event handler (which we'll examine shortly). The reason the Division TemplateColumn has these two additions is so that whenever the user modifies his selection from the Division DropDownList, the Web page will be posted back and the ddlDivision_SelectedIndexChanged event handler will fire. From this event handler, we'll populate the contents of the Department DropDownList based on the selected Division.

Populating the DropDownLists

The final piece left in the puzzle is populating the two DropDownLists. There are two occasions where these lists need to be populated:

  1. When the DataGrid is being placed in edit mode. This involves setting the EditItemIndex to the index of the row to be edited, and rebinding the data to the DataGrid. At this point, we need to populate the two DropDownLists and set their selected value based on the database data.
  2. When the end user selects a different division. At this point we must populate the Department DropDownList based on the selected division.

To populate the DropDownLists when the DataGrid is being placed in edit mode, we'll create an event handler for the DataGrid's ItemDataBound event. This event fires once for each row whenever the DataGrid's DataBind method is called. What we'll do is check to see if the current row being bound is the editable row. If so, we'll get a reference to the Division DropDownList and populate it with the rows from the Divisions table. Following this, we'll select the correct division, based on the current employee's division ID. Next, we'll get a reference to the Department DropDownList and populate it with the rows from the Departments table based on the selected division. Lastly, we'll select the department based on the employee's DepartmentID. The following code accomplishes this.

private void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
// see if we are working with the row being edited
if (e.Item.ItemType == ListItemType.EditItem)
{
// populate the division DDL
DropDownList ddlDivision = (DropDownList) e.Item.FindControl("ddlDivision");

// connect to Access DB
OleDbConnection myConnection = new OleDbConnection(
                    ConfigurationSettings.AppSettings["connString"]);
myConnection.Open();

const string SQL = @"SELECT DivisionID, Name FROM Divisions ORDER BY Name";
OleDbCommand myCommand = new OleDbCommand(SQL, myConnection);

ddlDivision.DataSource = myCommand.ExecuteReader();
ddlDivision.DataBind();

myConnection.Close();

// now, select the appropriate division
int currentDivisionID = Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "DivisionID"));
ListItem li = ddlDivision.Items.FindByValue(currentDivisionID.ToString());
if (li != null) li.Selected = true;

// finally, populate the department DDL based on the selected division
int currentDepartmentID = Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "DepartmentID"));
if (li != null) li.Selected = true;

DropDownList ddlDepartment = (DropDownList) e.Item.FindControl("ddlDepartment");

PopulateDepartmentBasedOnDivision(ddlDepartment, ddlDivision, currentDepartmentID);
}
}

private void PopulateDepartmentBasedOnDivision(DropDownList ddlDepartment, 
dropDownList ddlDivision, int currentDepartmentID)
{
int divID = Convert.ToInt32(ddlDivision.SelectedValue);

// connect to Access DB
OleDbConnection myConnection = new OleDbConnection(
                    ConfigurationSettings.AppSettings["connString"]);
myConnection.Open();

const string SQL = @"SELECT DepartmentID, Name FROM Departments 
                    WHERE DivisionID = @DivisionID ORDER BY Name";

OleDbCommand myCommand = new OleDbCommand(SQL, myConnection);
myCommand.Parameters.Add(new OleDbParameter("@DivisionID", divID));

ddlDepartment.DataSource = myCommand.ExecuteReader();
ddlDepartment.DataBind();

myConnection.Close();

// now, select the appropriate division
ListItem li = ddlDepartment.Items.FindByValue(currentDepartmentID.ToString());
if (li != null) li.Selected = true;
} 

The DataGrid1_ItemDataBound event handler fires once for each item being bound to the DataGrid. If the current item being bound is the editable item, then the DropDownLists are appropriately populated and have their selected items set based on the employee's data. (e.Item.DataItem is a reference to the data being bound to the DataGrid row that triggered the ItemDataBound event.)

The PopulateDepartmentBasedOnDivision() method is a helper method that populates the contents of the Department DropDownList based on the currently selected Division DropDownList value. The reason I separated this functionality into a separate method, rather than having it embedded in the DataGrid1_ItemDataBound event handler, is so that its functionality can be reused when a user selects a different Division.

Recall that when a user selects a different Division, the Web Form is automatically posted back (since the Division DropDownList's AutoPostBack property is set to True) and the ddlDivision_SelectedIndexChanged event handler fires. From this event handler, all that we have to do is get a reference to the Division and Department DropDownLists, and then call the PopulateDepartmentBasedOnDivision() method. The following code illustrates how this is accomplished:

protected void ddlDivision_SelectedIndexChanged(object sender, EventArgs e)
{
// get a reference to the Department DDL for this row
DropDownList ddlDivision = (DropDownList) sender;
DataGridItem dgi = (DataGridItem) ddlDivision.Parent.Parent;
DropDownList ddlDepartment = (DropDownList) dgi.FindControl("ddlDepartment");

PopulateDepartmentBasedOnDivision(ddlDepartment, ddlDivision, -1);
} 

The thing to note here is that the ddlDivision_SelectedIndexChanged event handler receives a reference to the DropDownList that triggered the event (sender). To get the associated Department DropDownList for the Division DropDownList, I first accessed the DataGridItem that contains both DropDownLists. Then I used FindControl() to reference the Department DropDownList. (Alternatively, I could have accessed the DataGrid's Items collection based on the EditItemIndex.)

Fully Editable Grid

Introduction

Along with sorting and paging features, the DataGrid control provides functionality for editing its data, one row at a time. While the editing features of the DataGrid are relatively easy to implement, the fact that it constrains the user to editing one row at a time can lead to excessive clicks for scenarios where the user needs to update large amounts of data on the DataGrid. A better approach for such situations would be to make the entire DataGrid editable, with a single Button at the bottom that, when clicked, would update the entire contents of the DataGrid. Crafting a fully-editable DataGrid merely requires two steps:

  1. Using a TemplateColumn for each editable DataGrid column and adding the editable user interface in the ItemTemplates for each TemplateColumn,
  2. Creating an "Update All Records" Button Web control that, when clicked, iterates through the DataGrid's items, sending an UPDATE statement to the database for each record.

Making the Entire DataGrid Editable

The first step in creating a fully-editable DataGrid is having the DataGrid render all of its rows as editable. Rather than use the DataGrid's default editing capabilities, we need to make each record display its editable interface. We can accomplish this by making each editable column in the DataGrid a TemplateColumn, defining the editable interface in the ItemTemplate. That is, in a DataGrid that utilizes the standard editing technique, whatever markup you'd put in a TemplateColumn's EditItemTemplate should instead be placed in the ItemTemplate for our fully-editable DataGrid.

The following code illustrates how to accomplish the task of creating a fully-editable DataGrid user interface. The DataGrid shown here displays data from the ASPFAQs.com database, and includes information about the first several FAQs. It lists each of these FAQ's ID, Category, Question, and who submitted the question/answer. Note that the ID and category here are created as BoundColumns, thereby making them read-only. The Question and Submitted By columns, however, are editable for each record since they are implemented as TemplateColumns with the appropriate editing interface defined in their ItemTemplates.

<asp:datagrid id="dgPopularFAQs" runat="server"
	AutoGenerateColumns="False"
	...>
	
  <Columns>
    <asp:BoundColumn DataField="FAQID" ItemStyle-Width="10%"
			ItemStyle-HorizontalAlign="Center" HeaderText="FAQ ID" />

    <asp:BoundColumn DataField="CategoryName" HeaderText="Category" />
	    
    <asp:TemplateColumn HeaderText="Question">
      <ItemTemplate>
        <asp:TextBox runat="server" id="txtDescription" Columns="75" 
             Text='<%# Container.DataItem("Description") %>' />
      </ItemTemplate>
    </asp:TemplateColumn>
	    
    <asp:TemplateColumn HeaderText="Submitted By">
      <ItemTemplate>
        <asp:TextBox runat="server" id="txtSubmittedBy"
             Text='<%# Container.DataItem("SubmittedByName") %>' />
      </ItemTemplate>
    </asp:TemplateColumn>

  </Columns>	
</asp:datagrid>

Saving All Changes to the Database

Now that we have a DataGrid with a completely editable interface, all that remains is writing a bit of code to iterate through the DataGrid's records and update the database accordingly. To accomplish this we'll add an "Update All" Button Web control to the page and add the necessary code in the Button Web control's Click event handler.

We can loop through each row in the DataGrid by enumerating the DataGrid's Items collection. Each DataGrid row is represented by a DataGridItem class instance. For each item, we'll need to access each of its editable columns' values. We can do this through the FindControl() method, passing in the ID of the Web control for the column's editable interface.

For each record in the DataGrid, once we have read in the TemplateColumns' values we need to issue an UPDATE statement to save any changes made back to the underlying database table. To accomplish this we'll need to be able to have information that uniquely identifies each row. If the data you are displaying has a single primary key field, you can just use the DataGrid's DataKeyField property to track the unique ID for each record. The pseudocode for doing an "Update All" will look like the following:

For Each record in the DataGrid
  Read in the values from the TemplateColumns
  
  Issue an UPDATE statement to the database
Next

The actual code isn't much more complex than the pseudocode, and is shown below. This code should be placed in the "Update All" Button Web control's Click event handler.

'Create connection and command objects
Dim myConnection as New SqlConnection(connection string)
Dim myCommand as New SqlCommand(strSQL, myConnection)

Dim dgi as DataGridItem
For Each dgi in dgPopularFAQs.Items
  'Read in the Primary Key Field
  Dim id as Integer = Convert.ToInt32(dgPopularFAQs.DataKeys(dgi.ItemIndex))
  Dim question as String = CType(dgi.FindControl("txtDescription"), TextBox).Text
  Dim submittedBy as String = CType(dgi.FindControl("txtSubmittedBy"), TextBox).Text
      
  'Issue an UPDATE statement...
  Dim updateSQL as String = "UPDATE TableName SET Question = @Question, " & _
              "SubmittedByName = @SubmittedByName WHERE FAQID = @ID"
  myCommand.Parameters.Clear()
  myCommand.Parameters.Add("@Question", question)
  myCommand.Parameters.Add("@SubmittedByName", submittedBy)
  
  myCommand.ExecuteNonQuery()
Next

When the user clicks the "Update All" Button, the page is posted back, the rows of the DataGrid are enumerated, and for each record an UPDATE statement is reissued to the database.

Bi-Directional Sorting Revisited

Introduction

In this part we'll look at an alternative approach to implementing bi-directional sorting, one that doesn't require any hard-coded mapping and therefore can easily be reused. We'll also look at a simple method that will enhance the appearance of our bi-directional sortable DataGrid, adding little up and down arrows on the column header that the grid's data is sorted by.

Storing Sorting Information in the ViewState

As you know, in a sortable DataGrid each sortable column has a SortExpression property that is passed to the DataGrid's SortCommand event handler when that particular column's sort heading is clicked. Typically the SortExpression will contain the actual SQL syntax that you want to have injected into the ORDER BY clause. For bi-directional sortable DataGrids, we need to be able to dynamically add the DESC or ASC depending on whether or not we want to sort in ascending or descending order. One technique to implement a bi-directional sortable DataGrid checks the SortExpression property in the SortCommand event handler. If it ended with ASC it changed the clicked DataGrid column's SortExpression, replacing the ASC with DESC; similarly, if the SortExpression already ended with DESC it was replaced with ASC. While this approach definitely worked, one downside was that in order to set the correct DataGrid column's SortExpression we needed to provide a mapping from the SortExpression property passed in and the corresponding index in the DataGrid's Columns collection. With this hard-coded mapping, the code would have to be customized for each bi-directional sortable DataGrid you created.

A better approach is to use the ViewState to track both the column to sort by and the direction. This technique involves maintaining two page-level properties which can be namedSortExpression, a string, and SortAscending, a Boolean. These two properties simply use the ViewState StateBag as their backing store, thereby ensuring that the values are remembered across postbacks. Then, in the SortCommand event handler I first check to see if the column the data is currently sorted by has already been clicked again. If so, I toggle the SortAscending property, thereby changing the sort direction. I finish by setting the SortExpression property maintained in ViewState to the SortExpression value passed into the SortCommand event handler.

'The Page-level properties that write to ViewState
Private Property SortExpression() As String
    Get
        Dim o As Object = viewstate("SortExpression")
        If o Is Nothing Then
            Return String.Empty
        Else
            Return o.ToString
        End If
    End Get
    Set(ByVal Value As String)
        viewstate("SortExpression") = Value
    End Set
End Property

Private Property SortAscending() As Boolean
    Get
        Dim o As Object = viewstate("SortAscending")
        If o Is Nothing Then
            Return True
        Else
            Return Convert.ToBoolean(o)
        End If
    End Get
    Set(ByVal Value As Boolean)
        viewstate("SortAscending") = Value
    End Set
End Property

'The SortCommand event handler
Private Sub dgPActivities_SortCommand(ByVal source As Object, _
   ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) _
   Handles dgPActivities.SortCommand
    'Toggle SortAscending if the column that the data was sorted by has
    'been clicked again...
    If e.SortExpression = Me.SortExpression Then 
      SortAscending = Not SortAscending
    Else
      SortAscending = True
    End If
    
    'Set the SortExpression property to the SortExpression passed in
    Me.SortExpression = e.SortExpression

    BindData()  'rebind the DataGrid data
End Sub

The BindData() method contains the code that grabs the appropriately-sorted data from the database and binds it to the DataGrid.

Improving the Appearance

At this point we have a working bi-directional sortable DataGrid, however there are no visual cues to indicate how the data is sorted. Many websites that offer bi-directional sortable data display a little arrow or icon next to the column that the data is sorted by, depending on whether the column sorted by is sorted in ascending or descending order.

The following method, UpdateColumnHeaders(DataGrid), takes in as input a DataGrid and iterates through its Columns collection, looking for the DataGrid column by which the data is sorted. Once it finds that DataGrid column it adds to it an image tag of either an up or down arrow, depending on if the data is sorted in ascending or descending order. Before adding the up or down arrow, a regular expression is used to strip out any image tag markup that might be present.

Sub UpdateColumnHeaders(ByVal dg As DataGrid)
   Dim c As DataGridColumn
   For Each c In dg.Columns
      'Clear any <img> tags that might be present
      c.HeaderText = Regex.Replace(c.HeaderText, "\s<.*>", String.Empty)

      If c.SortExpression = SortExpression Then
          If SortAscending Then
             c.HeaderText &= " <img src=""/images/up.gif"" border=""0"">"
          Else
             c.HeaderText &= " <img src=""/images/down.gif"" border=""0"">"
          End If
      End If
   Next
End Sub

To use this method simply call it from the BindData() method prior to binding the data to the DataGrid.

Conclusion (and Making this Approach Reusable)

You could easily move the sorting properties and UpdateColumnHeaders(DataGrid) method to a custom base class. If you have a page, then, that needs to provide a bi-directional sortable DataGrid you simply have that page inherit from the custom base class page. With a bit of refactoring of the BindData() method and SortCommand event handler you could end up with a means to create a bi-directional sortable DataGrid with just a couple lines of code.

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.