locked
Need Datatables working example RRS feed

  • Question

  • User-809753952 posted

    Can anyone help me with an example of using Datatables with json data? 

    I need search on all columns, and header freeze function. 

    This is my  code.

    Whenever I click on sort or I change the number of page showing,  the filters on each column is lost.

      <script type="text/javascript">
            $(function () {
                $.ajax({
                    type: "POST",
                    url: "Reflist.aspx/GetReflist",
                    data: '{}',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: OnSuccess,
                    failure: function (response) {
                        alert(response.d);
                       
                    },
                    error: function (request, status, error) {
                        alert(request.responseText);
                    }
                });
            });
    
             
            function OnSuccess(response) {
                var table = $("[id*=GridView1]").DataTable(
            {
                bLengthChange: true,
                lengthMenu: [[25, 50, 100, -1], [25, 50,  100, "All"]],
                bFilter: true,
                bSort: true,
                bPaginate: true,
                data: response.d,
                columns: [{ 'data': 'Task_No' },
                          { 'data': 'ShipName' },
                          { 'data': 'IMO_No' },
                          { 'data': 'Implementation_Year'}],  
                        "scrollY": "80%",  
                        fixedHeader : {
                        header : true,
                        footer : false ,
                        headerOffset: 0
    	            }
            });
    
            $('#GridView1 thead tr').clone(true).appendTo('#GridView1 thead');
            $('#GridView1 thead tr:eq(1) th').each(function (i) {
                var title = $(this).text();
                $(this).html('<input type="text" placeholder="Search ' + title + '" />');
                $(this).css("background-color", "#dedede");
    
                $('input', this).on('keyup change', function () {
                    if (table.column(i).search() !== this.value) {
                        table
                        .column(i)
                        .search(this.value)
                        .draw();
                    }
                });
    
                $('#Button1').click(function () {
                    table.columns().search(''); 
                    table.draw();
                });
    
            });
             
            };
        </script>

     

    Monday, September 7, 2020 2:51 PM

All replies

  • User-1330468790 posted

    Hi mnmhemaj,

     

    The problem is that you just add a row as search panel for the first time you render the datatable.

    When the data table is re-rendered, this search panel is disappeared since it is not include in datatable plugin.

     

    One feasible way is to draw this panel whenever the datatable is refreshed using the event "draw".

    More details, you could refer to below codes:

    <form id="form1" runat="server">
            <div>
                <asp:GridView ID="GridView1" runat="server"></asp:GridView>
                
            </div>
        </form>
    
        <script type="text/javascript">
            $(function () {
                $.ajax({
                    type: "POST",
                    url: "GridViewDataTable.aspx/GetReflist",
                    data: '{}',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: OnSuccess,
                    failure: function (response) {
                        alert(response.d);
    
                    },
                    error: function (request, status, error) {
                        alert(request.responseText);
                    }
                });
            });
    
    
            function OnSuccess(response) {
                var table = $("[id*=GridView1]").DataTable(
                    {
                        bLengthChange: true,
                        lengthMenu: [[25, 50, 100, -1], [25, 50, 100, "All"]],
                        bFilter: true,
                        bSort: true,
                        bPaginate: true,
                        data: response.d,
                        columns: [{ 'data': 'Task_No' },
                        { 'data': 'ShipName' },
                        { 'data': 'IMO_No' },
                        { 'data': 'Implementation_Year' }],
                        "scrollY": "80%",
                        fixedHeader: {
                            header: true,
                            footer: false,
                            headerOffset: 0
                        }
                    });
    
                AddSearchBox(table);
    
                table.on('draw', function () {
                    alert('Table redrawn');
                    AddSearchBox(table);
                });
                
    
            };
    
            function AddSearchBox(table) {
                $('#GridView1 thead tr').clone(true).appendTo('#GridView1 thead');
                $('#GridView1 thead tr:eq(1) th').each(function (i) {
                    var title = $(this).text();
                    $(this).html('<input type="text" placeholder="Search ' + title + '" />');
                    $(this).css("background-color", "#dedede");
    
                    $('input', this).on('keyup change', function () {
                        if (table.column(i).search() !== this.value) {
                            table
                                .column(i)
                                .search(this.value)
                                .draw();
                        }
                    });
    
    
                });
            }
        </script>

    Code behind: (Simulation the data)

     public partial class GridViewDataTable : System.Web.UI.Page
        {
            public static List<Ref> Reflist = new List<Ref>
            {
                new Ref{ Task_No = 1, ShipName="Name1",IMO_No="NO_1",Implementation_Year="2019" },
                new Ref{ Task_No = 2, ShipName="Name2",IMO_No="NO_2",Implementation_Year="2019" },
                new Ref{ Task_No = 3, ShipName="Name3",IMO_No="NO_3",Implementation_Year="2019" },
                new Ref{ Task_No = 4, ShipName="Name4",IMO_No="NO_4",Implementation_Year="2019" },
                new Ref{ Task_No = 5, ShipName="Name5",IMO_No="NO_5",Implementation_Year="2019" },
                new Ref{ Task_No = 6, ShipName="Name6",IMO_No="NO_6",Implementation_Year="2020" },
                new Ref{ Task_No = 7, ShipName="Name7",IMO_No="NO_7",Implementation_Year="2020" },
                new Ref{ Task_No = 8, ShipName="Name8",IMO_No="NO_8",Implementation_Year="2020" },
                new Ref{ Task_No = 9, ShipName="Name9",IMO_No="NO_9",Implementation_Year="2020" },
                new Ref{ Task_No = 10, ShipName="Name10",IMO_No="NO_10",Implementation_Year="2020" },
                new Ref{ Task_No = 6, ShipName="Name6",IMO_No="NO_6",Implementation_Year="2020" },
                new Ref{ Task_No = 7, ShipName="Name7",IMO_No="NO_7",Implementation_Year="2020" },
                new Ref{ Task_No = 8, ShipName="Name8",IMO_No="NO_8",Implementation_Year="2020" },
                new Ref{ Task_No = 9, ShipName="Name9",IMO_No="NO_9",Implementation_Year="2020" },
                new Ref{ Task_No = 10, ShipName="Name10",IMO_No="NO_10",Implementation_Year="2020" }
            };
    
            protected void Page_Load(object sender, EventArgs e)
            {
                GridView1.DataSource = new List<Ref> { new Ref() };
                GridView1.DataBind();
            }
    
            
            [WebMethod]
            public static List<Ref> GetReflist()
            {
                return Reflist;
            }
        }
    
        public class Ref
        {
            public int Task_No { get; set; }
            public string ShipName { set; get; }   
            public string IMO_No { set; get; }
            public string Implementation_Year { set; get; }
        }

    Demo:

     

    Hope this can help you.

    Best regards,

    Sean

    Tuesday, September 8, 2020 4:57 AM
  • User-809753952 posted

    Hi Sean

    Thank you for your reply. The table is redrawn on Page Size chage and Sorting.

    But Searching on various columns is resetting the search data and hence the filtering is not possible. 

    I need filtering only if the user, clicks on the enter key or tabulate from one text field to another.

    Tuesday, September 8, 2020 10:48 AM
  • User-1330468790 posted

    Hi mnmhemaj,

      

    Then you should modify your codes since I cannot directly use your original codes.

    The customization should be in line with 'initComplete' for the data table.

      

    More details, you could refer to below codes.

    Be aware of the change for bSort (from true to false). Otherwise, the sort will cause a weird behavior for the search boxes. 

     <script type="text/javascript">
            $(function () {
                $.ajax({
                    type: "POST",
                    url: "GridViewDataTable.aspx/GetReflist",
                    data: '{}',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: OnSuccess,
                    failure: function (response) {
                        alert(response.d);
    
                    },
                    error: function (request, status, error) {
                        alert(request.responseText);
                    }
                });
            });
    
    
            function OnSuccess(response) {
    
                var table = $("[id*=GridView1]").DataTable(
                    {
                        bLengthChange: true,
                        lengthMenu: [[25, 50, 100, -1], [25, 50, 100, "All"]],
                        bFilter: true,
                        bSort: false,
                        bPaginate: true,
                        data: response.d,
                        columns: [
                            {'title': 'Task No', 'data': 'Task_No' },
                            { 'title': 'Ship Name', 'data': 'ShipName' },
                            { 'title': 'IMO No',  'data': 'IMO_No' },
                            { 'title': 'Implementation Year',  'data': 'Implementation_Year' }],
                        "scrollY": "80%",
                        fixedHeader: {
                            header: true,
                            footer: false,
                            headerOffset: 0
                        },
                        initComplete: function () {
                            var api = this.api();
    
                            // Apply the search
                            api.columns().every(function () {
                                var that = this;
                                var title = $(this.header()).text();
                                $(this.header()).html('<input type="text" placeholder="Search ' + title + '" />');
                                console.log(that);
                                console.log(that.header());
                                $('input', this.header()).on('keyup change', function () {
                                    if (that.search() !== this.value) {
                                        that
                                            .search(this.value)
                                            .draw();
                                    }
                                });
                            });
                        }
                    });
            };
        </script>

    Demo:

     

      

    Hope this can help you.

    Best regards,

    Sean

    Wednesday, September 9, 2020 9:06 AM