Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

FREE 12 month online training for ASP.NET & MS Expression Studio and a Free copy of MS Expression Web with Windows Server Purchase
How to display column total on GridView Footer in ASP.NET

Author: Nikki Smith
Download Source Code : 1390_DisplayTotalInGridViewFooter.zip

In this article, I will show you how you can display total of integer or decimal values in a column on GridView Footer in ASP.NET.

 

RowDataBound event of GridView control can be used to display total of a column in footer template. If you are using paging of GridView control then you also have to handle PageIndexChanging event of Gridview control. Two types of totals can be displayed in footer template of GridView control. First you can display page total of a column and second you can display grand total of a column of  all GridView pages. Calculating grand total is easy, you can write query to get sum of the column from database and can display it on footer. Page total is bit tricky and I will show you in this article that how you can display page total. I am using NORTHWIND database and Products table to get data for this example.

 

  1. Create a new Empty Web Site in Visual Studio 2010 either in Visual Basic or Visual C#.
  2. Add a Web Form in the Web Site. No Need to change name of the Page
  3. Add a GridView control in Web Form and write code below in GridView.

    <asp:GridView ID="GridView1" AutoGenerateColumns="false" ShowFooter="true"
                runat="server" onrowdatabound="GridView1_RowDataBound" AllowPaging="True"
                onpageindexchanging="GridView1_PageIndexChanging">
            <Columns>
                <asp:TemplateField HeaderText="ProductID">
                    <ItemTemplate>
                        <asp:Label ID="lblProductID" runat="server" Text='<%# Eval("ProductID") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="ProductName">
                    <ItemTemplate>
                        <asp:Label ID="lblProductName" runat="server" Text='<%# Eval("ProductName") %>'></asp:Label>
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:Label ID="lblTotal" Font-Bold="true" runat="server" Text="Total"></asp:Label>
                    </FooterTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="UnitPrice">
                    <ItemTemplate>
                        <asp:Label ID="lblUnitPrice" runat="server" Text='<%# Eval("UnitPrice") %>'></asp:Label>
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:Label ID="lblTotalUnitPrice" Font-Bold="true" runat="server"></asp:Label>
                    </FooterTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="UnitsInStock">
                    <ItemTemplate>
                        <asp:Label ID="lblUnitsInStock" runat="server" Text='<%# Eval("UnitsInStock") %>'></asp:Label>
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:Label ID="lblTotalUnitsInStock" Font-Bold="true" runat="server"></asp:Label>
                    </FooterTemplate>
                </asp:TemplateField>
            </Columns>
    </asp:GridView>
     
     
  4. Namespaces used in the code

    Visual Basic
     
    Imports System.Data
    Imports System.Data.SqlClient
     
    Visual C#

    using System.Data;
    using System.Data.SqlClient;
     
  5. Write code below in code behind file

    ProductsData() functions is written to get data from Products table of NORTHWIND database in a DataSet object. In Page Load event, GridView in Bound to ProductsData() function. Two variables are declared before the RowDataBound Event of GridView to show total of UnitPrice and UnitsInStock. In RowDataBound Event, if the RowType is DataRow then UnitPrice and UnitInStock of a page is added and if the RowType is Footer then these values are displayed on Label controls. At the end, PageIndexChanging event is handled.
     
    Visual Basic
     
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        GridView1.DataSource = ProductsData()
        GridView1.DataBind()
    End Sub
     
    Public Function ProductsData() As DataSet
        Dim text As String = "SELECT ProductID, ProductName, UnitPrice, UnitsInStock FROM Products"
        Dim connString As String = "Data Source=YourServer;Initial Catalog=NORTHWIND;Integrated Security=True"
        Dim conn As New SqlConnection(connString)
        Dim cmd As New SqlCommand(text, conn)
        conn.Open()
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        da.Fill(ds)
        conn.Close()
        Return ds
    End Function
     
    Private totalUnitPrice As Decimal = 0
    Private totalUnitInstock As Integer = 0
     
    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs) Handles GridView1.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            totalUnitPrice += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "UnitPrice"))
            totalUnitInstock += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "UnitsInStock"))
        End If
        If e.Row.RowType = DataControlRowType.Footer Then
            Dim lblGTUnitPrice As Label = DirectCast(e.Row.FindControl("lblTotalUnitPrice"), Label)
            Dim lblGTUnitInStock As Label = DirectCast(e.Row.FindControl("lblTotalUnitsInStock"), Label)
            lblGTUnitPrice.Text = totalUnitPrice.ToString()
            lblGTUnitInStock.Text = totalUnitInstock.ToString()
        End If
    End Sub
     
    Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs) Handles GridView1.PageIndexChanging
        GridView1.PageIndex = e.NewPageIndex
        GridView1.DataSource = ProductsData()
        GridView1.DataBind()
    End Sub
     
     
    Visual C#
     
    protected void Page_Load(object sender, EventArgs e)
    {
        GridView1.DataSource = ProductsData();
        GridView1.DataBind();
    }
     
    public DataSet ProductsData()
    {
        string text = "SELECT ProductID, ProductName, UnitPrice, UnitsInStock FROM Products";
        string connString = "Data Source=YourServer;Initial Catalog=NORTHWIND;Integrated Security=True";
        SqlConnection conn = new SqlConnection(connString);
        SqlCommand cmd = new SqlCommand(text, conn);
        conn.Open();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        conn.Close();
        return ds;
    }
     
    decimal totalUnitPrice = 0;
    int totalUnitInstock = 0;
     
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            totalUnitPrice += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "UnitPrice"));
            totalUnitInstock += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "UnitsInStock"));
        }
        if (e.Row.RowType == DataControlRowType.Footer)
        {
            Label lblGTUnitPrice = (Label)e.Row.FindControl("lblTotalUnitPrice");
            Label lblGTUnitInStock = (Label)e.Row.FindControl("lblTotalUnitsInStock");
            lblGTUnitPrice.Text = totalUnitPrice.ToString();
            lblGTUnitInStock.Text = totalUnitInstock.ToString();
        }
    }
     
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        GridView1.DataSource = ProductsData();
        GridView1.DataBind();
    }
     
  6. Now you can see website in your browser
   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to use Transaction for MS Access database in ASP.NET

Disclaimer - Privacy
© 2002-2014 DevASP.net