Display ASP.Net GridView row total and Column Total in Footer using jQuery

smile

SOLVED CLOSED ACTIVE ANSWERED
User: smile
Posted: on Nov 28, 2018 11:04 PM
Forum: Data Controls
Answer: 1
Views: 9929
Sample Code: Download

my working output is

as u saw I have to enter 0 in Discount colomn to obtain the total in NetBal Colomn. Is there any way that when this data is shown then the Discount colomn inside gridview automatically contain 0 and NetBal should be calcalute automatically. Simply I want to get rid entering 0 value inside Discount and Paid colomn. and want autocalculation of Recievable + Arrears = NetBal example is

200 + 500 = 700

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript">
$(function () {
    $("[id*=txtPaid]").keyup(function () {
        var rowtotal = $(this).closest('tr').find('[id*=txtNet]').val();
        var paid = $(this).val();
        $(this).closest('tr').find('[id*=txtRem]').val(parseFloat(rowtotal) - parseFloat(paid));
    });
    $(".field1").keyup(function () {
        var total = 0;
        $(".field1").each(function () {
            total += parseFloat($(this).val());
        });
        $(".totaloffield td").eq(1).text(total);
        //calculate total for current row
        var val1 = $(this).val() == "" ? 0 : $(this).val();
        var val2 = $(this).parent().parent().find(".field2").val() == "" ? 0 : $(this).parent().parent().find(".field2").val();
        var val3 = $(this).parent().parent().find(".field3").val() == "" ? 0 : $(this).parent().parent().find(".field3").val();
        var rowtotal = parseFloat(!isNaN(val1) ? val1 : 0) + parseFloat(!isNaN(val2) ? val2 : 0) - parseFloat(!isNaN(val3) ? val3 : 0);
        $(this).parent().siblings().last().text(rowtotal);
        $(this).closest('tr').find('[id*=txtRem]').val(rowtotal);
    });
    $(".field2").keyup(function () {
        var total = 0;
        $(".field2").each(function () {
            total += parseFloat($(this).val());
        });
        $(".totaloffield td").eq(6).text(total);
        //calculate total for current row
        var val1 = $(this).parent().parent().find(".field1").val() == "" ? 0 : $(this).parent().parent().find(".field1").val();
        var val2 = $(this).val() == "" ? 0 : $(this).val();
        var val3 = $(this).parent().parent().find(".field3").val() == "" ? 0 : $(this).parent().parent().find(".field3").val();
        var rowtotal = parseFloat(!isNaN(val1) ? val1 : 0) + parseFloat(!isNaN(val2) ? val2 : 0) - parseFloat(!isNaN(val3) ? val3 : 0);
        $(this).parent().siblings().last().text(rowtotal);
        $(this).closest('tr').find('[id*=txtNet]').val(rowtotal);
    });
    $(".field3").keyup(function () {
        var total = 0;
        $(".field3").each(function () {
            total += parseFloat($(this).val());
        });
        $(".totaloffield td").eq(7).text(total);
        //calculate total for current row
        var val1 = $(this).parent().parent().find(".field1").val() == "" ? 0 : $(this).parent().parent().find(".field1").val();
        var val2 = $(this).parent().parent().find(".field2").val() == "" ? 0 : $(this).parent().parent().find(".field2").val();
        var val3 = $(this).val() == "" ? 0 : $(this).val();
        var rowtotal = parseFloat(!isNaN(val1) ? val1 : 0) + parseFloat(!isNaN(val2) ? val2 : 0) - parseFloat(!isNaN(val3) ? val3 : 0);
        $(this).parent().siblings().last().text(rowtotal);
        $(this).closest('tr').find('[id*=txtNet]').val(rowtotal);
    });
})
</script>

<asp:GridView ID="gvBudget" runat="server" AutoGenerateColumns="False" RowStyle-Wrap="false" HeaderStyle-Wrap="false"
     Width="100%" Class="table table-striped table-bordered table-hover"
    PagerStyle-CssClass="pgr" AlternatingRowStyle-CssClass="alt"
    ShowFooter="True" OnRowDataBound="gvBudget_RowDataBound">
    <Columns>
    <asp:TemplateField>
        <HeaderTemplate>
            <asp:CheckBox ID="checkAll" runat="server" onclick = "checkAll(this);" />
        </HeaderTemplate>
        <ItemTemplate>
            <asp:CheckBox ID="CheckBox1" runat="server" onclick = "Check_Click(this)" />
        </ItemTemplate>
     </asp:TemplateField>
        <asp:TemplateField HeaderText="Reg No.">
        <HeaderStyle HorizontalAlign="Left" Width="10%" />
        <ItemStyle HorizontalAlign="Left" VerticalAlign="Middle" Width="10%" />
        <ItemTemplate >
             <asp:Label ID="lbl_No" runat="server" Text='<%# Eval("AdmissionNo") %>' Width="50"></asp:Label>
        </ItemTemplate>               
    </asp:TemplateField> 
        <asp:BoundField DataField="SName" HeaderText="Student Name">
            <HeaderStyle HorizontalAlign="Left" Width="10%" />
            <ItemStyle HorizontalAlign="Left" VerticalAlign="Middle" Width="10%" />
        </asp:BoundField>
        <asp:BoundField DataField="FName" HeaderText="Father Name">
            <HeaderStyle HorizontalAlign="Left" Width="10%" />
            <ItemStyle HorizontalAlign="Left" VerticalAlign="Middle" Width="10%" />
        </asp:BoundField>
        <asp:BoundField DataField="AcademicName" HeaderText="Session">
            <HeaderStyle HorizontalAlign="Left" Width="10%" />
            <ItemStyle HorizontalAlign="Left" VerticalAlign="Middle" Width="10%" />
        </asp:BoundField>        
        <asp:TemplateField HeaderText="Recievable">
            <ItemTemplate>
                <asp:TextBox ID="txtReci" runat="server" Text='<%# Bind("Recievable") %>'
                    Width="100%" class="field1 form-control" autocomplete="off">
                </asp:TextBox>
            </ItemTemplate>
            <HeaderStyle HorizontalAlign="Left" Width="10%" />
            <ItemStyle HorizontalAlign="Left" Width="10%" />
        </asp:TemplateField>        
        <asp:TemplateField HeaderText="Arrears">
            <ItemTemplate>
                <asp:TextBox ID="txtArr" runat="server" Text='<%# Bind("RemBal") %>'
                    Width="100%" class="field2 form-control" autocomplete="off">
                </asp:TextBox>
            </ItemTemplate>
            <HeaderStyle HorizontalAlign="Left" Width="10%" />
            <ItemStyle HorizontalAlign="Left" Width="10%" />
        </asp:TemplateField>        
        <asp:TemplateField HeaderText="Discount">
            <ItemTemplate>
                <asp:TextBox ID="txtDis" runat="server"  
                    Width="100%" class="field3 form-control" autocomplete="off">
                </asp:TextBox>
            </ItemTemplate>
            <HeaderStyle HorizontalAlign="Left" Width="10%" />
            <ItemStyle HorizontalAlign="Left" Width="10%" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Net Balance">
            <ItemTemplate>
                <asp:TextBox ID="txtNet" runat="server" 
                    Width="100%" class="field4 form-control" autocomplete="off">
                </asp:TextBox>
            </ItemTemplate>
            <HeaderStyle HorizontalAlign="Left" Width="10%" />
            <ItemStyle HorizontalAlign="Left" Width="10%" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Paid">
            <ItemTemplate>
                <asp:TextBox ID="txtPaid" runat="server" 
                    Width="100%" class="field5 form-control" autocomplete="off">
                </asp:TextBox>
            </ItemTemplate>
            <HeaderStyle HorizontalAlign="Left" Width="10%" />
            <ItemStyle HorizontalAlign="Left" Width="10%" />
        </asp:TemplateField> 
        <asp:TemplateField HeaderText="Rem Bal">
            <ItemTemplate>
                <asp:TextBox ID="txtRem" runat="server" Text="0"
                    Width="100%" class="field6 form-control" autocomplete="off">
                </asp:TextBox>
            </ItemTemplate>
            <HeaderStyle HorizontalAlign="Left" Width="10%" />
            <ItemStyle HorizontalAlign="Left" Width="10%" />
        </asp:TemplateField>
        <asp:BoundField HeaderText="Total" />
    </Columns>
    <FooterStyle Font-Bold="True" CssClass="totaloffield" />
</asp:GridView>

    protected void BindGridClass()
    {
        con = new SqlDbConnect();
        con.SqlQuery("select * from (select g.AdmissionNo,SName,FName,AcademicName,sum(Head+AdmFees+TutionFees+LibFees+ExamFees+SportsFees+AnnualFees+ProspectusFees+UniformFees+BookFees+TokenFees+IDCardFees+BusCardFees+ElecFees+SchoolFund+FunFees+CSFees+GeneratorFees+SecFees+TourFees+Others) as [Recievable],c.VoucherNo,c.Arrears,c.CDiscount,NetBal,Paid,RemBal from tblFeesGenerate g left outer join tblFeesCollection c on  g.AdmissionNo=c.AdmissionNo  inner join tblStdReg as sr on g.AdmissionNo=sr.AdmissionNo inner join tblAcademicYear as dc on sr.YearID=dc.YearID where Year=@Year and Month=@Month and g.YearID=@YId and FeeID IN (SELECT MAX(FeeID) FROM tblFeesCollection GROUP BY AdmissionNo ) group by g.AdmissionNo,SName,VoucherNo,FName,c.Arrears,c.CDiscount,NetBal,Paid,RemBal,AcademicName ) tb");
        con.Cmd.Parameters.Add(new SqlParameter("@Year", ddlFYear.SelectedItem.ToString()));
        con.Cmd.Parameters.Add(new SqlParameter("@Month", ddlMonth.SelectedItem.ToString()));
        con.Cmd.Parameters.Add(new SqlParameter("@YId", ddlYear.SelectedValue.ToString())); 
        adapt.SelectCommand = con.Cmd;
        adapt.Fill(sTable);
        if (sTable.Rows.Count > 0)
        {
            gvBudget.Visible = true;
            gvBudget.DataSource = sTable;
            gvBudget.DataBind();
            lblTotal.Text = gvBudget.Rows.Count.ToString();

        }
        else
        {
            con.SqlQuery("select * from (select g.AdmissionNo,SName,FName,AcademicName,sum(Head+AdmFees+TutionFees+LibFees+ExamFees+SportsFees+AnnualFees+ProspectusFees+UniformFees+BookFees+TokenFees+IDCardFees+BusCardFees+ElecFees+SchoolFund+FunFees+CSFees+GeneratorFees+SecFees+TourFees+Others) as [Recievable],c.VoucherNo,c.Arrears,c.CDiscount,NetBal,Paid,RemBal from tblFeesGenerate g left outer join tblFeesCollection c on  g.AdmissionNo=c.AdmissionNo  inner join tblStdReg as sr on g.AdmissionNo=sr.AdmissionNo inner join tblAcademicYear as dc on sr.YearID=dc.YearID where Year=@Year and Month=@Month and g.YearID=@YId group by g.AdmissionNo,SName,VoucherNo,FName,c.Arrears,c.CDiscount,NetBal,Paid,RemBal,AcademicName ) tb ");
            con.Cmd.Parameters.Add(new SqlParameter("@Year", ddlFYear.SelectedItem.ToString()));
            con.Cmd.Parameters.Add(new SqlParameter("@Month", ddlMonth.SelectedItem.ToString()));
            con.Cmd.Parameters.Add(new SqlParameter("@YId", ddlYear.SelectedValue.ToString()));
            adapt.SelectCommand = con.Cmd;
            adapt.Fill(sTable);
            if (sTable.Rows.Count > 0)
            {
                gvBudget.Visible = true;
                gvBudget.DataSource = sTable;
                gvBudget.DataBind();
                lblTotal.Text = gvBudget.Rows.Count.ToString();

            }
        }
        con.conClose();
    }

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 2

Reply
ANSWER
User: dharmendr
Replied: on Nov 29, 2018 12:49 AM
Modified: on Nov 29, 2018 06:07 AM
Report

Hi smile,

Assign Text property to 0 for txtDis and txtPaid TextBox.

Then write the below JavaScript code in the document ready function to auto calculate the NetBal and the RemBal.

$('#gvBudget').find('tr:has(td)').each(function () {
    var recievable = $(this).find('[id*=txtReci]').val();
    var arrears = $(this).find('[id*=txtArr]').val();
    var discount = $(this).find('[id*=txtDis]').val();
    $(this).find('[id*=txtNet]').val(parseFloat(recievable) + parseFloat(arrears) - parseFloat(discount));
    var paid = $(this).find('[id*=txtPaid]').val();
    $(this).find('[id*=txtRem]').val(parseFloat($(this).find('[id*=txtNet]').val()) - parseFloat(paid));
});

Check the complete code.

HTML

<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
    <asp:GridView ID="gvBudget" runat="server" AutoGenerateColumns="False" RowStyle-Wrap="false"
        HeaderStyle-Wrap="false" Width="100%" Class="table table-striped table-bordered table-hover"
        PagerStyle-CssClass="pgr" AlternatingRowStyle-CssClass="alt" ShowFooter="True">
        <Columns>
            <asp:TemplateField>
                <HeaderTemplate>
                    <asp:CheckBox ID="checkAll" runat="server" onclick="checkAll(this);" />
                </HeaderTemplate>
                <ItemTemplate>
                    <asp:CheckBox ID="CheckBox1" runat="server" onclick="Check_Click(this)" />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Reg No.">
                <HeaderStyle HorizontalAlign="Left" Width="10%" />
                <ItemStyle HorizontalAlign="Left" VerticalAlign="Middle" Width="10%" />
                <ItemTemplate>
                    <asp:Label ID="lbl_No" runat="server" Text='<%# Eval("AdmissionNo") %>' Width="50"></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="SName" HeaderText="Student Name">
                <HeaderStyle HorizontalAlign="Left" Width="10%" />
                <ItemStyle HorizontalAlign="Left" VerticalAlign="Middle" Width="10%" />
            </asp:BoundField>
            <asp:BoundField DataField="FName" HeaderText="Father Name">
                <HeaderStyle HorizontalAlign="Left" Width="10%" />
                <ItemStyle HorizontalAlign="Left" VerticalAlign="Middle" Width="10%" />
            </asp:BoundField>
            <asp:BoundField DataField="AcademicName" HeaderText="Session">
                <HeaderStyle HorizontalAlign="Left" Width="10%" />
                <ItemStyle HorizontalAlign="Left" VerticalAlign="Middle" Width="10%" />
            </asp:BoundField>
            <asp:TemplateField HeaderText="Recievable">
                <ItemTemplate>
                    <asp:TextBox ID="txtReci" runat="server" Text='<%# Bind("Recievable") %>' Width="100%"
                        class="field1 form-control" autocomplete="off">
                    </asp:TextBox>
                </ItemTemplate>
                <FooterTemplate>
                    <asp:Label ID="lblRecievable" runat="server" />
                </FooterTemplate>
                <HeaderStyle HorizontalAlign="Left" Width="10%" />
                <ItemStyle HorizontalAlign="Left" Width="10%" />
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Arrears">
                <ItemTemplate>
                    <asp:TextBox ID="txtArr" runat="server" Text='<%# Bind("Arrears") %>' Width="100%"
                        class="field2 form-control" autocomplete="off">
                    </asp:TextBox>
                </ItemTemplate>
                <FooterTemplate>
                    <asp:Label ID="lblArrears" runat="server" />
                </FooterTemplate>
                <HeaderStyle HorizontalAlign="Left" Width="10%" />
                <ItemStyle HorizontalAlign="Left" Width="10%" />
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Discount">
                <ItemTemplate>
                    <asp:TextBox ID="txtDis" runat="server" Width="100%" class="field3 form-control"
                        Text="0" autocomplete="off">
                    </asp:TextBox>
                </ItemTemplate>
                <FooterTemplate>
                    <asp:Label ID="lblDiscount" runat="server" />
                </FooterTemplate>
                <HeaderStyle HorizontalAlign="Left" Width="10%" />
                <ItemStyle HorizontalAlign="Left" Width="10%" />
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Net Balance">
                <ItemTemplate>
                    <asp:TextBox ID="txtNet" runat="server" Width="100%" class="field4 form-control"
                        autocomplete="off">
                    </asp:TextBox>
                </ItemTemplate>
                <FooterTemplate>
                    <asp:Label ID="lblNet" runat="server" />
                </FooterTemplate>
                <HeaderStyle HorizontalAlign="Left" Width="10%" />
                <ItemStyle HorizontalAlign="Left" Width="10%" />
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Paid">
                <ItemTemplate>
                    <asp:TextBox ID="txtPaid" runat="server" Width="100%" class="field5 form-control"
                        Text="0" autocomplete="off">
                    </asp:TextBox>
                </ItemTemplate>
                <FooterTemplate>
                    <asp:Label ID="lblPaid" runat="server" />
                </FooterTemplate>
                <HeaderStyle HorizontalAlign="Left" Width="10%" />
                <ItemStyle HorizontalAlign="Left" Width="10%" />
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Rem Bal">
                <ItemTemplate>
                    <asp:TextBox ID="txtRem" runat="server" Text="0" Width="100%" class="field6 form-control"
                        autocomplete="off">
                    </asp:TextBox>
                </ItemTemplate>
                <FooterTemplate>
                    <asp:Label ID="lblRemain" runat="server" />
                </FooterTemplate>
                <HeaderStyle HorizontalAlign="Left" Width="10%" />
                <ItemStyle HorizontalAlign="Left" Width="10%" />
            </asp:TemplateField>
            <asp:BoundField HeaderText="Total" />
        </Columns>
        <FooterStyle Font-Bold="True" CssClass="totaloffield" />
    </asp:GridView>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script type="text/javascript" src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/js/bootstrap.min.js"></script>
    <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
    <script type="text/javascript">
        $(function () {
            CalculateTotal();
            $("[id*=txtPaid]").keyup(function () {
                CalculateTotal();
            });
            $(".field1").keyup(function () {
                CalculateTotal();
            });
            $(".field2").keyup(function () {
                CalculateTotal();
            });
            $(".field3").keyup(function () {
                CalculateTotal();
            });
        })
        function CalculateTotal() {
            $('[id$=gvBudget]').find('tr:has(td)').each(function () {
                var recievable = $(this).find('[id*=txtReci]').val();
                var arrears = $(this).find('[id*=txtArr]').val();
                var discount = $(this).find('[id*=txtDis]').val();
                $(this).find('[id*=txtNet]').val(parseFloat(recievable) + parseFloat(arrears) - parseFloat(discount));
                var paid = $(this).find('[id*=txtPaid]').val();
                $(this).find('[id*=txtRem]').val(parseFloat($(this).find('[id*=txtNet]').val()) - parseFloat(paid));
            });
            var td = $("[id$=gvBudget]").find('tr').find('td');
            var txtReci = td.find("[id*=txtReci]");
            var txtArr = td.find("[id*=txtArr]");
            var txtNet = td.find("[id*=txtNet]");
            var txtDis = td.find("[id*=txtDis]");
            var txtPaid = td.find("[id*=txtPaid]");
            var txtRem = td.find("[id*=txtRem]");

            var reci = 0, arr = 0, net = 0, discount = 0, paid = 0, remain = 0;

            for (var i = 0; i < txtReci.length; i++) {
                reci += parseFloat(txtReci.eq(i).val());
                arr += parseFloat(txtArr.eq(i).val());
                net += parseFloat(txtNet.eq(i).val());
                discount += parseFloat(txtDis.eq(i).val());
                paid += parseFloat(txtPaid.eq(i).val());
                remain += parseFloat(txtRem.eq(i).val());
            }
            $("[id$=gvBudget] [id*=lblRecievable]").html(reci);
            $("[id$=gvBudget] [id*=lblArrears]").html(arr);
            $("[id$=gvBudget] [id*=lblNet]").html(net);
            $("[id$=gvBudget] [id*=lblDiscount]").html(discount);
            $("[id$=gvBudget] [id*=lblPaid]").html(paid);
            $("[id$=gvBudget] [id*=lblRemain]").html(remain);
            // calculate total for each row.
            $.each($('[id$=gvBudget]').find('tr:has(td)'), function (i, item) {
                if (i < $('[id$=gvBudget]').find('tr:has(td)').length - 1) {
                    var val1 = $(item).find(".field1").val() == "" ? 0 : $(item).find(".field1").val();
                    var val2 = $(item).find(".field2").val() == "" ? 0 : $(item).find(".field2").val();
                    var val3 = $(item).find(".field3").val() == "" ? 0 : $(item).find(".field3").val();
                    var rowtotal = parseFloat(!isNaN(val1) ? val1 : 0) + parseFloat(!isNaN(val2) ? val2 : 0) - parseFloat(!isNaN(val3) ? val3 : 0);
                    $(item).find('td').last().html(rowtotal);
                }
            });
        }
    </script>
</asp:Content>

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

protected void BindGridClass()
{
    System.Data.DataTable dt = new System.Data.DataTable();
    dt.Columns.AddRange(new System.Data.DataColumn[10] { 
                    new System.Data.DataColumn("AdmissionNo", typeof(string)),
                    new System.Data.DataColumn("SName", typeof(string)),
                    new System.Data.DataColumn("FName",typeof(string)),
                    new System.Data.DataColumn("AcademicName", typeof(int)),
                    new System.Data.DataColumn("Recievable", typeof(int)),
                    new System.Data.DataColumn("Arrears", typeof(int)),
                    new System.Data.DataColumn("CDiscount", typeof(int)),
                    new System.Data.DataColumn("NetBal",typeof(int)),
                    new System.Data.DataColumn("Paid", typeof(int)),
                    new System.Data.DataColumn("RemBal", typeof(int))});
    dt.Rows.Add("R-000002", "KW", "MS", 2018, 700, 180, 0, 0, 0, 0);
    dt.Rows.Add("R-000003", "MF", "MS", 2018, 700, 130, 0, 0, 0, 0);
    dt.Rows.Add("R-000004", "RH", "MS", 2018, 700, 275, 0, 0, 0, 0);
    dt.Rows.Add("R-000005", "BS", "MS", 2018, 700, 235, 0, 0, 0, 0);
    gvBudget.DataSource = dt;
    gvBudget.DataBind();
}

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Display ASP.Net GridView row total and Column Total in Footer using jQuery

Company

Explore

Follow us

Contact Us