locked
GridView Column Total using Bootstrap Datatbale Plugin in asp.net RRS feed

  • 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