Introduction:
In this Post I will explain how to Display total of any numeric column in gridview pagefooter, and also display Grand total.In this case I got requirement to display per page total of numeric column as well as grand total for all records. It means that you have to calculate page total for each page and also calculate grand total of all records at every page so we can put it on pagefooter of grid view
To implement this requirement I had created OnRowDataBound event of gridview to calculate per page total and also for grand total.
Note:-
In this example I use inner join queries because I am taking data from two different tables with using sql sum() function and Groupby clause. If you have single table you can avoid it. Don’t forgot to replace connection string with your database connection string.
Source Code(in asp.net)
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ProductView2.aspx.cs" Inherits="ProductView" %>
<!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 runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
ForeColor="#333333" GridLines="None" DataKeyNames="pid" OnRowDataBound="GridView1_RowDataBound"
ShowFooter="True" AllowPaging="True" OnPageIndexChanging="GridView1_PageIndexChanging"
PageSize="3">
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<Columns>
<asp:TemplateField HeaderText="Pid" Visible="false">
<ItemTemplate>
<asp:Label ID="lpid" runat="server" Text='<% #Eval("pid")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="lpname" runat="server" Text='<% #Eval("pname")%>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
Grand Total:-
<asp:Label ID="lblgrt" runat="server"></asp:Label></FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Description">
<ItemTemplate>
<asp:Label ID="ldes" runat="server" Text='<% #Eval("des")%>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
Page Total:-</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Total">
<ItemTemplate>
<asp:Label ID="ltotal" runat="server" Text='<% #Eval("EXpr1")%>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lgtotal" runat="server" Text="Label"></asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="lbdelete" runat="server" OnClick="deleteDetail" CommandArgument='<% #Eval("pid")%>'>Delete</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
Grand Total:-
<asp:Label ID="lb" runat="server"></asp:Label>
<br />
</div>
</form>
</body>
</html>
Code Behind(in C#)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
public partial class ProductView : System.Web.UI.Page
{
static string str = @"Yor Conection string";
public static SqlConnection con = new SqlConnection(str);
decimal tot,gt;
//events
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataTable dt = Data_of_Grid("SELECT aby_Productnew.pname, aby_Productnew.des, aby_ProductRate.pid, SUM(aby_ProductRate.total) AS Expr1 FROM aby_ProductRate INNER JOIN aby_Productnew ON aby_ProductRate.pid = aby_Productnew.pid GROUP BY aby_ProductRate.pid, aby_Productnew.pname, aby_Productnew.des");
for (int i = 0; i < dt.Rows.Count; i++)
{
gt += Convert.ToDecimal(dt.Rows[i][3]);
}
tot = 0;
GridView1.DataSource = Data_of_Grid("SELECT aby_Productnew.pname, aby_Productnew.des, aby_ProductRate.pid, SUM(aby_ProductRate.total) AS Expr1 FROM aby_ProductRate INNER JOIN aby_Productnew ON aby_ProductRate.pid = aby_Productnew.pid GROUP BY aby_ProductRate.pid, aby_Productnew.pname, aby_Productnew.des");
GridView1.DataBind();
lb.Text = gt.ToString();
}
}
protected void deleteDetail(object sender, EventArgs e)
{
LinkButton lb = (LinkButton)sender;
Cmd_Non_Query("delete from aby_Productnew where pid='" + lb.CommandArgument+"'");
GridView1.DataSource = Data_of_Grid("SELECT aby_Productnew.pname, aby_Productnew.des, aby_ProductRate.pid, SUM(aby_ProductRate.total) AS Expr1 FROM aby_ProductRate INNER JOIN aby_Productnew ON aby_ProductRate.pid = aby_Productnew.pid GROUP BY aby_ProductRate.pid, aby_Productnew.pname, aby_Productnew.des");
GridView1.DataBind();
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
Control ctr = e.Row.FindControl("ltotal");
if (ctr != null)
{
Label l = (Label)ctr;
tot += Convert.ToDecimal(l.Text);
}
}
if (e.Row.RowType == DataControlRowType.Footer)
{
Label lbl = (Label)e.Row.FindControl("lblgrt");
lbl.Text = Cmd_Scaler("SELECT SUM(aby_ProductRate.total) AS Expr1 FROM aby_ProductRate").ToString();
Label lblamount = (Label)e.Row.FindControl("lgtotal");
lblamount.Text = tot.ToString();
}
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
tot = 0;
GridView1.DataSource = Data_of_Grid("SELECT aby_Productnew.pname, aby_Productnew.des, aby_ProductRate.pid, SUM(aby_ProductRate.total) AS Expr1 FROM aby_ProductRate INNER JOIN aby_Productnew ON aby_ProductRate.pid = aby_Productnew.pid GROUP BY aby_ProductRate.pid, aby_Productnew.pname, aby_Productnew.des");
GridView1.DataBind();
}
//sql Function
public DataTable Data_of_Grid(string q)
{
DataTable dt = new DataTable();
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand(q, con);
SqlDataAdapter sd = new SqlDataAdapter();
sd.SelectCommand = cmd;
sd.Fill(dt);
}
catch (Exception err)
{
//TODO
}
finally
{
con.Close();
}
return dt;
}
public void Cmd_Non_Query(string q)
{
try
{
SqlCommand cmd = new SqlCommand(q, con);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.ExecuteNonQuery();
}
catch (Exception err)
{
}
finally
{
con.Close();
}
}
public Object Cmd_Scaler(string q)
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand(q, con);
string val = string.Empty;
return cmd.ExecuteScalar();
con.Close();
}
}
1 comments:
pla help
Replyi hv table name product in dht i hv 3 field item,price,qty
nw i wnt to insert record dht match wid item field name bt update only price n qty wid increment & dcrement
thnx in advance
Post a Comment