Datatables stuck at loading when using ajax and webmethod

Daniel Kvaternik

I'm trying to populate a datatables table with data from an SQL query from inside a C# webmethod. After days of researching and trial and error, I still can't seem to get it to work. Most of the examples I see are performing server-side rendering but my dataset is relatively small so client-side should be adequate. If I switch to using hard-coded 'data' variable, everything works.

I suspect the issue lies within the string type being returned from the webmethod. Despite verifying properly formatted JSON string being returned, the table doesn't render. Even if I hard-code a properly formatted JSON string in the webmethod (bypassing the query), I still get the same, renderless, result. I tried passing back an object but that didn't work either.

I'm stuck and at wits-end.

Code snipets at: http://live.datatables.net/lurujefi/1/edit?html,css,js. FYI, my c# code is in the CSS window.

kthorngren

My suggestion would be to use the browser's developer tools to see the actual response. Maybe you can paste a snippet of the JSON response.

The JSON may be valid but it also needs to be in a structure that Datatables supports. This page discusses the supported data structures:
https://datatables.net/manual/data/#Data-source-types

Kevin

Daniel Kvaternik

I stripped it down to TOP 5 rows. Here's what Chrome says is the JSON:

{"d":"{\"data\":[{\"DepartmentID\":\"01\",\"DepartmentName\":\"CITY COUNCIL\",\"deptDetails\":\"\"},{\"DepartmentID\":\"02\",\"DepartmentName\":\"CITY CLERK\",\"deptDetails\":\"\"},{\"DepartmentID\":\"03\",\"DepartmentName\":\"CITY MANAGER\",\"deptDetails\":\"\"},{\"DepartmentID\":\"04\",\"DepartmentName\":\"HUMAN RESOURCES\",\"deptDetails\":\"\"},{\"DepartmentID\":\"05\",\"DepartmentName\":\"CITY ATTORNEY\",\"deptDetails\":\"\"}]}"}

My alert box also confirms this format (minus the escapes).
{"data":[{"DepartmentID":"01","DepartmentName":"CITY COUNCIL","deptDetails":""},{"DepartmentID":"02","DepartmentName":"CITY CLERK","deptDetails":""},{"DepartmentID":"03","DepartmentName":"CITY MANAGER","deptDetails":""},{"DepartmentID":"04","DepartmentName":"HUMAN RESOURCES","deptDetails":""},{"DepartmentID":"05","DepartmentName":"CITY ATTORNEY","deptDetails":""}]}

This format looks like it conforms correctly.

kthorngren

Yes, the second JSON format looks good. Next step is to see your JS code. Are you using Datatables ajax option or an external ajax call? Please post the Datatables init code and if you are using a separate ajax call to fetch the data.

We need to make sure you are extracting the data data object properly for Datatables. This thread may help:
https://datatables.net/forums/discussion/comment/114554/#Comment_114554

Kevin

Daniel Kvaternik

It's posted but I goofed and deselected the window before i locked it and posted the URL. Just toggle the JS window on the url, it's there.

kthorngren

You will want to use the ajax.dataSrc option as mentioned in the last post of the link I posted:
https://datatables.net/forums/discussion/comment/114554/#Comment_114554

Kevin

Daniel Kvaternik

I added that function for the dataSrc but still no go. If I comment out the success/error handlers, I get "no data" in the table which lead me to change the columns key but still can't get it to work.

 $(document).ready(function () { 
            var table = $('#dataView').DataTable({
                ajax: {
                    url: "Default.aspx/QueryData",
                    type: "post",
                    dataType: "json",
                    dataSrc: function (json) {
                        return $.parseJSON(json.d);                        
                    },
                    contentType: "application/json; charset=utf-8",
                    /*success: function (response) {
                        alert (response.d);
                    },
                    error: function () {
                        alert("oops");
                    },*/
                },
                select: "single",
                columns: [
                    {
                        "className": 'details-control',
                        "orderable": false,
                        "data": null,
                        "defaultContent": '',
                        "render": function () {
                            return '<i class="fa fa-plus-square" aria-hidden="true"></i>';
                        },
                        width:"15px"
                    },
                    { data: "DepartmentID" },
                    { data: "DepartmentName" },                    
                ],
                order: [[1, 'asc']]
            }); 

jQuery, especially ajax, is new to this old dog so a lot of this is frustratingly trial and error to me and debugging is a PITA. I appreciate the help.

kthorngren

jQuery, especially ajax, is new to this old dog

Been there

The ajax docs state that the success function should not be overridden. Its good that you commented it out.

Lets start with some debugging in the dataSrc function:

dataSrc: function (json) {
    var data = JSON.parse(json.d);
    console.log(data);

    //if data is output as a json string then add these lines
    data = JSON.parse(data);
    console.log(data);

    return data;                       
},

According to the jQuery docs:

As of jQuery 3.0, $.parseJSON is deprecated. To parse JSON strings use the native JSON.parse method instead.

Please post the results.

Kevin

Daniel Kvaternik

var data is indeed an array:

{data: Array(5)}
data: Array(5)
0: {DepartmentID: "01", DepartmentName: "CITY COUNCIL", deptDetails: ""}
1: {DepartmentID: "02", DepartmentName: "CITY CLERK", deptDetails: ""}
2: {DepartmentID: "03", DepartmentName: "CITY MANAGER", deptDetails: ""}
3: {DepartmentID: "04", DepartmentName: "HUMAN RESOURCES", deptDetails: ""}
4: {DepartmentID: "05", DepartmentName: "CITY ATTORNEY", deptDetails: ""}
length: 5
proto: Array(0)
proto: Object

Code update still yields, "No data available in table".

Daniel Kvaternik

EUREKA!!!

A slight modification to your code above gives me a populated table. Huzzah!

dataSrc: function (json) {
    var data = JSON.parse(json.d);
    console.log(data);
 
    return data.data;

Now off to break something styling the table to bootstrap and connecting the datasrc to real data.

Many thanks Kevin.

--Dan

Daniel Kvaternik

OK, so the celebration began a little too early. When I up the query back to top 100, I get a JS error. Unexpected end of JSON but I think that's probably go to do with a special character in the DepartmentName; likely a pesky apostrophe.

The hunt continues but at least I'm sane enough to continue from here.

kthorngren

Great, glad you fixed the problem. Yes the JSON error would be from your server script. You can validate JSON strings here:
https://jsonlint.com/

Kevin

Daniel Kvaternik

Sadly, the JSON string from the webmethod validates. There's also no special characters (that I can see) in SQL for the column it barfs on. There is one occurrence of an apostrophe prior but its contained with double quotes in the JSON.

It looks like the issue occurs during the JOSN.parse(). Chrome clearly shows a truncated JSON string if I console.log(json.d) which explains the error but I don't know why it's happening without any special characters being seen in the mix. It's almost like it's stopping after processing so many characters because the last part of the line is:

...
,{"DepartmentID":"28","DepartmentName":"JUVENILE SERVICES","deptDetails":""},{"Depart

See how it just abruptly ends in the middle of a key. Now my olden days of C++ whispers that it didn't necessarily error there, just that the print buffer was only that full at the time of the error.

I would love to just delete that row and re-test but I don't have write access to the database.

I continue to pick at this festering boil...

[update] If I use a where clause to ignore the trouble column, it still bombs mid-way through printing the next key. If I where clause out the trouble column and the one with the apostrophe it works but quickly falls apart in 2 more rows. Thinking there's hidden special characters in these rows...

Daniel Kvaternik

Final Update.

SQL has a 2033 character limit FOR JSON which I was exceeding.

Solution at: https://stackoverflow.com/questions/46327765/sql-server-truncates-data-returned-as-json?noredirect=1&lq=1

kthorngren

Interesting, I haven't seen the option to get JSON directly in the SQL query. Glad you found the problem.

Kevin