Calculate and display Total in GridView Footer while Edit using C# and VB.Net in ASP.Net

Last Reply 14 days ago By arunkurmi

Posted 14 days ago

how to add Sum function in editable grid view using asp c# 

I want to Sum with editable gridview which has text box. Total Sum display on footer of gridview.

while click on Edit button then textbox open and while we have fill textbox in gridview then total sum show on footer.

Posted 14 days ago

Hi indradeo,

Check this sample. now take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<asp:GridView ID="gvOrders" runat="server" AutoGenerateColumns="false" ShowFooter="true"
    OnRowCancelingEdit="gvOrders_RowCancelingEdit" OnRowEditing="gvOrders_RowEditing">
    <Columns>
        <asp:TemplateField HeaderText="Order ID">
            <ItemTemplate>
                <asp:Label ID="lblOrderID" Text='<%#DataBinder.Eval(Container.DataItem, "OrderID") %>'
                    runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Customer Name">
            <ItemTemplate>
                <asp:Label ID="lblCustomerID" Text='<%#DataBinder.Eval(Container.DataItem, "CustomerID") %>'
                    runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Price">
            <ItemTemplate>
                <asp:Label ID="lblPrice" Text='<%#DataBinder.Eval(Container.DataItem, "Freight") %>'
                    runat="server" />
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="txtPrice" Text='<%# DataBinder.Eval(Container.DataItem,"Freight") %>'
                    runat="server" OnTextChanged="ChangePrice" AutoPostBack="true" />
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:CommandField ShowEditButton="true" ShowCancelButton="true" ButtonType="Button" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindOrders();
    }
}    

protected void ChangePrice(object sender, EventArgs e)
{
    string price = (sender as TextBox).Text;
    int index = gvOrders.EditIndex;
    decimal total = 0;
    for (int i = 0; i < gvOrders.Rows.Count; i++)
    {
        if (i != index)
        {
            total += Convert.ToDecimal((gvOrders.Rows[i].FindControl("lblPrice") as Label).Text);
        }
    }
    total += Convert.ToDecimal(price);
    gvOrders.FooterRow.Cells[2].Text = total.ToString("N2");
}

protected void gvOrders_RowEditing(object sender, GridViewEditEventArgs e)
{
    this.gvOrders.EditIndex = e.NewEditIndex;
    this.BindOrders();
}

protected void gvOrders_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
    this.gvOrders.EditIndex = -1;
    this.BindOrders();
}

private void BindOrders()
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT TOP 3 OrderID,CustomerID,Freight FROM ORDERS", con))
        {
            cmd.CommandType = CommandType.Text;
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                sda.SelectCommand = cmd;
                DataTable dt = new DataTable();
                sda.Fill(dt);
                this.gvOrders.DataSource = dt;
                this.gvOrders.DataBind();

                decimal grandTotal = dt.AsEnumerable().Sum(row => row.Field<decimal>("Freight"));
                gvOrders.FooterRow.Cells[1].Text = "Grand Total";
                gvOrders.FooterRow.Cells[2].Text = grandTotal.ToString("N2");
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindOrders()
    End If
End Sub

Protected Sub ChangePrice(ByVal sender As Object, ByVal e As EventArgs)
    Dim price As String = (TryCast(sender, TextBox)).Text
    Dim index As Integer = gvOrders.EditIndex
    Dim total As Decimal = 0
    For i As Integer = 0 To gvOrders.Rows.Count - 1

        If i <> index Then
            total += Convert.ToDecimal((TryCast(gvOrders.Rows(i).FindControl("lblPrice"), Label)).Text)
        End If
    Next

    total += Convert.ToDecimal(price)
    gvOrders.FooterRow.Cells(2).Text = total.ToString("N2")
End Sub

Protected Sub gvOrders_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
    Me.gvOrders.EditIndex = e.NewEditIndex
    Me.BindOrders()
End Sub

Protected Sub gvOrders_RowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
    Me.gvOrders.EditIndex = -1
    Me.BindOrders()
End Sub

Private Sub BindOrders()
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Using cmd As SqlCommand = New SqlCommand("SELECT TOP 3 OrderID,CustomerID,Freight FROM ORDERS", con)
            cmd.CommandType = CommandType.Text
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                sda.SelectCommand = cmd
                Dim dt As DataTable = New DataTable()
                sda.Fill(dt)
                Me.gvOrders.DataSource = dt
                Me.gvOrders.DataBind()
                Dim grandTotal As Decimal = dt.AsEnumerable().Sum(Function(row) row.Field(Of Decimal)("Freight"))
                gvOrders.FooterRow.Cells(1).Text = "Grand Total"
                gvOrders.FooterRow.Cells(2).Text = grandTotal.ToString("N2")
            End Using
        End Using
    End Using
End Sub

Screenshot