How to refresh a DataTable 1.10 table loaded client side

goodideadave

I have a DataTable defined and loaded on $(document).ready. It shows 24 rows per year. When I change a year in a select control on the page, I want to refresh the table with another call to the database. I have tried api().ajax.reload and it throws errors; it seems to be useful when loading from server side ajax. Can someone please show me a way to accomplish this?

My aspx page:

                <table id="tblPayrollSchedule" class="table display table-condensed">
                    <thead>
                        <tr>
                            <th>Pay Period</th>
                            <th>Period Start</th>
                            <th>Period End</th>
                            <th>Pay Day Start</th>
                            <th>Pay Day End</th>
                            <th>Update</th>
                        </tr>
                    </thead>
                    <tbody>
                
                    </tbody>
                </table>

In document ready:

    var jqxhr =
        $.ajax({
            type: "GET",
            url: "Handlers/PayrollScheduleDataTable_Handler.ashx",
            data: "companyID=" + companyID + "&payrollYear=" + payrollYear,
            cache: false,
            contentType: "application/json; charset=utf-8",
            dataType: "json"
        }).done(function (result) {
            $("#tblPayrollSchedule").DataTable({
                "processing": true,
                "autoWidth": false,
                "data": result,
                "dom": 'rt',    // only show the processing (r) & table (t) elements
                "pageLength": 100,
                "columns": [
                   //Assign the data to rows
                    { "data": "PayPeriod", "sortable": true, "searchable": true },
                    { "data": "PayPeriodStart", "sortable": false, "searchable": false },
                    { "data": "PayPeriodEnd", "sortable": false, "searchable": false },
                    { "data": "PayDayStart", "sortable": false, "searchable": false },
                    { "data": "PayDayEnd", "sortable": false, "searchable": false }
                ],
                "columnDefs": [
                    { "targets": 0, "width": "60px" },
                    { "targets": 1, "width": "60px" },
                    { "targets": 2, "width": "140px" },
                    { "targets": 3, "width": "140px" },
                    { "targets": 4, "width": "140px" },
                    {
                        "targets": 5, "width": "140px",
                        "render": function (data, type, row, meta) {   // the update button on each row
                            var btn = [], obj = {};
                            btn[0] = '<button type="button" class="btnUpdate" onclick="ShowUpdateModal(\'';
                            btn[btn.length] = row.PayPeriod + '\');">Update</a>';
                            return btn.join('');
                        }
                    }],
                "oLanguage": {  // the caption next to the search box
                    "sSearch": "Display a Year:"
                }
            });
        })

allan

ajax.reload() isn't going to work in this case since it isn't an Ajax sourced table (at least not in the DataTables sense through the ajax option).

You would need to use clear() and rows.add() to clear the table and then add new data which you get from your own Ajax call.

Allan

goodideadave

Thanks very much, allan.

For reference, here's how I implemented your suggestion:

    var companyID = $("select#ddlCompanyID option:selected").val(),
        payrollYear = $("select#ddlPayrollYear option:selected").val(),
        tbl = $("#tblPayrollSchedule").DataTable();

    var jqxhr =
        $.ajax({
            type: "GET",
            url: "Handlers/PayrollScheduleDataTable_Handler.ashx",
            data: "companyID=" + companyID + "&payrollYear=" + payrollYear,
            cache: false,
            contentType: "application/json; charset=utf-8",
            dataType: "json"
        }).done(function (result) {
            tbl.clear();
            $.each(result, function (idx, obj) {
                tbl.row.add(result[idx]);                
            });
            tbl.draw();
        }).fail(function (result) {
            alert("An error occurred whilst getting the row detail.");
        });

Yoann

Hey goodideadave,

I think you can replace

tbl.clear();
$.each(result, function (idx, obj) {
      tbl.row.add(result[idx]);               
});
tbl.draw();

with

tbl.clear();
tbl.rows.add(result);     // You might need to use eval(result)
tbl.draw();