0
votes

I am filling my jQuery Datatable (ver: 1.10.15) from WebAPI and it works but when I search in datatable via searchbox then it doesn't fill the datatable with updated data. Why?

I checked, it sends search value and brings the updated data from server but doesn't populate the table with newly returned data.

function show()
{                               
  $('#example').DataTable({
//  "processing": true,
    "serverSide": true,
    "retrieve": true,
    "destroy": true,
    "pagination": true,
//  "contentType": 'application/json; charset=utf-8',
    "ajax": "http://localhost:28071/Users"
  });
}

UPDATE:

C# api code:

namespace WebApiHimHer.Controllers
{
    public class UsersController : ApiController
    {
        [HttpGet]
        public DTResult GetData([FromUri]DTParameters v)
        {

            List<string[]> s = new List<string[]>();

            //List<basicoperations> s = new List<basicoperations>();

            basicoperations bo= new basicoperations();


            s = bo.getUsers(v.length, v.start, 1, v.sortOrder, v.search.Value);

            DTResult r = new DTResult();
            r.draw = 1;
            r.recordsFiltered = s.Count;
            r.recordsTotal = 100; ;
            r.data = s;

            return r;
        }
    }

    public class DTResult
    {
        public int draw { get; set; }
        public int recordsTotal { get; set; }
        public int recordsFiltered { get; set; }
        public List<string[]> data { get; set; }
    }
    public abstract class DTRow
    {
        public virtual string DT_RowId
        {
            get { return null; }
        }
        public virtual string DT_RowClass
        {
            get { return null; }
        }
        public virtual object DT_RowData
        {
            get { return null; }
        }
    }
    public class DTParameters
    {
        public int draw { get; set; }
        public DTColumn[] columns { get; set; }
        public DTOrder[] order { get; set; }
        public int start { get; set; }
        public int length { get; set; }
        public DTSearch search { get; set; }
        public string sortOrder
        {
            get
            {
                return columns != null && order != null && order.Length > 0
                    ? (columns[order[0].Column].Data + (order[0].Dir == DTOrderDir.DESC ? " " + order[0].Dir : string.Empty))
                    : null;
            }
        }
    }
    public class DTColumn
    {
        public string Data { get; set; }
        public string Name { get; set; }
        public bool Searchable { get; set; }
        public bool Orderable { get; set; }
        public DTSearch Search { get; set; }
    }
    public class DTOrder
    {
        public int Column { get; set; }
        public DTOrderDir Dir { get; set; }
    }
    public enum DTOrderDir
    {
        ASC,
        DESC
    }
    public class DTSearch
    {
        public string Value { get; set; }
        public bool Regex { get; set; }


    }

}

I am posting a Request and response after initial table load and after performing a search

After initial load:

  • Request:

_ 1503560388132 columns[0][data] 0 columns[0][name] columns[0][orderable] true columns[0][search][regex] false columns[0][search][value] columns[0][searchable] true columns[1][data] 1 columns[1][name] columns[1][orderable] true columns[1][search][regex] false columns[1][search][value] columns[1][searchable] true draw 2 length 10 order[0][column] 0 order[0][dir] asc search[regex] false search[value] 2 start 0

Response:

{"draw":1,"recordsTotal":4,"recordsFiltered":25,"data":[["Hunain","123"],["hk","asd"],["daenerys Targaryen" ,"123"],["",""]]}

After performing a search:

  • Request:

_ 1503560409319 columns[0][data] 0 columns[0][name] columns[0][orderable] true columns[0][search][regex] false columns[0][search][value] columns[0][searchable] true columns[1][data] 1 columns[1][name] columns[1][orderable] true columns[1][search][regex] false columns[1][search][value] columns[1][searchable] true draw 2 length 10 order[0][column] 0 order[0][dir] asc search[regex] false search[value] w start 0

  • Response:

{"draw":1,"recordsTotal":1,"recordsFiltered":25,"data":[["Waleed","123"]]}

4
Impossible to say without seeing your serverside script. Somehow data is sent in a improper way.davidkonrad
@davidkonrad: sure, I am updating the questionuser8313534
@davidkonrad updated the question with server code, ajax request and responseuser8313534

4 Answers

1
votes

The reason was that draw parameter being sent and received was not same because I took draw static in the server code so mismatched. I returned the same draw parameter as it was sent.


From the docs :

The draw counter that this object is a response to - from the draw parameter sent as part of the data request. Note that it is strongly recommended for security reasons that you cast this parameter to an integer, rather than simply echoing back to the client what it sent in the draw parameter, in order to prevent Cross Site Scripting (XSS) attacks.

0
votes

Edit

    function show(){
        $("#example").DataTable({
            serverSide: true,
            processing: true,       
            columns : [
                { data : 'FirstName' },
                { data : 'LastName' }
            ],    
            ajax: {
                url: "https://api.myjson.com/bins/384sr",
                dataSrc : ''
           }
        });  
   }
   show();

Html

<table id="example" class="display">
<thead>
    <tr>
        <th>First Name</th>
        <th>Last Name</th>
    </tr>
</thead>

Jsfiddle

0
votes

Not sure if you found your error, but check this out.

Verify the configuration/system.webServer/security/requestFiltering/requestLimits@maxQueryString setting in the applicationhost.config or web.config file.

0
votes
//For me it was two things.

//wrap the table javascript with 

//function _displayItems(data) { var table = $('#...

//Then using the closing brackets:
//destroy then clear
    productsTable.destroy();
    productsTable.clear();
}

function _displayItems() {
  fetch(uri)
    .then(response => response.json())
    .then(data => _displayItems(data))
    .catch(error => console.error('Unable to get items.', error));
}

    var tableName = $('#data').DataTable({
        "processing": true,
        "data": data,
        "columns": [
            { "name": "name", "data": "name" },
            { "name": "id", "data": "id" },
            {
                "render": function (data, type, full, meta) {
                    var buttonID = "edit_" + full.id;
                    return '<a id="' + buttonID + '" class="btn btn-outline-info waves-effect editBtn" role="button">Edit</a>';
                }
            },
            {
                "render": function (data, type, full, meta) {
                    var buttonID = "delete_" + full.id;
                    return ' <a id="' + buttonID + '" onclick="deleteUserRoleModal()" type="button" class="btn btn-outline-danger waves-effect removeRole" role="button">Delete</a>';
                }
            }
        ],
        "responsive": true,
        "dom": 'Bfrtip',
        "buttons": [
            'copy', 'excel', 'pdf',
            {
                text: 'Delete',
            }
        ],
        "initComplete": function () {
            this.api().columns([0]).every(function () {
                var column = this;
                var select = $('<select class="selectpicker" data-size="5" data-live-search="true"><option value="">Application Role (All)</option></select>')
                    .appendTo("#table").end()
                    .on('change', function () {
                        var val = $.fn.dataTable.util.escapeRegex(
                            $(this).val()
                        );

                        column
                            .search(val ? '^' + val + '$' : '', true, false)
                            .draw();
                    });

                column.cells('', column[0]).render('display').sort().unique().each(function (d, j) {
                    if (column.search() === '^' + d + '$') {
                        select.append('<option value="' + d + '" selected="selected">' + d + '</option>')
                    }
                    else {
                        select.append('<option value="' + d + '">' + d + '</option>')
                    }
                });
            });
        },

        "bJqueryUI": true,
        "bPaginate": false,
        "aaSorting": [[1, 'asc']],
        "aoPreSearchCols": [[1]],

        "lengthMenu": [
            [12, 24, 48, 1000],
            ['12', '24', '48', 'all']
        ],
        "language": {
            searchPlaceholder: "🔎 Role Screen ",
            search: " ",
        }
    });

    $.fn.dataTable.Buttons(productsTable, {
        buttons: [
            'copy', 'excel', 'pdf'
        ]
    });
    productsTable.buttons().container()
        .appendTo($('.col-sm-6:eq(0)', productsTable.table().container()));
    $("#todos" + "#table").DataTable({
        responsive: true
    });

    //destroy then clear
    productsTable.destroy();
    productsTable.clear();
}