Including a Column Total Line in a DataGrid Control...

Date: February 3, 2003

The question is how to display a total line in a DataGrid control. It is quite easy to do by making use of the ItemDataBound event. As usual we will use an aspx page to define our grid.

We are setting a lot of properties of the datagrid, but most of those are to make it look pretty. You do need to notice several things, however. First note that we are setting ShowFooter to True, and settings properties for the FooterStyle. The footer is where our total line will be so we make sure we have one. We are also setting OnItemDataBound equal to "dtgOrderDetails_ItemDataBound". By doing that we are creating a place where we can compute a running total while the grid is being bound to the data source. A minor item is that we are setting HorizontalAlign to "right" for both the ItemStyle and HeaderStyle of the Quantity column (which we will be totaling). I should point out that we are using the Order Details table of the Northwind database for our example. You may run the example from the link at the bottom of the page.

<%@ Page Language="vb" Src="DataGridTotals.aspx.vb" Inherits="DataGridTotals" %>

<html>
<body>
<asp:DataGrid id="dtgOrderDetails" runat="server"
              AutoGenerateColumns="False"
              ShowFooter="True"
              CellPadding="2" CellSpacing="0"
              BorderWidth="1"
              Gridlines="Both"
              HeaderStyle-Font-Name="Verdana"
              HeaderStyle-Font-Size="10pt"
              HeaderStyle-Font-Bold="True"
              HeaderStyle-ForeColor="White"
              HeaderStyle-BackColor="Coral"
              HeaderStyle-HorizontalAlign="Right"
              ItemStyle-Font-Name="Verdana"
              ItemStyle-Font-Size="10pt"
              ItemStyle-HorizontalAlign="Right"
              FooterStyle-Font-Name="Verdana"
              FooterStyle-Font-Size="10pt"
              FooterStyle-Font-Bold="True"
              FooterStyle-ForeColor="White"
              FooterStyle-BackColor="Coral"
              FooterStyle-HorizontalAlign="Right"
              OnItemDataBound="dtgOrderDetails_ItemDataBound">
  <Columns>
    <asp:BoundColumn HeaderText="Order ID" DataField="orderid" />
    <asp:BoundColumn HeaderText="Product ID" DataField="productid" />
    <asp:BoundColumn HeaderText="Unit Price" DataField="unitprice" />
    <asp:BoundColumn HeaderText="Quantity" DataField="quantity"
                    ItemStyle-HorizontalAlign="Right"
                    HeaderStyle-HorizontalAlign="Right" />
  </Columns>
</asp:DataGrid>
</body>
</html>

Next is our code-behind file where the action happens. The first sub-routine in the code-behind file is "CalculateTotal". This is where our total (theTotal) gets incremented with the quantity value as each row is bound to the grid. Also please note the dtgOrderDetails_ItemDataBound event handler code. Using the Item.ItemType property we are checking to see if the ItemType is an Item or an AlternatingItem. Even though we did not use the AlternatingItem property in our grid on the aspx page, it is a good idea to write code that will work with or without AlternatingItems. In the line just above the ElseIf we are calling the CalculateTotal routine. So as each row is bound, we check to make sure it is either an Item or an AlternatingItem row of the grid, then we pass the quantity (item 3 in our zero-based system) value to the routine which increments our running total.

Following the ElseIf we check to see if the row is the footer. If it is, we write "Total" in the first (0) column and our total value for quantity in the fourth (3) column of the grid. That's all there is to it! The rest of the code is the usual database connection and accessing the table in the Page_Load event.

Imports System
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class DataGridTotals : Inherits Page

  Private theTotal As double = 0
  Protected dtgOrderDetails As DataGrid

  Private Sub CalculateTotal(theQuantity As String)
    theTotal += Integer.Parse(theQuantity)
  End Sub

  Public Sub dtgOrderDetails_ItemDataBound(sender As Object, e As DataGridItemEventArgs)
    If e.Item.ItemType = ListItemType.Item Or _
      e.Item.ItemType = ListItemType.AlternatingItem Then
      e.Item.Cells(2).Text = string.Format("{0:c}", Convert.ToDouble(e.Item.Cells(2).Text))
      CalculateTotal(e.Item.Cells(3).Text)
    ElseIf(e.Item.ItemType = ListItemType.Footer)
      e.Item.Cells(0).Text="Total"
      e.Item.Cells(3).Text = theTotal
    End If
  End Sub

  Protected Sub Page_Load(sender As object, e As EventArgs)
    Dim objConn As SqlConnection = New SqlConnection( _
      ConfigurationSettings.AppSettings("ConnectionString"))
    Dim objCmd As New SqlCommand("SELECT OrderID, ProductID, " _
      & "UnitPrice, Quantity " _
      & "FROM [Order Details] WHERE ProductID = 5", objConn)

    Try
      objConn.Open()
      dtgOrderDetails.DataSource = objCmd.ExecuteReader()
      dtgOrderDetails.DataBind()
      objConn.Close()
    Catch exc As Exception
      Response.Write(exc.ToString())
    End Try
  End Sub
End Class

I hope you are now convinced of how easy it is to add a total row to a DataGrid control.

You may download the code here.