jQuery DataTables - Cannot reinitialise DataTable

Question

I'm making a statements page in a spring 4.x application using jQuery DataTables to render the statements.

I have the following object returned by the server after parsing thru the Jackson message converter.

{
    "data":[
        {
            "desc":"SUBWAY        00501197 BRONX        NYUS",
            "amount":"-",
            "date":"5.72"
        },
        {
            "desc":"MIDTOWN COMICS         NEW YORK     NYUS",
            "amount":"-",
            "date":"73.32"
        },
        {
            "desc":"GOOGLE *PlayDog Soft   GOOGLE.COM/CHCAUS",
            "amount":"-",
            "date":"1.99"
        },
        {
            "desc":"BIMG PRIMARY CARE      NEW YORK     NYUS",
            "amount":"-",
            "date":"25.00"
        },
        {
            "desc":"GOOGLE *PlayDog Soft   GOOGLE.COM/CHCAUS",
            "amount":"-",
            "date":"1.99"
        },
        {
            "desc":"GOOGLE *PlayDog Soft   GOOGLE.COM/CHCAUS",
            "amount":"-",
            "date":"1.99"
        },
        {
            "desc":"Walgreens Speci 205 8THNEW YORK     NYUS",
            "amount":"-",
            "date":"10.00"
        },
        {
            "desc":"GOOGLE *SGN Games      GOOGLE.COM/CHCAUS",
            "amount":"-",
            "date":"9.99"
        },
        {
            "desc":"REDBOX *DVD RENTAL     866-733-2693 ILUS",
            "amount":"-",
            "date":"35.93"
        },
        {
            "desc":"SHUN FA RELAXING CENTE NEW YORK     NYUS",
            "amount":"-",
            "date":"20.00"
        },
        {
            "desc":"CHIPOTLE 0590          NEW YORK     NYUS",
            "amount":"-",
            "date":"9.00"
        },
        {
            "desc":"CHIPOTLE 0590          NEW YORK     NYUS",
            "amount":"-",
            "date":"9.00"
        },
        {
            "desc":"ALEX CLEANERS          BRONX        NYUS",
            "amount":"-",
            "date":"58.95"
        },
        {
            "desc":"SUBWAY        00501197 BRONX        NYUS",
            "amount":"-",
            "date":"5.17"
        },
        {
            "desc":"PAYPAL *LAROMANAAPA    4029357733   CAUS",
            "amount":"-",
            "date":"103.20"
        },
        {
            "desc":"GOOGLE *PlayDog Soft   GOOGLE.COM/CHCAUS",
            "amount":"-",
            "date":"1.99"
        },
        {
            "desc":"GOOGLE *PlayDog Soft   GOOGLE.COM/CHCAUS",
            "amount":"-",
            "date":"1.99"
        },
        {
            "desc":"GOOGLE *SGN Games      GOOGLE.COM/CHCAUS",
            "amount":"-",
            "date":"4.99"
        },
        {
            "desc":"GOOGLE *SGN Games      GOOGLE.COM/CHCAUS",
            "amount":"-",
            "date":"4.99"
        },
        {
            "desc":"LA ISLA RESTAURANT #2  NEW YORK     NYUS",
            "amount":"-",
            "date":"12.75"
        },
        {
            "desc":"GOOGLE *PlayDog Soft   GOOGLE.COM/CHCAUS",
            "amount":"-",
            "date":"1.99"
        },
        {
            "desc":"GOOGLE *PlayDog Soft   GOOGLE.COM/CHCAUS",
            "amount":"-",
            "date":"1.99"
        },
        {
            "desc":"REDBOX *DVD RENTAL     866-733-2693 ILUS",
            "amount":"-",
            "date":"7.62"
        },
        {
            "desc":"GOOGLE *PlayDog Soft   GOOGLE.COM/CHCAUS",
            "amount":"-",
            "date":"1.99"
        },
        {
            "desc":"GOOGLE *PlayDog Soft   GOOGLE.COM/CHCAUS",
            "amount":"-",
            "date":"1.99"
        },
        {
            "desc":"GOOGLE *PlayDog Soft   GOOGLE.COM/CHCAUS",
            "amount":"-",
            "date":"1.99"
        },
        {
            "desc":"GOOGLE *PlayDog Soft   GOOGLE.COM/CHCAUS",
            "amount":"-",
            "date":"1.99"
        },
        {
            "desc":"GOOGLE *PlayDog Soft   GOOGLE.COM/CHCAUS",
            "amount":"-",
            "date":"3.99"
        },
        {
            "desc":"GOOGLE *PlayDog Soft   GOOGLE.COM/CHCAUS",
            "amount":"-",
            "date":"1.99"
        },
        {
            "desc":"GOOGLE *PlayDog Soft   GOOGLE.COM/CHCAUS",
            "amount":"-",
            "date":"1.99"
        }
    ]
}

Now I'm trying to pass this to data table as follows. But I get an error.

$('#example').dataTable(data)

But it gives me an error shown below:

DataTables warning: table id=example - Cannot reinitialise DataTable. For more information about this error, please see https://datatables.net/manual/tech-notes/3.

Also the table rendered has data in place of column names and looks just wrong.

Below is my controller

@RequestMapping(value = "/getStmt", method = RequestMethod.GET)
    public @ResponseBody HashMap<String,ArrayList<UserStatement>> getStatement(@RequestParam(required = true) String name, @RequestParam(required = true) String month, HttpServletRequest request, Model model){
        System.out.println(name+", "+month+"");
        User user = userDao.findUserByName(name);
        ArrayList<UserStatement> s=new ArrayList<UserStatement>();
        if (user != null && user.getUser_token()!=null) {
            s = (ArrayList<UserStatement>) userDao.getUserSelectedStatement(user.getUser_token(), 07, 2015);

        }else{
            UserStatement us =new UserStatement();
            us.setAmount("A user token could not be found for the user ");
            s.add(us);
        }
        HashMap<String, ArrayList<UserStatement>> h=new HashMap<String, ArrayList<UserStatement>>();
        h.put("data", s);

        return h;
    }

Below is my Ajax function

$(document).ready(function(){
    var today = new Date();
    var month=today.getMonth();
    var name="test";

    $.ajax({
           type: "GET",
           url: "getStmt.ajax",
           data: "name="+name+"&month="+month,
           success: function(msg){
               $('#stdata').html( '<table cellpadding="0" cellspacing="0" border="0" class="display" id="example"></table>' );
             var data1 = JSON.stringify(msg);
                console.log(data1);
                console.log(data1.data);
             console.log( "Data Saved: " + msg );
             $('#example').dataTable( {
                 "aaData": data,
                 "aoColumns": [
                             {"sTitle": "Description"},
                             {"sTitle": "Amount" },
                             {"sTitle": "Date" }
                         ]
             } );
           },
           error: function(msg){
               var data = msg;
                 console.log( "There was an error: " + msg );
                 $('#stdata').dataTable( {
                     "ajax": data,
                     "columns": [
                                 { "title": "Description" },
                                 { "title": "Amount" },
                                 { "title": "Date" },
                                 { "title": "Balance" }
                             ]
                 } );
           }
         }); 
});

and html:

<div class="panel-body">

                        <div id="stdata">
                        </div>
</div>

the table should sit inside stdata div.

How can I get this to work? I also want it in such a way that if a user changes the date selected in a drop-down list, the table should be repopulated thru Ajax for that date.

Answer 1

SOLUTION 1

Use ajax.data option to add additional parameters that would be sent to the server.

That way you don't need to re-initialize the table which makes the code much simple.

For example:

$('#example').DataTable({
    ajax: {
        url: "/script.php",
        data: function(d){
            d.select1 = $('#example-select1').val();            
            d.select2 = $('#example-select2').val();            
        }
    }
});

$('#example-select1, #example-select2').on('change', function(){
    $('#example').DataTable().ajax.reload(); 
});

where example-select1, example-select2 are IDs of your drop-downs.

See this jsFiddle for code and demonstration.

SOLUTION 2

Use clear() along with rows.add() to clear the table and add new data for DataTables 1.10+. For DataTables 1.9, use fnClearTable and fnAddData respectively.

To add data to existing table (for DataTables 1.10+):

$('#example').DataTable().clear();
$('#example').DataTable().rows.add(msg.data);

where msg is a variable holding response from the server.

SOLUTION 3

Use bDestroy (for DataTables 1.9) or destroy (for DataTables 1.10+) options to destroy any existing table matching the selector and replace with the new options.

To reinitialize the table (for DataTables 1.10+):

$('#example').DataTable( {
   "destroy": true,
   "data": msg.data,
   "columns": [
      {"title": "Description"},
      {"title": "Amount" },
      {"title": "Date" }
   ]
});

where msg is a variable holding response from the server.