Currency Format for Negative numbers

Example of showing total in GridView's footer

In this example, I will use popular Northwind database. It is not required, if you don't have this database already installed, you can use any other database or even create new one with one table and few rows. Only thing important is that you have a table with one numeric column. In this example, numeric column "ProductSales" is type of float and contains sales amounts for several products. Web form output will look like this:


Drag one GridView and one SqlDataSource control from toolbox to the web form. SqlDataSource control defines data source and GridView will show the data. Here is the markup code of GridView and SqlDataSource control:

<asp:GridView ID="GridView1" runat="server" DataSourceID="sdsNorthwind" 
  AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" 
  GridLines="None" ShowFooter="True" onrowdatabound="GridView1_RowDataBound">
  
  <%--This example consists of two columns, second column is numeric and formatted as currency--%>
  <Columns>
  <asp:BoundField DataField="ProductName" HeaderText="Product Name" FooterText="Total:" />
  <asp:BoundField DataField="ProductSales" HeaderText="Product Sales" 
  DataFormatString="{0:c}" />
  </Columns>
  
  <%--Set styles to get better GridView appearance--%>
  <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
  <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
  <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
  </asp:GridView>
  
  <%--SqlDataSource control opens [Sales by Category] view of Northwind database--%>
  <asp:SqlDataSource ID="sdsNorthwind" runat="server" 
  ConnectionString="<%$ ConnectionStrings:NorthwindConnStr %>" 
  SelectCommand="SELECT [ProductName] ,[ProductSales]
   FROM [NORTHWIND].[dbo].[Sales by Category] WHERE CategoryID = 1"></asp:SqlDataSource>

Important thing is to set ShowFooter property to True because by default Footer element is not displayed. This markup code will load data from database and show records in GridView, but Total value is still not displayed. Standard GridView hasn't some property to calculate aggregate functions automatically and we can't finish this task using only markup. To show total, we need to calculate it using custom ASP.NET server side code.

In this approach, we'll use GridView RowDataBound event. Code would look like this:

Partial Class GridView_Total_VB
  Inherits System.Web.UI.Page
  
    ' Declare variable used to store value of Total
    Private TotalSales As Decimal = 0.0
  
    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
      ' check row type
      If e.Row.RowType = DataControlRowType.DataRow Then
        ' if row type is DataRow, add ProductSales value to TotalSales
        TotalSales += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "ProductSales"))
      ElseIf e.Row.RowType = DataControlRowType.Footer Then
        ' If row type is footer, show calculated total value
        ' Since this example uses sales in dollars, I formatted output as currency
        e.Row.Cells(1).Text = String.Format("{0:c}", TotalSales)
      End If
    End Sub
  End Class