Reload data from HTML

cepeleon

Hi,
I have been looking for a straightforward way to do this and not had much luck going through the various posts.

Basically I have an empty table that I initialise with .DataTable() . A jQuery call is made to a server which will then return the contents of a table (so the thead to tbody tags and all children) back to the page in HTML.

I have seen that you can use arrays, use JSON etc etc but I have to work with plain old HTML. From what I can see in previous posts version 1.10 is supposed to have an invalidate method that will reload the rows from the DOM. However I have not been able to find it?

Can someone give me a pointer on this or at least tell me how I can accomplish the task above?

Thanks.

jr42.gordon

You mean rows().invalidate() ?

cepeleon

Hi jr42,

I cannot find that method, the only one that appears is

.rows()._fnInvalidateRow();

cepeleon

Ok it seems there is something wrong with my version of 1.10 so I have re-downloaded the script. The invalidate method does not show up in intellisense but I can at least find it now.

However the function does not appear to do anything.
This is what I am doing.

1. Creating and empty table
2. Making an ajax call that returns HTML for the table
3. In the callback method I am setting the table.html() jquery to returned data
4. I am then calling table.rows().invalidate();

Nothing happens. The table appears but the datatable functionality does not work. If I enter a static table on the page and assign datatable to this, it does.

allan

The row().invalidate() method will get the information from the DOM (or data source) about existing rows, as explained in its documentation. It will not add or delete new and old rows, which it sounds like you are using.

You need to use the API to add and remove rows once a DataTable has been initialised - see this FAQ.

Allan

cepeleon

I still cannot get this to work. I am returning the tr data as mentioned in the FAQ (though would love to see an example!) alerting it out so I can definately see that the data is valid but as soon as the the script hits the line to add the rows it crashes. Doesnt seem to matter where I put this line the script just stops working.

Has anyone ever got this working? If so please provide an example.

`
var table = $('#search-table');

table.DataTable({
    paging: false,
    searching: false,
    ordering: true,
    autoWidth: false,
    info: false,
    stateSave: false,
    responsive: false
});

function runSearch() {
var searchTxt = $('#SearchString').val();
var searchbox = $('#search-text');
var tablerows = $('#search-table-rows');

    /* Start search if search text is greater than 0 characters */
    if (searchTxt.length > 0) {
        disableScreen();

        $.post('/Search/OrderSearch', { 'searchString': searchTxt }, function (data) {
            alert(data);
            table.rows.add(data).draw(); <-- DOES NOT WORK SCRIPT STOPS RUNNING
            tablerows.html(data);
            table.show();
            searchbox.val(searchTxt);
            $(window).scrollTop('0');
        }).success(function () {
            enableScreen();
        }).error(function () {
            alert('Error');
            enableScreen();
        });
    } else {
        alert('Please enter a search string');
        enableScreen();
    }
}

`

cepeleon

<table id="search-table" class="results-table" style="display: none;"> <thead> <tr> <th>Order No</th> <th>Budget Year</th> <th>Run</th> <th>Description</th> <th>Client</th> </tr> </thead> <tbody id="search-table-rows"> </tbody> </table>

jr42.gordon

1. Variable 'table' is not a reference to your DataTable, but to the table DOM object
2. If you are getting HTML from your server, which I don't like nor recommend, then you need to apply it to the DOM first then call .DataTable
3. You can only do (2) once because after you convert your DOM table to DataTables, a bunch of things happen to the DOM.

My suggestion is to not return HTML but data and use the DataTables API, as mentioned by @allan, to add/remove rows to your table.

cepeleon

Hi Gordon,

Unfortuantely I cannot use anything other than the data coming from the server, there is no other option for me unless I write a parser to convert the data and then re-read it but this seems exceedingly overkill as opposed to using the HTML being returned.

I really need to see an example of this working because I am beginning to think the lack of any examples on the FAQ or in any other answers I've searched for where the data is using a TR tag element kind of makes me think the functionality is just not there.

The FAQ for rows.add() specifically states it can accept a TR tag element so I cannot understand why there is no example of this shown or why it doesnt seem to work.

Even if I ignore the data coming from my ajax request and set this value using a simple one line string of "<tr><td>this is my cell</td></tr>", once I add this to the DOM and then call the DataTable function it sounds like your implying you cannot then make subsequent calls to DataTable?

If thats the case how can this ever work? I thought the point of the rows.add() and other API calls was that once you had initialised the data table on document ready by doing this:

var table = $('#search-table');
table.DataTable({
paging: false,
searching: false,
ordering: true,
autoWidth: false,
info: false,
stateSave: false,
responsive: false
});

You could then manipulate the table later, even if there are no rows in the table when the DOM loads initially.

This is why I call DataTable in document ready, so that once I make my ajax call I can then simply update the DOM and then call the api line like so:

table.rows.add(myajaxdata).draw();

So that my datatable updates itself and I should then be able to rinse and repeat with every subsequent ajax call.

If thats not actually possible, or as I understand from your post you can only call DataTable once and only after a single DOM manipulation, then this really needs to be made clear in the FAQ as it seems pretty misleading to suggest datatables can work dynamically otherwise when working with HTML.

jr42.gordon

I was referring to this line of code

tablerows.html(data);

If tablerows references the DOM tbody of your table, and you are replacing it with the returned data, then as far as I know that is a no-no with DataTables. Given only snippets of code and not a working example, as per forum rules, makes it difficult to fully understand and provide assistance.

In the code you have provided, can you please make the following changes.

var table = $('#search-table');
var dt = table.DataTable({
    paging: false,
    searching: false,
    ordering: true,   // true by default, can be excluded
    autoWidth: false,   
    info: false,
    stateSave: false,    // false by default, can be excluded
    responsive: false   // undefined by default, can be excluded
});

function runSearch() {
   var searchTxt = $('#SearchString').val();
   var searchbox = $('#search-text');
   var tablerows = $('#search-table-rows');

       /* Start search if search text is greater than 0 characters */
    if (searchTxt.length > 0) {
        disableScreen();
 
        $.post('/Search/OrderSearch', { 'searchString': searchTxt }, function (data) {
            alert(data);
            dt.clear();   // If the new data is meant to completely replace.
            dt.rows.add(data).draw();
            tablerows.html(data);   // What is this for?
            table.show();
            searchbox.val(searchTxt);
            $(window).scrollTop('0');
        }).success(function () {
            enableScreen();
        }).error(function () {
            alert('Error');
            enableScreen();
        });
    } else {
        alert('Please enter a search string');
        enableScreen();
    }
}

cepeleon

Hi Gordon,

I did not post the remaining code, since I did not beleive it would provide anything extra to the puzzle but your quite right so here it is for clarity. I have also made the amendments as you suggest however I started to get the undefined paramter error for some reason. I then added the columndefs default.

My result now however is that I get a bunch of empty rows and no data at all.

This is the javascript:

$(document).ready(function () {
    /* Perform simple string search of client name */
    var mytable = $('#search-table');

    var dt = mytable.DataTable({
        paging: false,
        searching: false,
        autoWidth: false,
        info: false,
        "columnDefs": [{
            "defaultContent": "",
            "targets": "_all"
        }]
    });

    $('#searchform').submit(function (e) {
        e.preventDefault();
    });

    $('#create-neworder').on('click', function (e) {
        e.preventDefault();
        window.location = '/Order/NewOrder';
    });

    $('#search-button').on('click', function () {
        runSearch();
    });

    $(document).keypress(function (e) {
        if (e.which == 13) {
            runSearch();
        }
    });

    $('#search-results').on('click', '.clickable', function () {
        var oid = $(this).data('id');
        window.location = '/Order/Order?orderId=' + oid;
    });

    /* Set loading dialog */
    function disableScreen() {
        $('#dialog').attr('title', 'Please wait...').attr('id', 'dialog').html('<p>Loading... Please wait.</p>').dialog({
            open: function () { $(".ui-dialog-titlebar-close").hide(); },
            draggable: false,
            resizable: false,
            show: 'fade',
            modal: true
        });
    }

    /* Remove loading dialog */
    function enableScreen() {
        $('.ui-dialog:has(#' + $('#dialog').attr('id') + ')').empty().remove();
        $('#dialog').html('');
    }

    function runSearch() {
        var searchTxt = $('#SearchString').val();
        var searchbox = $('#search-text');

        /* Start search if search text is greater than 0 characters */
        if (searchTxt.length > 0) {
            disableScreen();

            $.post('/Search/OrderSearch', { 'searchString': searchTxt }, function (data) {
                dt.clear();
                dt.rows.add(data).draw();
                mytable.show();
                searchbox.val(searchTxt);
                $(window).scrollTop('0');
            }).success(function () {
                enableScreen();
            }).error(function () {
                alert('Error');
                enableScreen();
            });
        } else {
            alert('Please enter a search string');
            enableScreen();
        }
    }
});

This is the html master file

@using System.Web.Optimization
@model BudgetSystem.Models.Search.OrderSearchViewModel
@{
    ViewBag.Title = "Search for Client Orders";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

@section styles {
    @Styles.Render("~/Content/themes/base/css")
}

@section scripts { 
    @Scripts.Render("~/bundles/jqueryui")
    @Scripts.Render("~/bundles/datatables")
    @Scripts.Render("~/bundles/ordersearch")
}
<div id="content">
    <h3>@ViewBag.Title</h3>
    <p><a href="../Home/Index">Go Back to Menu</a></p>
    <div id="search-box">
        @using (Html.BeginForm("OrderSearch", "Search", FormMethod.Post, new { id = "searchform"}))
        {
            <p>@Html.ValidationMessageFor(model => model.SearchString)</p>
            <p>Search for orders on client id, number or name.</p>
            @Html.TextBoxFor(model => model.SearchString, null, new { style = "width: 80%;" })
            <img src="~/Content/images/search-icon.png" class="btn btn-info" alt="Search" id="search-button"/>
            <input name="search-text" id="search-text" type="hidden" value="" />
        }
    </div>
    <div id="search-results">
        <table id="search-table" class="results-table" style="display: none;">
            <thead>
                <tr>
                    <th>Order No</th>
                    <th>Budget Year</th>
                    <th>Run</th>
                    <th>Description</th>
                    <th>Client</th>
                </tr>
            </thead>
            <tbody id="search-table-rows"></tbody>
        </table>
    </div>
    <a id="create-neworder" href="">Create New Order</a>
</div>

And this is an example of the output from the server:

<tr data-id="1235" class="clickable"><td>1235</td><td>2016</td><td>3</td><td>This is a test</td><td>000 - my0009 - test ltd - te5 t12</td></tr>

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

however I started to get the undefined paramter error for some reason

Can you give the specific error message please.

Can you also link to the page showing the issue so we can debug it directly which will make it much easier to provide assistance.

Also what exactly is data in your rows.add() call? It should be an array (since it is plural) and contain either objects, arrays or table nodes.

Allan

cepeleon

Hi Allan,

The error message I received was:

DataTables warning: table id=search-table - Requested unknown parameter '1' for row 0. For more information about this error, please see http://datatables.net/tn/4

I fixed this with in the initialisation for the datatable:

,
        "columnDefs": [{
            "defaultContent": "",
            "targets": "_all"
        }]

The data object is the returned html as below

<tr data-id="1235" class="clickable"><td>1235</td><td>2016</td><td>3</td><td>This is a test</td><td>000 - my0009 - test ltd - te5 t12</td></tr>

p.s. thanks for the guide, I did wonder why the code option on the editor wasnt working!

allan

So data is a string? If so, that is what it isn't working. You could try:

dt.rows.add( $(data) ).draw();

Allan

cepeleon

Hi Allan,

Yes thats correct, the data is a string of HTML. I thought that was acceptable according to this https://datatables.net/reference/api/rows.add()

Each data element may be an array, object, Javascript object instance or a tr element.

I have made the change you recommend however this seems to just crash the script on the line that was updated.

dt.rows.add($(data)).draw(); 

If data should not be a string, how are you able to use a tr element as per the API doc?

allan

By tr element the documentation means a node, not a string. I should perhaps clarify that.

how are you able to use a tr element as per the API doc?

As I suggest above - use $(data) which will let jQuery render the string into HTML.

If that isn't working for you, I'd be happy to look at the page if you can give me a link to it.

Allan

cepeleon

Hi Allan,
Well that at least explains why I could never get it to work

Anyway I did try your suggestion but for some reason using $(data) just kept crashing the script so instead I did the following:

    function runSearch() {
        var searchTxt = $('#SearchString').val();
        var searchbox = $('#search-text');
        var html;

        /* Start search if search text is greater than 0 characters */
        if (searchTxt.length > 0) {
            disableScreen();

            $.post('/Search/OrderSearch', { 'searchString': searchTxt }, function (data) {
                alert(data.trim());
                html = $(data.trim());
                dt.clear();
                dt.rows.add(html).draw();
                mytable.show();
                searchbox.val(searchTxt);
                $(window).scrollTop('0');
            }).success(function () {
                enableScreen();
            }).error(function () {
                alert('Error');
                enableScreen();
            });
        } else {
            alert('Please enter a search string');
            enableScreen();
        }
    }

This works to a certain degree. I now get rows with data and I am also getting sort functionality. However I am also getting a number of blank rows at the top of the table equal (bar one) to the number of rows in the data (so 2 blank rows to every 3 of data, or 10 blanks for every 11 of data).

As for the page I am unable to provide a link because it is hosted on an intranet. However I will try to mock up a page and host it somewhere if that helps.

allan

Here is a little example showing it working: http://live.datatables.net/codewude/1/edit .

If you are able to modify that to show the issue I can take a look into it.

Allan

cepeleon

Hi Allan,

I have sussed it! It looks like the data coming back from the server requires more sanitization. The empty rows are being caused by tab/newline/space characters between the tr elements for each row. This then results in a blank row between each data row.

I have done the following to sanatize the data from the server and now everything is hunky dorey

    function runSearch() {
        var searchTxt = $('#SearchString').val();
        var searchbox = $('#search-text');
        var html;

        /* Start search if search text is greater than 0 characters */
        if (searchTxt.length > 0) {
            disableScreen();

            $.post('/Search/OrderSearch', { 'searchString': searchTxt }, function (data) {
                html = $(data.trim().replace(/\s+/g, " ").replace(/\<\/tr\> \<tr/g, '</tr><tr'));
                dt.clear();
                dt.rows.add(html).draw();
                mytable.show();
                searchbox.val(searchTxt);
                $(window).scrollTop('0');
            }).success(function () {
                enableScreen();
            }).error(function () {
                alert('Error');
                enableScreen();
            });
        } else {
            alert('Please enter a search string');
            enableScreen();
        }
    }

Many thanks to you and gordon for all your help on this! I hope it helps others who fell into the same misunderstanding with the FAQ at least.

allan

Thanks for posting back - good to hear you are up and running with it now.

Allan