How to Display Total at the bottom of the Gridview?

Posted by Syed Shakeer Hussiain P in ASP.NET category on 5/21/2009 for Beginner level | Views : 30596

Rating: 3 out of 5

1 vote(s)

Post Article |

Search Articles |

Articles Home

In this Article I am going to explain you how to display a Toatal amount or Salary at the Footer of the Gridview.

First add one datagridevew on a webpage.Now i am going to retrive a data from a Table named 'emp'.This emp table has 3 columns
column name          DataType
-----------------------------------------------
id                          number
empname                varchar(50)
salary                     number

In Soucrce code .aspx page

create a Template in gridvew to display Data as follows in between the <asp:Gridview>and </asp:Gridview>

<asp:GridView ID="GridView1" runat="server" Style="z-index: 100; left: 271px; position: absolute;
top: 68px" AutoGenerateColumns="False" Height="165px" ShowFooter="True" CellPadding="4"
ForeColor="Black" GridLines="Vertical" Width="229px" BackColor="White" BorderColor="#DEDFDE"
BorderStyle="None" BorderWidth="1px">
<Columns>
<asp:TemplateField HeaderText="ID">
<ItemTemplate>
<asp:Label ID="idlbl" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"id")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="NAME">
<ItemTemplate>
<asp:Label ID="namelbl" runat="server" Text-Align="right" Text='<%#DataBinder.Eval(Container.DataItem,"empname")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="SALARY">
<ItemTemplate>
<asp:Label ID="sal1" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"salary")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#CCCC99" />
<RowStyle BackColor="#F7F7DE" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<PagerStyle ForeColor="Black" HorizontalAlign="Right" BackColor="#F7F7DE" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>

write a code for display data in grdview:

in aspx.cs:

protected void Page_Load(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("connectionstring");
conn.Open();
SqlDataAdapter ad = new SqlDataAdapter("select * from emp", conn);
DataSet ds = new DataSet();
ad.Fill(ds, "emp");
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}

Below is the simple output Image of a gridview:

Now we have calculate the Total Salary then have to dispaly Total Salary at the Bottom of the Gridview.By using <FooterTemplate><FooterTemplate> is used for the Last Line of our OutPut. eg:

<FooterTemplate>Last Line_outPut </FooterTemplate>

NOTE:If we are using footer template we have to set ShowFooter Property as True by selecting the datagrid.

In this Example I am going to calculate the sum of Salary column.This sum should be displayed in the salary column LastLine(bottom of the salary column).

I am using a method name as gettotal().here i am calculating the total salarys of employes.

public int gettotal()
{
SqlConnection conn = new SqlConnection("connectionstring");
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT SUM(salary) FROM emp", conn);
int totalsal = Convert.ToInt32(cmd.ExecuteScalar());
return totalsal;
}

Next we have to use this gettotal () method in <FooterTemplate>

<FooterTemplate>
<%# gettotal()%>
</FooterTemplate>

here <%# gettotal()%> will cal the gettotal() in aspx.cs page

we have to write use this FooterTemplate where we want a lastline output. Here I am using this FooterTemplate in salary column.

eg:

<asp:TemplateField HeaderText="SALARY">
<ItemTemplate>
<asp:Label ID="sal1" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"salary")%>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<%# gettotal()%>
</FooterTemplate>
</asp:TemplateField>

See the output Image

The aspx.page will look like as follows:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" Style="z-index: 100; left: 271px; position: absolute;
top: 68px" AutoGenerateColumns="False" Height="165px" ShowFooter="True" CellPadding="4"
ForeColor="Black" GridLines="Vertical" Width="229px" BackColor="White" BorderColor="#DEDFDE"
BorderStyle="None" BorderWidth="1px">
<Columns>
<asp:TemplateField HeaderText="ID">
<ItemTemplate>
<asp:Label ID="idlbl" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"id")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="NAME">
<ItemTemplate>
<asp:Label ID="namelbl" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"empname")%>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="namelbl" runat="server" Text="Total"></asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="SALARY">
<ItemTemplate>
<asp:Label ID="sal1" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"salary")%>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<%# gettotal()%>
</FooterTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#CCCC99" />
<RowStyle BackColor="#F7F7DE" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<PagerStyle ForeColor="Black" HorizontalAlign="Right" BackColor="#F7F7DE" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
&nbsp;&nbsp;
</div>
</form>
</body>
</html>

the complete asp.cs page is:

protected void Page_Load(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("connectionstring");
conn.Open();
SqlDataAdapter ad = new SqlDataAdapter("select * from emp", conn);
DataSet ds = new DataSet();
ad.Fill(ds, "emp");
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
gettotal();
}
 
public int gettotal()
{
SqlConnection conn = new SqlConnection("connectionstring");
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT SUM(salary) FROM emp", conn);
int totalsal = Convert.ToInt32(cmd.ExecuteScalar());
return totalsal;
}

Plz give me Feedback about this Article.

About the Author

Full Name: Syed Shakeer Hussiain P
Member Level:
Member Status: Member
Member Since: 2/5/2009 3:12:18 AM
Country: India
Syed Shakeer Hussain
http://www.dotnetfunda.com
Shakeer Hussain has completed his Master of Computer Applications degree from Deccan College of engg and technology of Osmania University.He is a MVM of www.dotnetspider.com.He has good experience in the areas of ASP.NET, C#.NET, VB.NET, SQL SERVER 2000/2005 and Windows Mobile. He has worked in Windows Mobile,Web Applicatin and ERP projects.

Bookmark It

Login to vote for this post.

Latest Articles

Comments or Responses

Posted by: Avicool08 on: 5/21/2009

Hey my sal is not 7k.....

never mind... its a good article...

Keep it up dude...

Posted by: RoOoSa on: 2/26/2011 | Points: 25

hi

I try to do this

public int gettotal()
{
SqlConnection conn = new SqlConnection("connectionstring");
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT SUM(salary) FROM emp", conn);
int totalsal = Convert.ToInt32(cmd.ExecuteScalar());

return totalsal;}

to show the sum in textbox or label and it's is work ok for me , but I got error when the gridview is empty or no row or value

Can you help me ?

Comment using

(Author doesn't get notification)