Tuesday 19 March 2013

how to display pagetotal and grand total in gridview Footer Template in asp.net


                  



Description:-                


In this example we explain that how to Display PageTotal and GranTotal in your GridView.
Listen this thing you have a Qustion in Your Mind is that what is PageTotal and What is GrandTotal.so now we explain that suppose your Gridview Contain 10 pageview with 20 rows in each pageview so finally your Gridview can contain 200 rows.

So Pagetotal means  total of each pageview so in this case total of 20 Record because our Gridview contain 20 Record in each pageview.

And now the GrandTotal means the Total of All pageview so in this case total of 200 Record because our Griview can contain 10 Pageview with 20 record in each pageview so 20*10=200.

We also provide Facility for Edit and Delete Record and main Important point is that the calculation of PageTotal and GrandTotal are Automatically change as per record Deleted.
To do this we Create all Calculation in RowdataBound Event of the GridView are as Follow

if (e.Row.RowType == DataControlRowType.Footer)

        {

            Label rn = (Label)e.Row.FindControl("lblrn");

            Label gr = (Label)e.Row.FindControl("lblgrand");

            rn.Text = irn.ToString();

            gr.Text = to();

        }
 
Look at this It will Check that this RowType is Footer then it will Find Label in Footer in each row and assign Total to this Label. Here to is the Function that will Return the Total of all row in Gridview.

public string to()

    {

        string con = @"Data Source=SQLDB;Initial Catalog=Demo;User ID=Demod;Password=Demo1@";

        String q2 = "select sum(total) from manage";

        SqlConnection cn = new SqlConnection(con);

        SqlCommand cmd1 = new SqlCommand(q2, cn);

        cn.Open();

        string total = cmd1.ExecuteScalar().ToString();

        return total;

    }

So this type of Facility are very useful  In Billing System to Generate a Bill Report.


Here is some link that is very useful for same programming like :-

to show Example of Bind data from XML file to DataList click here Read/Insert from XML file and bind to DataList


MVC Registration form with all Validation in MVC client and server side validation in MVC4

Insert,Update,Delete in Gridview without postback CRUD operation without refresh the page

Bind data in Accordion Control from database bind Dynamic data in Accordion Control

inner zoom effect of image bind into gridview from database Display Inner zoom Effect of the image in Gridview
  to show Example of insert,update,delete in gridview using Three Tier Architecture please click here Three Tier Architecture For insert,update,Delete


bill.aspx:-

<asp:GridView ID="GridView1" runat="server"
        ForeColor="#333333" GridLines="None"

         DataKeyNames="id"  AutoGenerateColumns="False"
                    AllowPaging="true" PageSize="5"
        
OnRowDeleting="DeleteRecord"
        onrowcancelingedit="GridView1_RowCancelingEdit"
        onrowediting="GridView1_RowEditing" onrowupdating="GridView1_RowUpdating"
        onrowdatabound="GridView1_RowDataBound1" ShowFooter="True"
        onpageindexchanging="GridView1_PageIndexChanging"
         >

            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

            <RowStyle BackColor="#EFF3FB" />

            <EditRowStyle BackColor="#2ff1BF" />

            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />

            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />

            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

            <AlternatingRowStyle BackColor="White" />

            <Columns>

                <asp:BoundField DataField="id" HeaderText="ID" ReadOnly="True"  SortExpression="id" />

                <asp:TemplateField HeaderText="Image" SortExpression="Image">

                    <ItemTemplate>

                        <asp:Image ID="Image1" runat="server" Width="80" Height="80" ImageUrl='<%# Eval("image") %>' />

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="Product Name" SortExpression="pnm">

                    <ItemTemplate>

                        <%# Eval("pnm") %>

                    </ItemTemplate>

                    <FooterTemplate>
                  
                <asp:Label ID="lbl" runat="server" Text="page Total" ></asp:Label><br />
                    <asp:Label ID="Label1" runat="server" Text="Grand Total" ></asp:Label>
                </FooterTemplate>
              
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Prize" SortExpression="prize">

                    <ItemTemplate>

                        <%# Eval("prize") %>

                    </ItemTemplate>

                </asp:TemplateField>
                  
                <asp:TemplateField HeaderText="Quantity" SortExpression="quantity">

                    <ItemTemplate>

                        <%# Eval("quantity") %>

                    </ItemTemplate>

                    <EditItemTemplate>

                        <asp:TextBox ID="txtqu" runat="Server" Text='<%# Eval("quantity") %>'></asp:TextBox>

                    </EditItemTemplate>
              
                </asp:TemplateField>
            
                <asp:TemplateField HeaderText="Total">
                <ItemTemplate>

                    <asp:Label ID="grand" runat="server" Text=' <%# Eval("total") %>'></asp:Label>

                    </ItemTemplate>
              
                <FooterTemplate>
                   <asp:Label ID="lblrn" runat="server" ></asp:Label><br />
              
                    <asp:Label ID="lblgrand" runat="server" ></asp:Label>
                </FooterTemplate>
             
               </asp:TemplateField>
                <asp:TemplateField>
                <ItemTemplate>
            <asp:ImageButton ID="ImageButton2" ImageUrl="~/delete.png" runat="server" Width="50" Height="50" CommandName="Delete" ToolTip="Delete Record" OnClientClick="return confirmSubmit()"/>
            </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField>
            <ItemTemplate>
            <asp:ImageButton ID="ImageButton3" ImageUrl="~/ed.jpeg" CommandName="Edit" Width="50" Height="50" runat="server" ToolTip= "Update Record" />
            </ItemTemplate>
            <EditItemTemplate>
                <asp:Button ID="Button1" runat="server" Text="Update" CommandName="update" />
                <asp:Button ID="Button2" runat="server" Text="cancel" CommandName="cancel" />
            </EditItemTemplate>
            </asp:TemplateField>
    
            </Columns>    </asp:GridView>




bill.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
public partial class bill : System.Web.UI.Page
{
    int irn;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            grid();

        }

    }
    public void grid()
    {
        string con = @"Data Source=SQLDB;Initial Catalog=Demo;User ID=Demod;Password=Demo1@";
        //Menu inm = (Menu)this.Master.FindControl("Menu1");


        string q1 = "select * from manage";
        //String q2 = "select sum(total) from manage";
        DataSet dsOrders = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter();
        SqlConnection cn = new SqlConnection(con);

        SqlCommand cmd = new SqlCommand(q1, cn);
        // SqlCommand cmd1 = new SqlCommand(q2, cn);
        cn.Open();
        da.SelectCommand = cmd;
        cmd.ExecuteNonQuery();
        da.Fill(dsOrders);
        GridView1.DataSource = dsOrders;
        GridView1.DataBind();
        // igr = Convert.ToInt32(dsOrders.Tables[0].AsEnumerable().Sum(x => x.Field<int>("total")));



    }
    public string to()
    {
        string con = @"Data Source=SQLDB;Initial Catalog=Demo;User ID=Demod;Password=Demo1@";
        String q2 = "select sum(total) from manage";
        SqlConnection cn = new SqlConnection(con);
        SqlCommand cmd1 = new SqlCommand(q2, cn);
        cn.Open();
        string total = cmd1.ExecuteScalar().ToString();
        return total;


    }

    protected void DeleteRecord(object sender, GridViewDeleteEventArgs e)
    {

        int id = Int32.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());
        string con = @"Data Source=SQLDB;Initial Catalog=Demo;User ID=Demod;Password=Demo1@";
        //Menu inm = (Menu)this.Master.FindControl("Menu1");


        string q1 = "delete from manage where id = " + id;


        SqlConnection cn = new SqlConnection(con);

        SqlCommand cmd = new SqlCommand(q1, cn);

        cn.Open();

        cmd.ExecuteNonQuery();
        GridView1.EditIndex = -1;

        grid();

    }
    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        int id = Int32.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());



        GridViewRow row = GridView1.Rows[e.RowIndex];


        TextBox qu = (TextBox)row.FindControl("txtqu");

        string con = @"Data Source=SQLDB;Initial Catalog=Demo;User ID=Demod;Password=Demo1@";


        if (qu.Text.Length > 0)
        {

            string q1 = "update manage set  quantity = '" + qu.Text + "' where id = '" + id + "'";


            SqlConnection cn = new SqlConnection(con);

            SqlCommand cmd = new SqlCommand(q1, cn);

            cn.Open();

            cmd.ExecuteNonQuery();



            GridView1.EditIndex = -1;

            grid();
        }
        else
        {
            ClientScript.RegisterClientScriptBlock(this.GetType(), "key", "<script> alert('plz fill the value');</script>");
            qu.Focus();
        }


    }
    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        grid();

    }
    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        e.Cancel = true;
        GridView1.EditIndex = -1;
        grid();
    }


    protected void GridView1_RowDataBound1(object sender, GridViewRowEventArgs e)
    {

        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            //Label lblGTUnitInStock = (Label)e.Row.FindControl("lblgrandtotal");
            int a = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "total"));
            // Label lblGTUnitInStock = (Label)e.Row.FindControl("grand");

            irn += a;

        }

        if (e.Row.RowType == DataControlRowType.Footer)
        {
            Label rn = (Label)e.Row.FindControl("lblrn");
            Label gr = (Label)e.Row.FindControl("lblgrand");
            rn.Text = irn.ToString();
            gr.Text = to();
        }


    }
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        grid();
    }

}

0 comments:

Post a Comment