jqgrid not showing any results when count exceeds ~9000

Question

I am using jqgrid version 4.1.2 with MVC4, using loadonce option. When the search result's count exceeds approximately 9000 records, no data is shown up on the grid.

What might be the issue?

Here is the JS code: Update 1

function showCompletedGrid() {
// Set up the jquery grid
$("#jqTableCompleted").jqGrid({
    // Ajax related configurations
    url: jqDataUrl,
    datatype: "json",
    mtype: "POST",
    loadonce: true,
    loadtext: 'Loading Data please wait ...',


    postData: { strUserName: function () { return $('#ddlUserName :selected').val(); },
        strFunctionName: function () { return $('#ddlOPMSFunction :selected').text(); },
        strProcName: function () { return $('#ddlOPMSProcess :selected').text(); },
        strCategory: function () { return $('#ddlSearchCategory :selected').text(); },
        strWorkType: function () { return $('#ddlSearchWorkType :selected').text(); },
        strRequestNumber: function () { return $('#txtRequestNo').val(); },
        strStatus: function () { return $('#ddlSearchStatus :selected').text(); },
        strFromDate: function () { return $('#txtFromDate').val().toString(); }, //datepicker('getDate'),
        strToDate: function () { return $('#txtToDate').val().toString(); }, //datepicker('getDate'),
        strAction: "Closed"
    },
    autowidth: true,
    shrinkToFit: true,
    // Specify the column names
    colNames: ["S.No.", "User Name", "Category", "Work Type", "Request Number", "Status", "Time Spent", "RE", "GUID", "Marked for Correction", "Correction Complete", "Reason", "Task Type", "acf2id", "Created Date", "Action", "IsTeam"],

    // Configure the columns
    colModel: [
                { name: "SNo", index: "SNo", sorttype: 'int', width: 100, align: "left", hidden: true, sortable: true, search: true, searchoptions: { sopt: ['eq']} },
                { name: "UserName", index: "UserName", width: 200, align: "left", sortable: true, search: true, sorttype: 'string', searchoptions: { sopt: ['cn']} },
                { name: "Category", index: "Category", width: 200, align: "left", sortable: true, search: true, sorttype: 'string', searchoptions: { sopt: ['cn']} },
                { name: "WorkType", index: "WorkType", width: 200, align: "left", sortable: true, search: true, sorttype: 'string', searchoptions: { sopt: ['cn']} },
                { name: "RequestNumber", index: "RequestNumber", width: 200, align: "left", sortable: true, search: true, searchoptions: { sopt: ['cn']} },
                { name: "Status", index: "Status", width: 200, align: "left", sortable: true, search: true, sorttype: 'string', searchoptions: { sopt: ['cn']} },
                { name: "TimeSpent", index: "TimeSpent", width: 200, align: "left", sortable: true, search: true },
                { name: "RE", index: "RE", width: 200, align: "left", sortable: true, search: true },
                { name: "GUID", index: "GUID", sortable: false, search: false, width: 200, align: "left", hidden: true },
                { name: "MarkCorrection", index: "MarkCorrection", width: 200, align: "left", hidden: true, sortable: true, search: true, sorttype: 'string', searchoptions: { sopt: ['cn']} },
                { name: "CorrectionComplete", index: "CorrectionComplete", width: 200, align: "left", hidden: true, sortable: true, search: true, sorttype: 'string', searchoptions: { sopt: ['cn']} },
                { name: "Reason", index: "Reason", width: 200, align: "left", hidden: true, sortable: true, search: true, sorttype: 'string', searchoptions: { sopt: ['cn']} },
                { name: "TaskType", index: "TaskType", width: 200, align: "left", sortable: true, search: true, sorttype: 'string', searchoptions: { sopt: ['cn']} },
                { name: "acf2id", index: "acf2id", width: 200, align: "left", hidden: true, sortable: true, search: true, sorttype: 'string', searchoptions: { sopt: ['cn']} },
                { name: "CreatedDate", index: "CreatedDate", width: 200, align: "left", hidden: false, search: false },
                { name: 'Actions', sortable: false, search: false, fixed: true, align: 'center', formatter: returnHyperLinkCompleted },
                { name: 'IsTeam', sortable: false, hidden: true, search: false, fixed: true, align: 'center' }
              ],
    ignoreCase: true,
    //width: 1250,
    height: 150,

    // Paging
    toppager: true,
    pager: $("#jqTableCompletedPager"),
    //rowTotal: 200,
    rowNum: 20,
    rowList: [20, 15, 10, 5],
    viewrecords: true,
    emptyrecords: "",
    hiddengrid: true,

    // Default sorting
    sortname: "SNo",
    sortorder: "asc",

    // Grid caption
    caption: "Closed",
    loadComplete: function (data) {

        var RE;
        var TimeSpent;
        var rowIDs = jQuery("#jqTableCompleted").jqGrid('getDataIDs');
        for (var i = 0; i < rowIDs.length; i++) {
            var rowID = rowIDs[i];
            var row = jQuery('#jqTableCompleted').jqGrid('getRowData', rowID);

            RE = hmsToSecondsOnly(row.RE);
            RE = (0.2 * RE) + RE;
            TimeSpent = hmsToSecondsOnly(row.TimeSpent);
            if (TimeSpent > RE && RE > 0) {
                $(row).removeClass('ui-widget-content');
                $(row).removeClass('ui-state-highlight');
                $("#jqTableCompleted tr[id='" + rowID + "']").addClass('myColor');

            }
        }
    }
}).navGrid("#jqTableCompletedPager",
        { refresh: true, add: false, edit: false, del: false },
            {}, // settings for edit
            {}, // settings for add
            {}, // settings for delete
            {sopt: ["cn"]}
     );

$("#jqTableCompleted").jqGrid('navGrid', '#jqTableCompletedPager', { del: false, add: false, edit: false, search: false });
$("#jqTableCompleted").jqGrid('filterToolbar', { searchOnEnter: false, searchOperators: true });

$("#jqTableCompleted").jqGrid('navButtonAdd', '#jqTableCompletedPager',
{ caption: "Export to Excel", buttonicon: "ui-icon-extlink", title: "Export", id: "btnExport",
    onClickButton: function (evt) {
        var UserName = $('#ddlUserName option:selected').val();
        var RequestNumber = $('#txtRequestNo').val();
        var FunctionName = encodeURIComponent($('#ddlOPMSFunction option:selected').text());
        var ProcessName = encodeURIComponent($('#ddlOPMSProcess option:selected').text());
        var Category = $('#ddlSearchCategory option:selected').text();
        var WorkTypeName = $('#ddlSearchWorkType option:selected').text();
        var SearchStatus = $('#ddlSearchStatus option:selected').text();
        var TransactionStartTS = $('#txtFromDate').val().toString();
        var TransactionEndTS = $('#txtToDate').val().toString();

        window.open("../Search/Export?UserName=" + UserName + "&RequestNumber=" + RequestNumber + "&FunctionName=" + FunctionName + "&ProcessName=" + ProcessName + "&Category=" + Category + "&WorkTypeName=" + WorkTypeName + "&SearchStatus=" + SearchStatus + "&TransactionStartTS=" + TransactionStartTS + "&TransactionEndTS=" + TransactionEndTS + "&ActionName=" + "Closed");
    }
});

}

Although the data is being returned and in the correct format, from the controller, as follows, the grid does not show any result.

var jsonData = new
            {
                page = page,
                rows = data
            };

            return Json(jsonData, JsonRequestBehavior.AllowGet);

Also is there any limit on number of records or dependency on the browser when returning all the data from server using the loadonce option?

Any help would be much appreciated. Thanks.

I suppose that you have problem with *local* searching of data. Isn't so? The problem could be in `colModle` which you use or in any callbacks which you implemented. You don't posted any code which could contain the origin of the problem. — Oleg, Mar 11 '15 at 09:51
No, local search and reloads are working fine. But once the amount of data exceeds the mentioned number, the grid does not populate any data, and the last searched result (if less than ~9k records, say 8k records) remains in the grid. If searched for lesser number of records by changing the parameter, and reloading again, everything works fine. — Jasmeet Singh, Mar 12 '15 at 04:49
I have tried reducing the number of columns in the grid to 1, to check if it is any data amount related issue, but it did not helped. Also I tried removing the loadComplete, as it has performance impact, but still the result was same. The json data being returned from controller shows correct number of records and in the desired format. — Jasmeet Singh, Mar 12 '15 at 04:52
@Oleg: I have posted the complete jqgrid code for your reference. Hope this would help. Thanks. — Jasmeet Singh, Mar 12 '15 at 04:54
As the first I would strictly recommend you to use **local paging**: replace `rowNum: 100000` to some smaller value like `rowNum: 25`. It has no sense to spend the time in filling thousands of rows which are invisible. — Oleg, Mar 12 '15 at 06:24
I forgot to correct that, it was initially 20, I later modified it to check how many results show up in single page. I have corrected it now, also uncommented the rowList attribute. Now the grid is in its original state. — Jasmeet Singh, Mar 12 '15 at 09:18
You should definitively remove the current **loadComplete** implementation and replace it to `rowattr`. See [the answer](http://stackoverflow.com/a/10531680/315935). You should add `gridview: true` option. It will improve the performance of the grid especially in case of usage large `rowNum` value. You code calls `navGrid` twice. The second call will be ignored. I recommend you to use `height: "auto"` and `autoencode: true` options additionally. — Oleg, Mar 12 '15 at 09:46
To your main problem: you write "the search result's count exceeds approximately 9000 records". What you means exactly? You use mixed sorting. You use `postData` to load filtered data first. Then you have local filter to filter the data additionally. So what you mean with 9000 records? Is it the *total number of rows loaded from the server* or it's the *total number of rows after the local filtering*? Can you validate that more as 9000 rows can be loaded from the server? So it's important to know where the problem is *on the server side or not*. — Oleg, Mar 12 '15 at 09:48
As I said earlier, I tried removing 'loadComplete' as it has a performance impact. Also I read your post regarding 'rowattr' before using loadComplete in my grid. Since it says 'rowattr' has been implemented in 4.3.2, and the version which I am using is 4.1.2, it will not work I guess. I will try the remaining options mentioned by you and get back. Thanks. — Jasmeet Singh, Mar 12 '15 at 09:57
Also about the main problem of count: Basically I have a set of fields which are not part of the grid, using which I am posting the data to controller to show the result. Now I am having a date range field as well in those search parameters. Considering approx. 1500-1600 records are there for each day, when I supply a date range greater than 6, the results are returned from the server, however these are not populated in the grid. It works fine otherwise. — Jasmeet Singh, Mar 12 '15 at 10:03
Sorry, but it was a lot of changes in jqGrid last years. jqGrid 4.1.2 is **4 years old**. Why you use the version? I'm abrade that the question about specific problems in the 4.1.2 have no interest for other readers. Do you tried to upgrade jqGrid to 4.7 or 4.8 version? Are the problems still exist? — Oleg, Mar 12 '15 at 10:11
Tried all the following options: `gridview:true`, removed duplicate call to navGrid, height: "auto" and `autoencode:true`. But still no help. As far as version is concerned, I have not tried upgrading to any version yet. Will check for sure. Thanks for your help Oleg. — Jasmeet Singh, Mar 12 '15 at 10:39
You should understand that 4 years in web development is almost 100 years in total history of people. 4 years Internet Explorer 9 was just published. The most uses used IE6-IE8. jQuery 1.4.x was still used. If you want that your web site will work with more modern web browsers you should use more recent products. I recommend you to try [free jqGrid 4.8](https://github.com/free-jqgrid/jqGrid) which I published recently See [wiki](https://github.com/free-jqgrid/jqGrid/wiki) for details. I recommend to try [fonts](https://github.com/free-jqgrid/jqGrid/wiki/Using-Font-Awesome-in-free-jqGrid-4.8) — Oleg, Mar 12 '15 at 10:48
That's true but due to some constraints earlier we were unable to update the system. However, now I can try out your suggestion. Thanks for sharing all the references, would try integrating the 4.8 version. — Jasmeet Singh, Mar 13 '15 at 03:57
@Oleg I found the solution to my problem, however I am still wondering why this error was not raised in the controller itself while returning the value. Could you please share your inputs on that? Thanks. — Jasmeet Singh, Mar 17 '15 at 07:41
Sorry, but what inputs you mean? You wrote "I found the solution to my problem". What was the origin of the problem? What version of jqGrid you use now? What problem you have currently? — Oleg, Mar 17 '15 at 07:45

score 0 · Accepted Answer · edited May 23 '17 at 11:52

Finally I found the solution for the above problem by tracing the request in Fiddler, in the response title following error was shown:

Error during serialization or deserialization using the JSON JavaScriptSerializer. The length of the string exceeds the value set on the maxJsonLength property.

Upon searching for the above error, I found a workaround: Here

Basically the problem area was not the jqGrid, but was MaxJsonLength property of JavaScriptSerializer which defaults to 2097152 characters ~ 4 MB of data. Source: MaxJsonLength

To get it working, I replaced the following code in my Action method:

return Json(jsonData, JsonRequestBehavior.AllowGet);

with:

var jsonResult = Json(jsonData, JsonRequestBehavior.AllowGet);
            jsonResult.MaxJsonLength = int.MaxValue;
            return jsonResult;

Thanks.

`JavaScriptSerializer` is the oldest JSON serializer. After that Microsoft suggested multiple other like `DataContractSerializer`. Currently Microsoft suggest to use [Newtonsoft.Json](http://james.newtonking.com/) (see [here](https://json.codeplex.com/releases/view/598599) and [here](https://www.nuget.org/packages/Newtonsoft.Json)). You can change the default JSON serialize to another one even if you use old MVC. See [the answer](http://stackoverflow.com/a/14591946/315935) and [this](http://stackoverflow.com/a/13274791/315935) or [this](http://stackoverflow.com/a/12858020/315935) for example. — Oleg, Mar 17 '15 at 07:57

jqgrid not showing any results when count exceeds ~9000

1 Answers1