Dispaly Column Sum in GridView Footer after adding new record using C# and VB.Net in ASP.Net

Last Reply 14 days ago By arunkurmi

Posted one month ago


how to dispaly sum value in editable gridview in footer field using asp c#.
while user fill value then sum value display in footer text box and save in table.

Exam:-
S. No. Particulars of Expenses Amount
1 1000 1000
2 500 2000
3 200 200
Total- 3200

 

Posted 14 days ago

Hi indradeo,

Check this sample. now take its reference.

HTML

<asp:GridView ID="gvTourPartic" runat="server" AutoGenerateColumns="false" AutogeneratSelectButton="true"
    OnRowCommand="gvTourPartic_RowCommand" OnRowDeleting="gvTourPartic_RowDeleting"
    OnRowUpdating="gvTourPartic_RowUpdating" ShowFooter="true" EmptyDataText="No Record are there">
    <Columns>
        <asp:TemplateField HeaderText="Sr No." Visible="true">
            <ItemTemplate>
                <%# Container.DataItemIndex +1 %>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Order ID" Visible="false">
            <ItemTemplate>
                <asp:Label ID="lblOrderID" runat="server" Text='<%# Eval("OrderID") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Particulars">
            <ItemTemplate>
                <asp:Label ID="lblItem" runat="server" Text='<%# Eval("Item") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:Label ID="txtEditItem" runat="server" Text='<%# Eval("Item") %>'></asp:Label>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtFooterParticular" runat="server"></asp:TextBox><br />
                <asp:Label ID="lblFooterText" Text="" runat="server" />
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Price">
            <ItemTemplate>
                <asp:Label ID="lblPrice" runat="server" Text='<%# Eval("Price") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:Label ID="txtEditPrice" runat="server" Text='<%# Eval("Price") %>'></asp:Label>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtFooterPrice" runat="server"></asp:TextBox><br />
                <asp:Label ID="lblFooterTotal" Text="" runat="server" />
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Action">
            <ItemTemplate>
                <asp:ImageButton ID="imgbtnDelete" runat="server" CommandName="Delete" AlternateText="Delete"
                    ImageUrl="~/Images/Delete.png" />
            </ItemTemplate>
            <FooterTemplate>
                <asp:LinkButton ID="lbtnAdd" runat="server" CommandName="ADD" Text="Add" Width="80px"></asp:LinkButton>
            </FooterTemplate>
        </asp:TemplateField>
    </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)
    {
        if (!this.IsPostBack)
        {
            this.BindData();
        }
    }
}

protected void BindData()
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT OrderID,Item,Price FROM tblOrders", con))
        {
            cmd.CommandType = CommandType.Text;
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                sda.SelectCommand = cmd;
                DataTable dt = new DataTable();
                sda.Fill(dt);
                this.gvTourPartic.DataSource = dt;
                this.gvTourPartic.DataBind();
                int grandTotal = dt.AsEnumerable().Sum(row => row.Field<int>("Price"));
                (gvTourPartic.FooterRow.FindControl("lblFooterText") as Label).Text = "Grand Total";
                (gvTourPartic.FooterRow.FindControl("lblFooterTotal") as Label).Text = grandTotal.ToString("N2");
            }
        }
    }
}

protected void gvTourPartic_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        Label lblId = (Label)gvTourPartic.Rows[e.RowIndex].FindControl("lblOrderID");
        con.Open();
        string cmdstr = "DELETE FROM tblOrders WHERE OrderID=@OrderID";
        SqlCommand cmd = new SqlCommand(cmdstr, con);
        cmd.Parameters.AddWithValue("@OrderID", lblId.Text);
        cmd.ExecuteNonQuery();
        con.Close();
        BindData();

    }
}

protected void gvTourPartic_RowCommand(object sender, GridViewCommandEventArgs e)
{
    if (e.CommandName.Equals("ADD"))
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
        {
            TextBox txtAddParticulars = (TextBox)gvTourPartic.FooterRow.FindControl("txtFooterParticular");
            TextBox txtAddAmt = (TextBox)gvTourPartic.FooterRow.FindControl("txtFooterPrice");

            con.Open();
            string cmdstr = "INSERT INTO tblOrders values(@Particular,@Price)";
            SqlCommand cmd = new SqlCommand(cmdstr, con);
            cmd.Parameters.AddWithValue("@Particular", txtAddParticulars.Text);
            cmd.Parameters.AddWithValue("@Price", txtAddAmt.Text);
            cmd.ExecuteNonQuery();
            con.Close();
            BindData();
        }
    }
}

protected void gvTourPartic_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        Label lblDep_Date = (Label)gvTourPartic.Rows[e.RowIndex].FindControl("lblDep_Date");
        con.Open();
        TextBox txtEditId = (TextBox)gvTourPartic.Rows[e.RowIndex].FindControl("txtEditdep_time");
        TextBox txtEditParticulars = (TextBox)gvTourPartic.Rows[e.RowIndex].FindControl("txtEditdep_station");
        TextBox txtEditAmt = (TextBox)gvTourPartic.Rows[e.RowIndex].FindControl("txtEditarr_date");
        TextBox lblTotal = (TextBox)gvTourPartic.Rows[e.RowIndex].FindControl("txtTotal");
        string cmdstr = "UPDATE tblOrders SET Particular=@Particular,Price=@Price WHERE OrderID=@OrderID";
        SqlCommand cmd = new SqlCommand(cmdstr, con);
        cmd.Parameters.AddWithValue("@OrderID", txtEditId.Text);
        cmd.Parameters.AddWithValue("@Particular", txtEditParticulars.Text);
        cmd.Parameters.AddWithValue("@Price", txtEditAmt.Text);
        cmd.ExecuteNonQuery();
        con.Close();
        gvTourPartic.EditIndex = -1;
        BindData();

    }
}

protected void gvTourPartic_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
    gvTourPartic.EditIndex = -1;
    BindData();
}

 VB.Net

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

Protected Sub BindData()
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Using cmd As SqlCommand = New SqlCommand("SELECT OrderID,Item,Price FROM tblOrders", con)
            cmd.CommandType = CommandType.Text
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                sda.SelectCommand = cmd
                Dim dt As DataTable = New DataTable()
                sda.Fill(dt)
                Me.gvTourPartic.DataSource = dt
                Me.gvTourPartic.DataBind()
                Dim grandTotal As Integer = dt.AsEnumerable().Sum(Function(row) row.Field(Of Integer)("Price"))
                TryCast(gvTourPartic.FooterRow.FindControl("lblFooterText"), Label).Text = "Grand Total"
                TryCast(gvTourPartic.FooterRow.FindControl("lblFooterTotal"), Label).Text = grandTotal.ToString("N2")
            End Using
        End Using
    End Using
End Sub

Protected Sub gvTourPartic_RowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs)
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Dim lblId As Label = CType(gvTourPartic.Rows(e.RowIndex).FindControl("lblOrderID"), Label)
        con.Open()
        Dim cmdstr As String = "DELETE FROM tblOrders WHERE OrderID=@OrderID"
        Dim cmd As SqlCommand = New SqlCommand(cmdstr, con)
        cmd.Parameters.AddWithValue("@OrderID", lblId.Text)
        cmd.ExecuteNonQuery()
        con.Close()
        BindData()
    End Using
End Sub

Protected Sub gvTourPartic_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs)
    If e.CommandName.Equals("ADD") Then
        Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
            Dim txtAddParticulars As TextBox = CType(gvTourPartic.FooterRow.FindControl("txtFooterParticular"), TextBox)
            Dim txtAddAmt As TextBox = CType(gvTourPartic.FooterRow.FindControl("txtFooterPrice"), TextBox)
            con.Open()
            Dim cmdstr As String = "INSERT INTO tblOrders values(@Particular,@Price)"
            Dim cmd As SqlCommand = New SqlCommand(cmdstr, con)
            cmd.Parameters.AddWithValue("@Particular", txtAddParticulars.Text)
            cmd.Parameters.AddWithValue("@Price", txtAddAmt.Text)
            cmd.ExecuteNonQuery()
            con.Close()
            BindData()
        End Using
    End If
End Sub

Protected Sub gvTourPartic_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Dim lblDep_Date As Label = CType(gvTourPartic.Rows(e.RowIndex).FindControl("lblDep_Date"), Label)
        con.Open()
        Dim txtEditId As TextBox = CType(gvTourPartic.Rows(e.RowIndex).FindControl("txtEditdep_time"), TextBox)
        Dim txtEditParticulars As TextBox = CType(gvTourPartic.Rows(e.RowIndex).FindControl("txtEditdep_station"), TextBox)
        Dim txtEditAmt As TextBox = CType(gvTourPartic.Rows(e.RowIndex).FindControl("txtEditarr_date"), TextBox)
        Dim lblTotal As TextBox = CType(gvTourPartic.Rows(e.RowIndex).FindControl("txtTotal"), TextBox)
        Dim cmdstr As String = "UPDATE tblOrders SET Particular=@Particular,Price=@Price WHERE OrderID=@OrderID"
        Dim cmd As SqlCommand = New SqlCommand(cmdstr, con)
        cmd.Parameters.AddWithValue("@OrderID", txtEditId.Text)
        cmd.Parameters.AddWithValue("@Particular", txtEditParticulars.Text)
        cmd.Parameters.AddWithValue("@Price", txtEditAmt.Text)
        cmd.ExecuteNonQuery()
        con.Close()
        gvTourPartic.EditIndex = -1
        BindData()
    End Using
End Sub

Protected Sub gvTourPartic_RowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
    gvTourPartic.EditIndex = -1
    BindData()
End Sub

Screenshot