Is it possible the "Invalid Json Response" browser error is because of too many database records?

rdm

To provide context, I am working out a scenario with a very simple data model that happens to have 100,000+ records.

My browser gives me an Invalid Json error (see screenshot).

When I examined what was being returned in my DtResponse query (this is an MVC 5 project), I saw that I was indeed getting valid Json, but my suspicion is that maybe the Json file was too large. To translate part of the trace, "data Count=322920" means that there were that many lines being returned. I opened up one of the rows where I could see the correct fields and values.

The more I think of it, it might make sense that there is a malformed Json error if it gets cut off in the middle of the Json set. I read that DataTables/Editor can handle millions of rows, so I'm not sure what is happening. Is there something obvious I'm missing?

crwdzr

I saw this the other day which was relevant:
https://datatables.net/forums/discussion/44770/can-ajax-handle-5gb-json-file-which-contains-20-millions-of-records#latest

also relevant:
https://stackoverflow.com/questions/1262376/is-there-a-limit-on-how-much-json-can-hold

The take-away from the second link is that both the server and browser may be limiting the size its going to allow you to handle

bindrid

It was not a limit set or intrinsic to JSON. It was a default setting on IIS that threw an exception after a certain size. We uppped the limit and were good to go

allan

With that many rows, it would be worth enabling server-side processing. The Editor libraries you are using will automatically detect the server-side processing request and act on it.

Allan

rdm

@Allan -- I tried serverSide: true yesterday. That by itself did not solve the problem. Since I don't actually want to present the entire table -- I want the editor to page the results as it normally does -- I don't see the need to send the entire database table to client side.

So the question is why serverSide: true doesn't appear to work in my case.

rdm

@bindrid -- What technique did you use? I found several different approaches (using web.config and javascript.serialization settings in the controller action, and it didn't resolve the issue).

rdm

Just to make sure that I didn't have any errors on my view page, I artificially limited the size of the query results and I got no invalid Json errors. But I don't need to send the entire dataset over. I just need to send the requested page results. So maybe there's something in the response statement I need to add?

var response = new Editor(db, "PlanningSubset", "Id")
                    .Field(new Field("FridayPlanningRosterMembersId"))
                    .Field(new Field("QuarterNumber"))
                    .Field(new Field("WeekNumber"))
                    .Where("QuarterNumber",1)
                    .Where("WeekNumber", 1)
                    .Process(formData)
                    .Data();

Just in case there is a relevant clue in my jQuery code, I'm including that. You can see that I have even set paging as well as serverSide to true.

<script>
        var editor;
        $(() => {
            editor = new $.fn.dataTable.Editor({
                ajax: "@Url.Action("JoinedTableTest")",
                table: "#example",
                fields: [
                    { label: "FridayPlanningRosterMembersId", name: "FridayPlanningRosterMembersId" },
                    { label: "QuarterNumber", name: "QuarterNumber" },
                    { label: "WeekNumber", name: "WeekNumber" }
                ]
            });

            $('#example').DataTable({
                dom: "Bfrtip",
                ajax: {
                    url: "@Url.Action("JoinedTableTest")",
                    data: { campus: "@Model.Campus" }
                },
                serverSide: true,
                paging: true,
                pageLength: 5,
                columns: [
                    {
                        data: null,
                        defaultContent: '',
                        className: 'select-checkbox',
                        orderable: false
                    },
                    { data: "FridayPlanningRosterMembersId" },
                    { data: "QuarterNumber" },
                    { data: "WeekNumber" }
                ],
                select: {
                    style: 'os',
                    selector: 'td:first-child'
                },
                buttons: [
                    { extend: "create", editor: editor },
                    { extend: "edit", editor: editor },
                    { extend: "remove", editor: editor }
                ]
            });

            // Activate an inline edit on click of a table cell
            $('#example').on('click',
                'tbody td:not(:first-child)',
                function (e) {
                    editor.inline(this);
                });
        });
    </script>

rdm

So I forgot to add declare type: "POST". I added that

$('#example').DataTable({
                dom: "Bfrtip",
                ajax: {
                    url: "@Url.Action("JoinedTableTest")",
                    data: { campus: "@Model.Campus" },
                    type: "POST"
                },
                serverSide: true,
     [...]

Now I'm getting a new error: Unknown field: (index 0)

rdm

This might be the culprit. The advice on this page is to make sure the checkbox is not orderable. That is still the case, but still does not resolve the problem.

columns: [
                    {
                        data: null,
                        defaultContent: '',
                        className: 'select-checkbox',
                        orderable: false
                    },

rdm

Here's a mystery: when I move the checkbox column to the final column, I don't have any issues. That is a problem, however, because my users insist that the checkbox needs to be first because that's how they think.

So I'm back to square one: how do I get serverside to work with a selection checkbox in the first column?

allan

Add order: [[ 1, 'asc' ]] to your initialisation.

Even although you've marked the column as orderable: false, that actually for the end user's ability to order by that column only. So the default order of [[ 0, 'asc' ]] will cause issues.

You'll also want to add columns.searchable and set it to be false for that column.

Allan

rdm

@allan -- That did the trick. The matter is now resolved. Thank you