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

Last Reply 6 months ago By pandeyism

Posted 6 months 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();
    }

 

Posted 6 months 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