jqGrid fetched json but has empty rows and no data

Question

JSON As generated by Spring 3 MVC @ResponseBody

{
    "total": "1",
    "page": "1",
    "records": "2",
    "rows": [
        {
            "id": "1",
            "cell": {
                "accountId": 1,
                "userId": 1,
                "transactionId": 6,
                "subCatId": 0,
                "accountName": "Credit Card",
                "remarks": "Movie Hall Pass",
                "amount": 250.0,
                "transactionDate": "2011-03-16",
                "subCatName": "Entertainment"
            }
        },
        {
            "id": "2",
            "cell": {
                "accountId": 2,
                "userId": 1,
                "transactionId": 7,
                "subCatId": 1,
                "accountName": "Savings Bank",
                "remarks": "Part at Besand Nagar",
                "amount": 5000.0,
                "transactionDate": "2011-03-16",
                "subCatName": "Dine Out"
            }
        }
    ]
}

JQGrid Initialization Code:

$("#transactionLogTable").jqGrid({
                url: '/et/transaction/getTransactions?dateValue=03%2F16%2F2011',
                datatype: "json",
                loadError: function(xhr,status,error){alert(status+" "+error);},
                colNames:['Transaction ID', 'User ID', 'Subcat ID', 'Subcat Name',
                          'Account ID', 'Account Name', 'Date', 'Amount', 'Notes'],

                colModel:[
                    {name: 'transactionId', index: 'transactionId', width: 100},
                    {name: 'userid', index: 'userId', width: 100},
                    {name: 'subCatId', index: 'subCatId', width: 100},
                    {name: 'subCatName', index: 'subCatName', width: 100},
                    {name: 'accountId', index: 'accountId', width: 100},
                    {name: 'accountName', index: 'accountName', width: 100},
                    {name: 'transactionDate', index: 'transactionDate', width: 100},
                    {name: 'amount', index: 'amount', width: 100},
                    {name: 'remarks', index: 'remarks', width: 100}
                ],
                pager: "#pager",
                rowNum: 10,
                rowList: [10,20,30],
                sortname: 'userId',
                sortorder: 'asc',
                viewrecords: true,
                caption: 'Transactions'
            });

The server is been hit successfully with QueryString as:

dateValue=03%2F16%2F2011&_search=false&nd=1300532086871&rows=10&page=1&sidx=userId&sord=asc

Fine now, I get a screen which has the jqGrid displayed and 2 empty rows in it. I can't get to display the data inside the rows.

I guess it's something related to the mapping, but I have tried as many combinations I can.

Included files and versions:

<script type="text/javascript" language="javascript" src="/et/resources/js/jquery.jqGrid-3.8.2.full/js/jquery-1.4.2.min.js"></script>
<script type="text/javascript" language="javascript" src="/et/resources/js/jquery-ui-1.8.10.start/js/jquery-ui-1.8.10.custom.min.js"></script>
<script type="text/javascript" language="javascript" src="/et/resources/js/form-2.52.js"></script>
<script type="text/javascript" language="javascript" src="/et/resources/js/validate-1.7.js"></script>
<script type="text/javascript" language="javascript" src="/et/resources/js/jquery.jqGrid-3.8.2.full/js/i18n/grid.locale-en.js" charset="utf-8"></script>
<script type="text/javascript" language="javascript" src="/et/resources/js/jquery.jqGrid-3.8.2.full/js/jquery.jqGrid.min.js"></script>

Appreciate your help.

Firdous Amir

Answer 1

Your main error is that the data are wrong formatted. You should use

{
    "total": "1",
    "page": "1",
    "records": "2",
    "rows": [
        {
            "id": "1",
            "cell": ["1", "1", "6", "0", "Credit Card", "Movie Hall Pass",
                     "250.0", "2011-03-16", "Entertainment" ]
        },
        ...
    ]
}

instead of

{
    "total": "1",
    "page": "1",
    "records": "2",
    "rows": [
        {
            "id": "1",
            "cell": {
                "accountId": 1,
                "userId": 1,
                "transactionId": 6,
                "subCatId": 0,
                "accountName": "Credit Card",
                "remarks": "Movie Hall Pass",
                "amount": 250.0,
                "transactionDate": "2011-03-16",
                "subCatName": "Entertainment"
            }
        },
        ...
    ]
}

In general jqGrid is flexible enough to read almost any JSON data. You can just define the jsonReader jqGrid parameter and sometime additionally jsonmap property in the column definition. In your case for example one can read your data with the following jqGrid definition

$("#transactionLogTable").jqGrid({
    // ... other parameters
    cmTemplate: {width: 100},
    colModel:[
        {name:'transactionId',   jsonmap: 'cell.transactionId'},
        {name:'userId',          jsonmap: 'cell.userId'},
        {name:'subCatId',        jsonmap: 'cell.subCatId'},
        {name:'subCatName',      jsonmap: 'cell.subCatName'},
        {name:'accountId',       jsonmap: 'cell.accountId'},
        {name:'accountName',     jsonmap: 'cell.accountName'},
        {name:'transactionDate', jsonmap: 'cell.transactionDate'},
        {name:'amount',          jsonmap: 'cell.amount'},
        {name:'remarks',         jsonmap: 'cell.remarks'}
    ],
    height: "auto",
    jsonReader: { repeatitems: false }
});

Here I used jsonReader: { repeatitems: false } to define that JSON data for a row are not in array for, but in for of object with named property. The property like jsonmap: "cell.userId" is needed to specify that the value for the corresponding grid column should be not as the default userId property of the row object, but are additionally are the child of the "cell" property. By the way you use 'userid' as the column name and 'userId' in the JSON data. It is better to use the same names as the JSON data. In you use the same 'index' property as the 'name' you can drop out the 'index'. In the case the value of the 'name' property will be used as the 'index'.

Because you used "width:100" property for all columns of your grid I used cmTemplate: {width: 100} parameter to make colModel definition shorter and better to read.

You can see the modified grid live here.

I recommend you additionally post the date always in the ISO form YYYY-mm-dd and use formatter:'date' and datefmt properties of colModel (for example formatter:'date', formatoptions:{newformat:'d-m-Y'}, datefmt: 'd-m-Y')