Remove From My Forums

GridView Column Total using Bootstrap Datatbale Plugin in asp.net

Question

User1152553138 posted

How to added column total ... ? See the code ...

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <link type="text/css" rel="stylesheet" href="https://cdn.datatables.net/1.10.9/css/dataTables.bootstrap.min.css" />
    <link type="text/css" rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" />
    <link type="text/css" rel="stylesheet" href="https://cdn.datatables.net/responsive/1.0.7/css/responsive.bootstrap.min.css" />
    <script type="text/javascript" src="https://code.jquery.com/jquery-1.12.4.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/responsive/1.0.7/js/dataTables.responsive.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.9/js/dataTables.bootstrap.min.js"></script>
    <script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
    <script type="text/javascript">
        $(function () {
            $('[id*=gvCustomers]').prepend($("<thead></thead>").append($(this).find("tr:first"))).DataTable({
                "responsive": true,
                "sPaginationType": "full_numbers"
            });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" class="table table-striped"
            Width="100%">
            <Columns>
                <asp:BoundField DataField="CustomerID" HeaderText="Customer ID" />
                <asp:BoundField DataField="Qty" HeaderText="Qty" />
                <asp:BoundField DataField="Country" HeaderText="Country" />
            </Columns>
        </asp:GridView>
    </div>
    </form>
</body>
</html>

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT CustomerID,Qty,Country FROM Customers", con))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    cmd.CommandType = CommandType.Text;
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    gvCustomers.DataSource = dt;
                    gvCustomers.DataBind();
                }
            }
        }
    }
}

The above code is working fine ... I need to add column total

Saturday, March 31, 2018 9:46 AM

All replies

User283571144 posted

Hi Ashraf007,

According to your description, I suggest you could use footerCallback option in datatable.

More details, you could refer to below codes:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <link type="text/css" rel="stylesheet" href="https://cdn.datatables.net/1.10.9/css/dataTables.bootstrap.min.css" />
    <link type="text/css" rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" />
    <link type="text/css" rel="stylesheet" href="https://cdn.datatables.net/responsive/1.0.7/css/responsive.bootstrap.min.css" />
    <script type="text/javascript" src="https://code.jquery.com/jquery-1.12.4.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/responsive/1.0.7/js/dataTables.responsive.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.9/js/dataTables.bootstrap.min.js"></script>
    <script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
    <script type="text/javascript">

        $(document).ready(function () {
            $("#gvCustomers").prepend($("<thead></thead>").append($(this).find("tr:first")));
            $("#gvCustomers").prepend($("<tfoot></tfoot>").append($(this).find("tr:last")));

            $('#gvCustomers').DataTable({
                "footerCallback": function (row, data, start, end, display) {
                    var api = this.api(), data;
                    // Remove the formatting to get integer data for summation
                    var intVal = function (i) {
                        return typeof i === 'string' ?
                            i.replace(/[\$,]/g, '') * 1 :
                            typeof i === 'number' ?
                                i : 0;
                    };

                    api.columns('.sum', { page: 'current' }).every(function () {
                        var sum = this
                            .data()
                            .reduce(function (a, b) {
                                return intVal(a) + intVal(b);
                            }, 0);

                        this.footer().innerHTML = sum;
                    });
                }
            });
        });

     
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <div>
                <asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" class="table table-striped" ShowFooter="true"
                    Width="100%">
                    <Columns>
                        <asp:BoundField DataField="CustomerID" HeaderText="Customer ID" HeaderStyle-CssClass="sum"   FooterText="Country" />
                        <asp:BoundField DataField="Qty" HeaderText="Qty" />
                        <asp:BoundField DataField="Country" HeaderText="Country"  />
                    </Columns>
                </asp:GridView>
            </div>
        </div>
    </form>
</body>
</html>

Result:

Best Regards,

Brando

Tuesday, April 3, 2018 9:58 AM

User1152553138 posted

Hi Ashraf007,

According to your description, I suggest you could use footerCallback option in datatable.

More details, you could refer to below codes:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <link type="text/css" rel="stylesheet" href="https://cdn.datatables.net/1.10.9/css/dataTables.bootstrap.min.css" />
    <link type="text/css" rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" />
    <link type="text/css" rel="stylesheet" href="https://cdn.datatables.net/responsive/1.0.7/css/responsive.bootstrap.min.css" />
    <script type="text/javascript" src="https://code.jquery.com/jquery-1.12.4.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/responsive/1.0.7/js/dataTables.responsive.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.9/js/dataTables.bootstrap.min.js"></script>
    <script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
    <script type="text/javascript">

        $(document).ready(function () {
            $("#gvCustomers").prepend($("<thead></thead>").append($(this).find("tr:first")));
            $("#gvCustomers").prepend($("<tfoot></tfoot>").append($(this).find("tr:last")));

            $('#gvCustomers').DataTable({
                "footerCallback": function (row, data, start, end, display) {
                    var api = this.api(), data;
                    // Remove the formatting to get integer data for summation
                    var intVal = function (i) {
                        return typeof i === 'string' ?
                            i.replace(/[\$,]/g, '') * 1 :
                            typeof i === 'number' ?
                                i : 0;
                    };

                    api.columns('.sum', { page: 'current' }).every(function () {
                        var sum = this
                            .data()
                            .reduce(function (a, b) {
                                return intVal(a) + intVal(b);
                            }, 0);

                        this.footer().innerHTML = sum;
                    });
                }
            });
        });

     
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <div>
                <asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" class="table table-striped" ShowFooter="true"
                    Width="100%">
                    <Columns>
                        <asp:BoundField DataField="CustomerID" HeaderText="Customer ID" HeaderStyle-CssClass="sum"   FooterText="Country" />
                        <asp:BoundField DataField="Qty" HeaderText="Qty" />
                        <asp:BoundField DataField="Country" HeaderText="Country"  />
                    </Columns>
                </asp:GridView>
            </div>
        </div>
    </form>
</body>
</html>

Result:

Best Regards,

Brando

Thank the above code works only for BoundField

 <asp:BoundField DataField="CustomerID" HeaderText="Customer ID" HeaderStyle-CssClass="sum"   FooterText="Country" />

But i have item template field , i am getting total as NAN

 <asp:TemplateField HeaderText="CustomerID" HeaderStyle-CssClass="sum">
<ItemTemplate>
<asp:Label ID="LblCustomerID" runat="server" Text='<%# Bind("CustomerID") %>' ToolTip='<%# Bind("CustomerID") %>' ></asp:Label>
</ItemTemplate> 
</asp:TemplateField>

Saturday, September 1, 2018 7:00 AM

Asked by:

GridView Column Total using Bootstrap Datatbale Plugin in asp.net

Question

All replies