Wednesday 13 March 2013

How to Display GrandTotal in gridview with insert,update,delete operation 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 Question in You 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 page view so finally your Grid view 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.
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 insert,update,delete in gridview using LINQ please click here insert,update,delete using Linq

to show Example of insert,update,delete in gridview using Naming Container please click here Naming Container for insert update Delete in Gridview

to show Example of insert,update,delete in gridview using Modal Popup please click here insert,update,delete in Modal Popup

 to show Example of insert,update,delete in gridview using Stored Procedure please click here insert,update,delete through stored Procedure

  to show Example of insert,update,delete in XML File and bind to Gridview please click here insert,upadte,delete in XML File

  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:-

<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="bill.aspx.cs" Inherits="bill" %>
<asp:Content ID="Content2" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <script type="text/javascript">
        function confirmSubmit() {

            var agree = confirm("Do you really want to Delete Record?");

            if (agree)

                return true;

            else

                return false;

        }

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

         DataKeyNames="id"  AutoGenerateColumns="False"
                  
        

          OnRowDeleting="DeleteRecord"
        onrowcancelingedit="GridView1_RowCancelingEdit"
        onrowediting="GridView1_RowEditing" onrowupdating="GridView1_RowUpdating"
        onrowdatabound="GridView1_RowDataBound1" ShowFooter="True" >

            <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="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="lblgrandtotal" runat="server" Text=' <%# Eval("total") %>'></asp:Label>
                  
                </FooterTemplate>
               </asp:TemplateField>
                <asp:TemplateField>
                <ItemTemplate>
            <asp:ImageButton ID="ImageButton2" ImageUrl="~/images/del.jpeg" 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="~/images/edit.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>

</asp:Content>



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
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
            grid();
    }
    protected 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 ds = 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(ds);
        GridView1.DataSource = ds;
        GridView1.DataBind();
        string total = cmd1.ExecuteScalar().ToString();
        /* foreach (GridViewRow r in GridView1.Rows)
         {
             Label lblgrandtotal1 = (Label)GridView1.Rows[r.RowIndex].Cells[5].FindControl("lblgrandtotal");
             //lblGTUnitPrice.Text = total.ToString();
             lblgrandtotal1.Text = 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.Footer)
        {

            Label lblGTUnitInStock = (Label)e.Row.FindControl("lblgrandtotal");

            lblGTUnitInStock.Text = to();
        }



    }
}




1 comments: