Show the Sum of GridView Column Values in Footer in Asp.Net – C# and Vb.Net

We have discussed about Asp.Net GridView with examples before in our blog. In this article we will show you how to display a summary or the sum of values in a particular column, in the GridView footer row. Also known as running total of a column, these accumulated figures needs to be displayed below all the pages, if GridView Paging is set as “True”.

Page 1GridView 1st Page

Page 2GridView 2nd Page

We have come across many examples, where the total values shown at the bottom are the sum of the values of the active page. That is, page 1 will show running total of the 1st page; page 2 will display the total of 2nd page etc.

Using ‘ViewState [""]’, we can add and display a Grand Total either on every page’s footer or the last page itself. This may or may be the best solution, but it works without any issues.

The working Solution

First, create an SQL table and name it as Books. We will bind the GridView with the table’s data. This table has three columns namely the BookID, BookName and Price. We will display the total price per page along with the Grand total. (See image)

Create Table (Books)

Using SQL Server

CREATE TABLE [dbo].[Books](
    [BookID] [int] IDENTITY(1,1) NOT NULL,
    [BookName] [varchar](50) NULL,
    [Price] [numeric](18, 2) NULL,
    PRIMARY KEY CLUSTERED 
    ( [BookID] ASC ) ON [PRIMARY]
)

Add few rows of data in it, so you can set the GridView Paging as true. In fact you need more rows, at least 10 rows and set the GridView page size to ‘5’, so you can have ‘2’ pages to check for the running totals in each page with the Grand Total.

PageSize="5"

The Markup and GridView
<!DOCTYPE html>
<html>
<head>
    <title>GridView Footer Example</title>
</head>

<body>
    <form id="form1" runat="server">
    <div style="width:500px">
        
        <asp:GridView ID="grd" runat="server" Width="100%" 
            AutoGenerateColumns="false"
            AlternatingRowStyle-BackColor="#E9ECF1" 
            HeaderStyle-BackColor="white" 
            Font-Names="Arial" 
            RowStyle-HorizontalAlign="Center" 
            RowStyle-Height="22" 
            HeaderStyle-Height="25"
            FooterStyle-HorizontalAlign="Center" 
            FooterStyle-Font-Bold="true" 
            FooterStyle-ForeColor="#555555"
            ShowFooter="true" AllowPaging="true" PageSize="5" 
            OnPageIndexChanging="grd_PageIndexChanging" 
            OnRowDataBound="grd_RowDataBound">
                
            <Columns>
                <asp:TemplateField HeaderText="Book ID">
                    <ItemTemplate><%#Eval("BookID")%></ItemTemplate>
                </asp:TemplateField>
                    
                <asp:TemplateField HeaderText="Name of the Book">
                    <ItemTemplate><%#Eval("BookName")%></ItemTemplate>

                    <FooterTemplate>
                        <div style="padding:0 0 5px 0"><asp:Label Text="Page Total" runat="server" /></div>
                        <div><asp:Label Text="Grand Total" runat="server" /></div>
                    </FooterTemplate>

                </asp:TemplateField>
                    
                <asp:TemplateField HeaderText="Price ($)">
                    <ItemTemplate><asp:Label ID="lblTotalPrice" runat="server" Text='<%#Eval("Price")%>'>
                        </asp:Label></ItemTemplate>

                    <FooterTemplate>
                        <div style="padding:0 0 5px 0"><asp:Label ID="lblPageTotal" runat="server" /></div>
                        <div><asp:Label ID="lblGrandTotal" runat="server" /></div>
                    </FooterTemplate>

                </asp:TemplateField>
            </Columns>
        </asp:GridView>
    </div>
    </form>
</body>
</html>

Related: Bind data with GridView using SqlDataSource in Asp.Net

Before we proceed further with our example, few things that I thought would be important for you to understand about the GridView design properties.
1) I have set ShowFooter="true". This example has a running total at the footer of each row.
2) GridView paging is set as AllowPaging="true", also the size of page is set as ‘5’ (PageSize="5").
3) Events, such as OnPageIndexChanging and OnRowDataBound are declared.
4) Finally we have ‘two’ footer templates under the 2nd and 3rd columns each. The 3rd column’s footer will display the values for Page total and Grand Total respectively. <FooterTemplate></FooterTemplate>

Related: Search Records (Filter rows) in a GridView Control using Asp.Net FilterParameters

Code Behind (C#)
using System;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    Decimal dPageTotal;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        { 
            BindData(); 
        }
    }
    private void BindData()
    {
        DataTable dt = new DataTable();

        using (SqlConnection con = new SqlConnection("Data Source=dna;" +
            "Persist Security Info=False;" +
             "Initial Catalog=DNA_CLASSIFIED;" + 
             "User Id=sa;Password=demo;Connect Timeout=30;"))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = "SELECT *FROM Books";
                con.Open();

                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                sda.Fill(dt);

                // CALCULATE THE TOTAL PRICE AND HOLD THE VALUE IN A "VIEWSTATE".
                if (ViewState["TotalPrice"] == null)
                {
                    Decimal dPrice = 0;
                    for(int i=0; i <= dt.Rows.Count - 1; i++)
                    {
                        dPrice  += dt.Rows[i].Field<Decimal>(2);
                    }
                    ViewState["TotalPrice"] = dPrice;
                }
                // BIND QUERY RESULT WITH THE GRIDVIEW.
                grd.DataSource = dt; grd.DataBind();
            }
        }
    }

    // GRIDVIEW PAGING.
    protected void grd_PageIndexChanging(object sender, 
        System.Web.UI.WebControls.GridViewPageEventArgs e)
    {
        grd.PageIndex = e.NewPageIndex;
        BindData();
    }

    // CALCULATE AND SHOW PAGE TOTAL AND GRAND TOTAL WHILE BINDING THE ROWS.
    protected void grd_RowDataBound(object sender, 
        System.Web.UI.WebControls.GridViewRowEventArgs e)
    {
        // GET THE RUNNING TOTAL OF PRICE FOR EACH PAGE.
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            Label lblPgTotal = (Label)e.Row.FindControl("lblTotalPrice");
            dPageTotal += Decimal.Parse(lblPgTotal.Text);
        }

        // FINALLY, SHOW THE RUNNING AND GRAND TOTAL ON EACH PAGE.
        if (e.Row.RowType == DataControlRowType.Footer)
        {
            if (ViewState["TotalPrice"] != null && dPageTotal != 0)
            {
                // PAGE TOTAL.
                Label lblPageTotal = (Label)e.Row.FindControl("lblPageTotal");
                lblPageTotal.Text = dPageTotal.ToString("N2");

                //GRAND TOTAL.
                Label lblGrandTotal = (Label)e.Row.FindControl("lblGrandTotal");
                lblGrandTotal.Text = ViewState["TotalPrice"].ToString();
            }
        }
    }
}
Vb.Net
Option Explicit On
Imports System.Data
Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Dim dPageTotal As Double = 0

    Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) 
        Handles form1.Load

        If Not IsPostBack Then
            Me.BindData()
        End If
    End Sub

    Private Sub BindData()
        Dim dt As DataTable = New DataTable
        Using con As SqlConnection = _
            New SqlConnection ("Data Source=dna;Persist Security Info=False;" & _
            "Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=demo;Connect Timeout=30;")

            Dim strQuery As String = "SELECT *FROM Books"

            Using cmd As SqlCommand = New SqlCommand(strQuery)
                Dim sda As SqlDataAdapter = New SqlDataAdapter
                
                cmd.Connection = con : con.Open()
                sda.SelectCommand = cmd
                sda.Fill(dt)

                ' CALCULATE THE TOTAL PRICE AND HOLD THE VALUE IN A "VIEWSTATE".
                If ((ViewState("TotalPrice")) Is Nothing) Then
                    For i = 0 To dt.Rows.Count - 1
                        ViewState("TotalPrice") += dt.Rows(i).Item(2)   ' ITEM 2 IS THE PRICE.
                    Next
                End If

                grd.DataSource = dt : grd.DataBind()
            End Using
        End Using
    End Sub
    
    ' GRIDVIEW PAGING.
    Protected Sub grd_PageIndexChanging(ByVal sender As Object, 
            ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) 
            Handles grd.PageIndexChanging
            
        grd.PageIndex = e.NewPageIndex
        BindData()
    End Sub

    Protected Sub grd_RowDataBound(ByVal sender As Object, 
            ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) 
            Handles grd.RowDataBound
            
        ' GET THE RUNNING TOTAL OF PRICE FOR EACH PAGE.
        If e.Row.RowType = DataControlRowType.DataRow Then
            Dim lblTotalPrice As Label = CType(e.Row.FindControl("lblTotalPrice"), Label)
            dPageTotal += lblTotalPrice.Text
        End If

        ' FINALLY, SHOW THE RUNNING AND GRAND TOTAL ON EACH PAGE.
        If e.Row.RowType = DataControlRowType.Footer Then
            If Not (ViewState("TotalPrice") Is Nothing) Then
                ' PAGE TOTAL.
                Dim lblPageTotal As Label = CType(e.Row.FindControl("lblPageTotal"), Label)
                lblPageTotal.Text = Decimal.Parse(dPageTotal).ToString("N2")

                ' GRAND TOTAL.
                Dim lblGrandTotal As Label = CType(e.Row.FindControl("lblGrandTotal"), Label)
                lblGrandTotal.Text = Decimal.Parse(ViewState("TotalPrice")).ToString("N2")
            End If
        End If
    End Sub
End Class

That is it. If you find this article useful, then leave a message and please don’t forget to share it with your friends.

Thanks for reading.

← PreviousNext →


Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner

Related Posts: