Display sum of Columns total in GridView Footer using Linq in ASP.Net

Last Reply one month ago By pandeyism

Posted one month ago

how to show the sum under the arrears footer?

tblFeesCollection 

AdmissionNo

Recievable

Arrears

NetBal

R-01

500

200

700

R-02

500

200

700

R-03

500

200

700

R-04

500

NULL

500

R-05

500

NULL

500

R-06

500

NULL

500

Total

3000

 

3600

 

This is showing from database and using following code I converted NULL to 0

for (int i = 0; i < sTable.Rows.Count; i++)
{
    for (int j = 0; j < sTable.Columns.Count; j++)
    {
        if (string.IsNullOrEmpty(sTable.Rows[i][j].ToString()))
        {
            sTable.Rows[i][j] = "0";
        }
    }
}

Now it is showing at front end like this

AdmissionNo

Recievable

Arrears

NetBal

R-01

500

200

700

R-02

500

200

700

R-03

500

200

700

R-04

500

0

500

R-05

500

0

500

R-06

500

0

500

Total

3000

 

3600

I want Total of Arrears in the footer like the Total of Receivable and NetBal which is 3000 and 3600 respectively. When I try to show the total of Arrears it shows me error. My Complete code is following: 

protected void btnRefSum_Click(object sender, EventArgs e)
    {
        con = new SqlDbConnect();
        con.SqlQuery(@"select AdmissionNo, Recievable,Arrears,NetBal from tblFees");
        adapt.SelectCommand = con.Cmd;
        adapt.Fill(sTable);
        if (sTable.Rows.Count > 0)
        {
            for (int i = 0; i < sTable.Rows.Count; i++)
            {
                for (int j = 0; j < sTable.Columns.Count; j++)
                {
                    if (string.IsNullOrEmpty(sTable.Rows[i][j].ToString()))
                    {
                       sTable.Rows[i][j] = "0";
                    }
                }
            }
         
            GridView1.DataSource = sTable;
            GridView1.DataBind();
            lblTotal.Text = GridView1.Rows.Count.ToString();

             decimal TotBal = sTable.AsEnumerable().Sum(row => row.Field<decimal>("Recievable"));
             GridView1.FooterRow.Cells[1].Text = TotBal.ToString();
             GridView1.FooterRow.Cells[1].HorizontalAlign = HorizontalAlign.Center;
             GridView1.FooterRow.Cells[1].Font.Bold = true;
             GridView1.FooterRow.Cells[1].BackColor = System.Drawing.Color.Honeydew;

             decimal Paid = sTable.AsEnumerable().Sum(row => row.Field<decimal>("Arrears"));
             GridView1.FooterRow.Cells[2].Text = Paid.ToString();
             GridView1.FooterRow.Cells[2].HorizontalAlign = HorizontalAlign.Center;
             GridView1.FooterRow.Cells[2].Font.Bold = true;
             GridView1.FooterRow.Cells[2].BackColor = System.Drawing.Color.Honeydew;

             decimal Bal = sTable.AsEnumerable().Sum(row => row.Field<decimal>("NetBal"));
             GridView1.FooterRow.Cells[3].Text = Bal.ToString();
             GridView1.FooterRow.Cells[3].HorizontalAlign = HorizontalAlign.Center;
             GridView1.FooterRow.Cells[3].Font.Bold = true;
             GridView1.FooterRow.Cells[3].BackColor = System.Drawing.Color.Honeydew; 
        }
        else
        {
            GridView1.Visible = false;
            Response.Write("No Record Found");
            lblTotal.Text = "0";
            return;
        }
        con.conClose();
    }

 

You are viewing reply posted by: pandeyism one month ago.
Posted one month ago

Hey smile,

Please refer below sample it's working.

HTML

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" ShowFooter="true">
    <Columns>
        <asp:BoundField DataField="AdmissionNo" HeaderText="Reg No" />
        <asp:TemplateField HeaderText="Recievable">
            <ItemTemplate>
                <asp:Label ID="lbl_Reci" runat="server" Text='<%# Bind("Recievable") %>'></asp:Label>
            </ItemTemplate>
            <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Arrears">
            <ItemTemplate>
                <asp:Label ID="lbl_Arr" runat="server" Text='<%# Bind("Arrears") %>'></asp:Label>
            </ItemTemplate>
            <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="NetBal">
            <ItemTemplate>
                <asp:Label ID="lbl_Total" runat="server" Text='<%# Bind("NetBal") %>'></asp:Label>
            </ItemTemplate>
            <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Data;

VB.Net

Imports System.Data

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[] { 
        new DataColumn("AdmissionNo", typeof(string)), 
        new DataColumn("Recievable", typeof(decimal)), 
        new DataColumn("Arrears", typeof(decimal)), 
        new DataColumn("NetBal", typeof(decimal)) });
        dt.Rows.Add("R-01", 500, 200, 700);
        dt.Rows.Add("R-02", 500, 200, 700);
        dt.Rows.Add("R-03", 500, 200, 700);
        dt.Rows.Add("R-04", 500, null, 500);
        dt.Rows.Add("R-05", 500, null, 500);
        dt.Rows.Add("R-06", 500, null, 500);
        if (dt.Rows.Count > 0)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (string.IsNullOrEmpty(dt.Rows[i][j].ToString()))
                    {
                        dt.Rows[i][j] = "0";
                    }
                }
            }
            this.GridView1.DataSource = dt;
            this.GridView1.DataBind();
            decimal TotBal = dt.AsEnumerable().Sum(row => row.Field<decimal>("Recievable"));
            decimal Paid = dt.AsEnumerable().Sum(row => row.Field<decimal>("Arrears"));
            decimal Bal = dt.AsEnumerable().Sum(row => row.Field<decimal>("NetBal"));
            GridView1.FooterRow.Cells[0].Text = "Total";
            GridView1.FooterRow.Cells[0].Font.Bold = true;
            GridView1.FooterRow.Cells[1].Text = TotBal.ToString();
            GridView1.FooterRow.Cells[1].HorizontalAlign = HorizontalAlign.Center;
            GridView1.FooterRow.Cells[1].Font.Bold = true;
            GridView1.FooterRow.Cells[1].BackColor = System.Drawing.Color.Honeydew;

            GridView1.FooterRow.Cells[2].Text = Paid.ToString();
            GridView1.FooterRow.Cells[2].HorizontalAlign = HorizontalAlign.Center;
            GridView1.FooterRow.Cells[2].Font.Bold = true;
            GridView1.FooterRow.Cells[2].BackColor = System.Drawing.Color.Honeydew;

            GridView1.FooterRow.Cells[3].Text = Bal.ToString();
            GridView1.FooterRow.Cells[3].HorizontalAlign = HorizontalAlign.Center;
            GridView1.FooterRow.Cells[3].Font.Bold = true;
            GridView1.FooterRow.Cells[3].BackColor = System.Drawing.Color.Honeydew;
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim dt As DataTable = New DataTable()
        dt.Columns.AddRange(New DataColumn() {New DataColumn("AdmissionNo", GetType(String)), New DataColumn("Recievable", GetType(Decimal)), New DataColumn("Arrears", GetType(Decimal)), New DataColumn("NetBal", GetType(Decimal))})
        dt.Rows.Add("R-01", 500, 200, 700)
        dt.Rows.Add("R-02", 500, 200, 700)
        dt.Rows.Add("R-03", 500, 200, 700)
        dt.Rows.Add("R-04", 500, Nothing, 500)
        dt.Rows.Add("R-05", 500, Nothing, 500)
        dt.Rows.Add("R-06", 500, Nothing, 500)

        If dt.Rows.Count > 0 Then

            For i As Integer = 0 To dt.Rows.Count - 1

                For j As Integer = 0 To dt.Columns.Count - 1

                    If String.IsNullOrEmpty(dt.Rows(i)(j).ToString()) Then
                        dt.Rows(i)(j) = "0"
                    End If
                Next
            Next

            Me.GridView1.DataSource = dt
            Me.GridView1.DataBind()
            Dim TotBal As Decimal = dt.AsEnumerable().Sum(Function(row) row.Field(Of Decimal)("Recievable"))
            Dim Paid As Decimal = dt.AsEnumerable().Sum(Function(row) row.Field(Of Decimal)("Arrears"))
            Dim Bal As Decimal = dt.AsEnumerable().Sum(Function(row) row.Field(Of Decimal)("NetBal"))
            GridView1.FooterRow.Cells(0).Text = "Total"
            GridView1.FooterRow.Cells(0).Font.Bold = True
            GridView1.FooterRow.Cells(1).Text = TotBal.ToString()
            GridView1.FooterRow.Cells(1).HorizontalAlign = HorizontalAlign.Center
            GridView1.FooterRow.Cells(1).Font.Bold = True
            GridView1.FooterRow.Cells(1).BackColor = System.Drawing.Color.Honeydew
            GridView1.FooterRow.Cells(2).Text = Paid.ToString()
            GridView1.FooterRow.Cells(2).HorizontalAlign = HorizontalAlign.Center
            GridView1.FooterRow.Cells(2).Font.Bold = True
            GridView1.FooterRow.Cells(2).BackColor = System.Drawing.Color.Honeydew
            GridView1.FooterRow.Cells(3).Text = Bal.ToString()
            GridView1.FooterRow.Cells(3).HorizontalAlign = HorizontalAlign.Center
            GridView1.FooterRow.Cells(3).Font.Bold = True
            GridView1.FooterRow.Cells(3).BackColor = System.Drawing.Color.Honeydew
        End If
    End If
End Sub

Screenshot