Subtract sum of two column values and display in GridView footer using C# and VB.Net in ASP.Net

smile
 
on Dec 02, 2021 02:34 AM
Sample_150678.zip
443 Views

I have a data like 

Company

Date

Debit

Credit

A

01-12-2021

0

500

B

01-12-2021

1500

0

C

02-12-2021

1000

0

 

Total

2500

500

 

 

Credit - Debit

-2000

Now I want to Subtract Credit Value from Debit Value in the footer row such as 500-2500 = -2000

how to get solution? here is my code

<asp:GridView ID="GridView1" runat="server"
    CssClass="table table-striped table-bordered table-hover" ShowFooter="true"
    BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="0px" CellPadding="6"
    AutoGenerateColumns="false" Font-Names="Arial" Font-Size="Small" GridLines="None"
    ForeColor="black" >
    <Columns>
        <asp:BoundField ItemStyle-Width="500px" DataField="A_Name" HeaderText="Company" />
        <asp:BoundField ItemStyle-Width="500px" DataField="Pay_Date" HeaderText="Date" />
        <asp:BoundField ItemStyle-Width="70px" DataField="Debit" HeaderText="Debit" />
        <asp:BoundField ItemStyle-Width="70px" DataField="Credit" HeaderText="Credit" />
         
    </Columns>
    <FooterStyle BackColor="Tan" />
    <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
    <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
    <HeaderStyle BackColor="Tan" Font-Bold="True" HorizontalAlign="Left" />
    <AlternatingRowStyle BackColor="PaleGoldenrod" />
    <RowStyle HorizontalAlign="Left" />
</asp:GridView>

 

protected void BindGrid()
{
    con = new SqlDbConnect();
    con.SqlQuery(@"select InvoiceNo,Remarks,Pay_Date,ag.A_Name,Debit,Credit from tblCashTransaction as j
                    inner join tblGroupAccount as ga on j.AccountID=ga.AccountID
                    inner join tblGroupAccount as ag on j.CustomerID = ag.AccountID
                    where j.AccountID=@AID and Pay_Date between @From and @To
                    union all
                    select InvoiceNo,Remarks,Pay_Date,ag.A_Name,Debit,Credit from tblPayTransaction as j
                    inner join tblGroupAccount as ga on j.AccountID=ga.AccountID
                    inner join tblGroupAccount as ag on j.CustomerID = ag.AccountID
                    where j.AccountID=@AID and Pay_Date between @From and @To");
    con.Cmd.Parameters.Add(new SqlParameter("@AId", ddlAccount.SelectedValue.ToString()));
    con.Cmd.Parameters.Add(new SqlParameter("@From", txtDate.Text));
    con.Cmd.Parameters.Add(new SqlParameter("@To", txtTDate.Text));
    adapt.SelectCommand = con.Cmd;
    adapt.Fill(sTable);
    if (sTable.Rows.Count > 0)
    {
        GridView1.Visible = true;
        GridView1.DataSource = sTable;
        GridView1.DataBind();
 
        GridView1.FooterRow.Cells[1].Text = "Grand Total:";
        GridView1.FooterRow.Cells[1].Font.Bold = true;
        GridView1.FooterRow.Cells[1].BackColor = System.Drawing.Color.LightSteelBlue;
        GridView1.FooterRow.Cells[1].HorizontalAlign = HorizontalAlign.Right;
 
 
        decimal totalDis = sTable.AsEnumerable().Sum(row => row.Field<decimal?>("Debit")).Value;
        GridView1.FooterRow.Cells[2].Text = totalDis.ToString();
        GridView1.FooterRow.Cells[2].HorizontalAlign = HorizontalAlign.Center;
        GridView1.FooterRow.Cells[2].Font.Bold = true;
        GridView1.FooterRow.Cells[2].BackColor = System.Drawing.Color.Yellow;
         
        decimal totalAr = sTable.AsEnumerable().Sum(row => row.Field<decimal?>("Credit")).Value;
        GridView1.FooterRow.Cells[3].Text = totalAr.ToString();
        GridView1.FooterRow.Cells[3].HorizontalAlign = HorizontalAlign.Center;
        GridView1.FooterRow.Cells[3].Font.Bold = true;
        GridView1.FooterRow.Cells[3].BackColor = System.Drawing.Color.Yellow;
    }
    else
    {
        GridView1.Visible = false;
        ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('No Record Found...');", true);
        return;
    }
    con.conClose();
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
arjunv
 
on Dec 02, 2021 06:46 AM

Hi Smile,

 Please refer below Sample.

HTML

<asp:GridView runat="server" ID="gvDetails" AutoGenerateColumns="false" ShowFooter="true"
    OnDataBound="gvDetails_DataBound">
    <Columns>
        <asp:BoundField DataField="Comapany" HeaderText="Comapany" />
        <asp:BoundField DataField="Date" HeaderText="Date" />
        <asp:BoundField DataField="Debit" HeaderText="Debit" />
        <asp:BoundField DataField="Credit" HeaderText="Credit" />
    </Columns>
</asp:GridView>

Namespace

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("Comapany", typeof(string)),
            new DataColumn("Date"),
            new DataColumn("Debit",typeof(decimal)),
            new DataColumn("Credit",typeof(decimal))
        });

        dt.Rows.Add("A", "01-12-2021", "0", "500");
        dt.Rows.Add("B", "01-12-2021", "1500", "0");
        dt.Rows.Add("C", "02-12-2021", "1000", "0");
        gvDetails.DataSource = dt;
        gvDetails.DataBind();

        gvDetails.FooterRow.Cells[1].Text = "Grand Total:";
        gvDetails.FooterRow.Cells[1].Font.Bold = true;
        gvDetails.FooterRow.Cells[1].BackColor = System.Drawing.Color.LightSteelBlue;
        gvDetails.FooterRow.Cells[1].HorizontalAlign = HorizontalAlign.Right;

        decimal totalDis = dt.AsEnumerable().Sum(row => row.Field<decimal?>("Debit")).Value;
        decimal totalAr = dt.AsEnumerable().Sum(row => row.Field<decimal?>("Credit")).Value;

        gvDetails.FooterRow.Cells[2].Text = totalDis.ToString();
        gvDetails.FooterRow.Cells[2].HorizontalAlign = HorizontalAlign.Center;
        gvDetails.FooterRow.Cells[2].Font.Bold = true;
        gvDetails.FooterRow.Cells[2].BackColor = System.Drawing.Color.Yellow;

        gvDetails.FooterRow.Cells[3].Text = totalAr.ToString();
        gvDetails.FooterRow.Cells[3].HorizontalAlign = HorizontalAlign.Center;
        gvDetails.FooterRow.Cells[3].Font.Bold = true;
        gvDetails.FooterRow.Cells[3].BackColor = System.Drawing.Color.Yellow;
    }
}

protected void gvDetails_DataBound(object sender, EventArgs e)
{
    GridView gvDetails = (GridView)sender;
    GridViewRow footer = gvDetails.FooterRow;
    var numCells = footer.Cells.Count;
    GridViewRow newRow = new GridViewRow(footer.RowIndex + 1, -1, footer.RowType, footer.RowState);
    for (int i = 0; i <= numCells - 1; i++)
    {
        TableCell emptyCell = new TableCell();
        emptyCell.ApplyStyle(gvDetails.Columns[i].ItemStyle);
        newRow.Cells.Add(emptyCell);
    }

    DataTable dt = gvDetails.DataSource as DataTable;
    decimal debit = dt.AsEnumerable().Sum(row => row.Field<decimal?>("Debit")).Value;
    decimal credit = dt.AsEnumerable().Sum(row => row.Field<decimal?>("Credit")).Value;
    newRow.Cells[2].Text = "Credit - Debit";
    newRow.Cells[3].Text = (credit - debit).ToString();
    ((Table)gvDetails.Controls[0]).Rows.Add(newRow);
}

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("Comapany", GetType(String)), 
            New DataColumn("Date"), 
            New DataColumn("Debit", GetType(Decimal)), 
            New DataColumn("Credit", GetType(Decimal))
        })
        dt.Rows.Add("A", "01-12-2021", "0", "500")
        dt.Rows.Add("B", "01-12-2021", "1500", "0")
        dt.Rows.Add("C", "02-12-2021", "1000", "0")
        gvDetails.DataSource = dt
        gvDetails.DataBind()
        gvDetails.FooterRow.Cells(1).Text = "Grand Total:"
        gvDetails.FooterRow.Cells(1).Font.Bold = True
        gvDetails.FooterRow.Cells(1).BackColor = System.Drawing.Color.LightSteelBlue
        gvDetails.FooterRow.Cells(1).HorizontalAlign = HorizontalAlign.Right
        Dim totalDis As Decimal = dt.AsEnumerable().Sum(Function(row) row.Field(Of Decimal?)("Debit")).Value
        Dim totalAr As Decimal = dt.AsEnumerable().Sum(Function(row) row.Field(Of Decimal?)("Credit")).Value
        gvDetails.FooterRow.Cells(2).Text = totalDis.ToString()
        gvDetails.FooterRow.Cells(2).HorizontalAlign = HorizontalAlign.Center
        gvDetails.FooterRow.Cells(2).Font.Bold = True
        gvDetails.FooterRow.Cells(2).BackColor = System.Drawing.Color.Yellow
        gvDetails.FooterRow.Cells(3).Text = totalAr.ToString()
        gvDetails.FooterRow.Cells(3).HorizontalAlign = HorizontalAlign.Center
        gvDetails.FooterRow.Cells(3).Font.Bold = True
        gvDetails.FooterRow.Cells(3).BackColor = System.Drawing.Color.Yellow
    End If
End Sub

Protected Sub gvDetails_DataBound(ByVal sender As Object, ByVal e As EventArgs)
    Dim gvDetails As GridView = CType(sender, GridView)
    Dim footer As GridViewRow = gvDetails.FooterRow
    Dim numCells = footer.Cells.Count
    Dim newRow As GridViewRow = New GridViewRow(footer.RowIndex + 1, -1, footer.RowType, footer.RowState)

    For i As Integer = 0 To numCells - 1
        Dim emptyCell As TableCell = New TableCell()
        emptyCell.ApplyStyle(gvDetails.Columns(i).ItemStyle)
        newRow.Cells.Add(emptyCell)
    Next

    Dim dt As DataTable = TryCast(gvDetails.DataSource, DataTable)
    Dim debit As Decimal = dt.AsEnumerable().Sum(Function(row) row.Field(Of Decimal?)("Debit")).Value
    Dim credit As Decimal = dt.AsEnumerable().Sum(Function(row) row.Field(Of Decimal?)("Credit")).Value
    newRow.Cells(2).Text = "Credit - Debit"
    newRow.Cells(3).Text = (credit - debit).ToString()
    CType(gvDetails.Controls(0), Table).Rows.Add(newRow)
End Sub

Screenshot