DataTables with different number of columns

Question

I am loading data using ajax and generating column names dynamically in my DataTable. My DataTable has different number of columns, depending on the selection by user.(There is a drop-down list).

For example, there are 2 options in drop-down list as Southern Province and Northern Province. Southern Province table has 4 columns and Northern Province table has 6 columns.

Scenario 1

First user select Southern Province which has 4 columns. Then it generates table without no errors, But after that if user select Northern Province which has 6 columns, table not generate and js console print error as below.

Uncaught TypeError: Cannot read property 'style' of undefined jquery.dataTables.js:3828

Scenario 2

First user select Northern Province which has 6 columns. Then it generates table without no errors, But after that if user select Southern Province which has 4 columns, table not generate and js console print error as below.

Uncaught TypeError: Cannot read property 'mData' of undefined jquery.dataTables.js:6122

But if both table has same number of columns, both tables generate without errors.

How can I solve this ?

Here is the JS Code

jQuery(document)
.ready(
function() {
    $('#province-list').change(
            function() {
                var prov = $(this).val();
                if (prov == "sp") {
                    make_SP();
                } else if (prov == "np") {
                    make_NP();
                }
            });
    function make_SP() {
    $("#dataTables-res_item")
    .dataTable(
    {
        "bDestroy" : true,
        "bProcessing" : false,
        "bServerSide" : true,
        "sAjaxSource" : "/province_list_view?p_name=sp",
        "aoColumns" : [
                {
                    "mData" : "result_date",
                    "sTitle" : "Result Date"
                },
                {
                    "mData" : "result_day",
                    "sTitle" : "Result Day"
                },
                {
                    "mData" : "draw_number",
                    "sTitle" : "Draw Number"
                },
                {
                    "mData" : "draw_time",
                    "sTitle" : "Draw Time"
                } ],
        "order" : [ [ 0, "desc" ] ]
        });
    };                  
    function make_NP() {
        $("#dataTables-res_item")
        .dataTable(
        {
            "bDestroy" : true,
            "bProcessing" : false,
            "bServerSide" : true,
            "sAjaxSource" : "/province_list_view?p_name=np",
            "aoColumns" : [
                    {
                        "mData" : "result_date",
                        "sTitle" : "Result Date"
                    },
                    {
                        "mData" : "result_day",
                        "sTitle" : "Result Day"
                    },
                    {
                        "mData" : "draw_number",
                        "sTitle" : "Draw Number"
                    },
                    {
                        "mData" : "draw_time",
                        "sTitle" : "Draw Time"
                    },
                    {
                        "mData" : "draw_place",
                        "sTitle" : "Draw Place"
                    },
                    {
                        "mData" : "draw_person",
                        "sTitle" : "Agent"
                    } ],
            "order" : [ [ 0, "desc" ] ]
        });
    };
});

Answer 1

I faced the same issue when my updated data had different number of columns than the previous data. The recipe is really simple! In the scenario when there is a change in number of columns, Destroy function works in conjunction with $("#datatable").empty();. So before reloading data your code would contain following lines:

if (dataTableObject) { // Check if DataTable has been previously created and therefore needs to be flushed

    dataTableObject.fnDestroy(); // destroy the dataTableObject
    // For new version use table.destroy();
    $('#' + DataTableDivID).empty(); // Empty the DOM element which contained DataTable
    // The line above is needed if number of columns change in the Data
    }
// DataTable data loading/reloading codes comes here

So overall, your code may look like this:

if(dataTableObject) { // Check if table object exists and needs to be flushed
    dataTableObject.fnDestroy(); // For new version use table.destroy();
    $('#myTable').empty(); // empty in case the columns change
}

var data = (province=='sp') ? sp : np;
var columns = (province=='sp') ? spColumns : npColumns;

dataTableObject = $('#myTable').DataTable({
        columns: columns,
        data:    data
    });

Answer 2

I think the safest way is to remove the table completely, and then re-insert it to the DOM before reinitialising. Seems to me that dataTables not completely removes all generated content, thats why the error(s) occurs (for different reasons). In theory it should work as above, more or less, but it doesn't. Consider this solution :

[full source in the demo link below]

var dataTable,
    domTable, 
    htmlTable = '<table id="example"><tbody></tbody></table>';

function initDataTable(province) {
    if ($.fn.DataTable.fnIsDataTable(domTable)) {
        dataTable.fnDestroy(true);
        $('body').append(htmlTable);
    } 
    var data = (province=='sp') ? sp : np;
    var columns = (province=='sp') ? spColumns : npColumns;    
    dataTable = $("#example").dataTable({
        aaData : data,
        aoColumns : columns
        /* other options here */
    });        
    domTable = document.getElementById('example');
}

$('#province-list').change(function() {
    var prov = $(this).val();
    initDataTable(prov);
});

This works. See demo -> http://jsfiddle.net/gss4a17t/ Basically it is the same as in OP, but instead of having different functions for different provinces, I have made different aoColumns for different provinces and so on. And instead of relying on bDestroy, I remove the entire <table> with dataTable.fnDestroy(true) (both DOM and and dataTables injections) and then reinserts the <table>-skeleton before reinitialising the dataTable.

I dont know if that is adaptable to OP's need, but this is how I would do it. It is more flexible for future changes, and the aoColumns-objects can be autogenerated from a script or achieved from the server by AJAX (if you want to have different titles for different languages, for example). "Belt and braces" :)