Skip to main content
I
iccsi
Inspiring
July 27, 2013
Answered

JQuery GRID JSON data

  • July 27, 2013
  • 2 replies
  • 2142 views

I have following code to use JQuery GRID.

I tried to use XML data first, using DataXML.cfm which works.

After that, I tried to use JSON using following code which is the same I just change datatype to json.

I use url to test my cfm file which returns data.

Can any one help me or suggestion to see where I can look the following javaScripts or cfm file for JQuery GRID to find a solution,

Your help and information is great appreciated,

Iccsi,

following is cfc file

<cffunction name="getLoc" access="remote" returntype="any" returnformat="json">

<cfquery name="qryLoc" datasource="Mysource">

SELECT invid, invdate, amount, tax, total, note  FROM invheader

</cfquery>

<cfoutput>

<cfset i = 1>

<cfset data = ArrayNew(1)>

<cfloop query="qryLoc">

<cfset row = StructNew()>

<cfset row["invid"] = "#qryLoc.invid#">

<cfset row["invdate"] = "#qryLoc.invdate#">

<cfset row["amount"] = "#qryLoc.amount#">

<cfset row["tax"] = "#qryLoc.tax#">

<cfset row["total"] = "#qryLoc.total#">

<cfset row["note"] = "#qryLoc.note#">

<cfset data  = row>

<cfset i = i + 1>

</cfloop>

<cfreturn #serializeJSON(data)#>

</cfoutput>

</cffunction>

following  is jGrid.cfm

$(function () {

    $("#list").jqGrid({

        url: 'http://127.0.0.1/test/Example.cfc?method=getLoc',

        datatype: 'json',

      

        colNames: ['Tax', 'Inv No', 'Date', 'Notes', 'Total', 'Amount' ],

        colModel: [

            { name: 'tax', width: 80, align: 'right' },

            { name: 'invid', width: 55 },

            { name: 'invdate', width: 90 },

            { name: 'note', width: 150, sortable: false },

            { name: 'total', width: 80, align: 'right' },

            { name: 'amount', width: 80, align: 'right' }

         

          

        ],

        pager: '#pager',

        rowNum: 10,

        rowList: [10, 20, 30],

        sortname: 'invid',

        sortorder: 'desc',

        viewrecords: true,

        gridview: true,

        autoencode: true,

        caption: 'My first grid'

    });

  });

</script>

</head>

<body>

<table id="list"><tr><td></td></tr></table>

    <div id="pager"></div>

</body>

</html>

XMLData.cfm is following data

<cfcontent type="text/xml;charset=utf-8" />

<rows>

   <page>1</page>

   <total>1</total>

   <records>1</records>

   <row id='1'>

       <cell>1</cell>

       <cell>07/26/2013</cell>

       <cell><![CDATA[Client 1]]></cell>

       <cell>700</cell>

       <cell>140</cell>

       <cell>840</cell>

       <cell><![CDATA[Nice work!]]></cell>

   </row>

</rows>

my cfc file returns following data

[{"tax":10.00,"invid":1,"invdate":"July, 24 2013 00:00:00","note":"Test","total":10.00,"amount":10.00},{"tax":50.00,"invid":2,"invdate":"July, 03 2013 00:00:00","note":"test","total":100.00,"amount":20.00},{"tax":50.00,"invid":3,"invdate":"July, 15 2013 00:00:00","note":"test","total":100.00,"amount":20.00}]

    This topic has been closed for replies.
    Correct answer p_sim

    Thanks a million again for helping,

    It seems that my cfc file does not return total, page, records, and rows, the grid does not have pager.

    I entered 12 records, but it shows on one page only, there is no previous, next, first, last button and there is no page to select in the dropdown,

    Thanks again,

    Regards,

    Iccsi,


    Did you look at the example in the jqGrid Demos page? The answer is there.

    Open http://trirand.com/blog/jqgrid/jqgrid.html

    Open Chrome Developer Tools

    See what is the URL when you click the next page button on the grid?

    http://trirand.com/blog/jqgrid/server.php?q=1&_search=false&nd=1374957231342&rows=10&page=2&sidx=id&sord=desc

    If you click the previous page:

    http://trirand.com/blog/jqgrid/server.php?q=1&_search=false&nd=1374957248411&rows=10&page=1&sidx=id&sord=desc

    As you can see the page value in the query string change from 2 to 1.

    Then, see the PHP code. The page variable is use to retrieve data.

    That means if you give a number of rows, the grid does not calculate page number automatically. You should do the calculation from the CFC.

    2 replies

    p_sim
    p_sim
    Participating Frequently
    July 27, 2013

    Your CFC should return like this:

    {

       "page":"1",

       "total":1,

       "records":"10",

       "rows":[

          {

             "tax":10.00,

             "invid":1,

             "invdate":"July, 24 2013 00:00:00",

             "note":"Test",

             "total":10.00,

             "amount":10.00

          },

          {

             "tax":50.00,

             "i nvid":2,

             "invdate":"July, 03 2013 00:00:00",

             "note":"test",

             "total":100.00,

             "amount":20.00

          },

          {

             "tax":50.00,

             " invid":3,

             "invdate":"July, 15 2013 00:00:00",

             "note":"test",

             "total":100.00,

             "amount":20.00

          }

       ]

    }

    Tested and the jqGrid worked!

    I
    iccsiAuthor
    Inspiring
    July 27, 2013

    Thanks for the mesage and help,

    so my cfc does not return right JSON format,

    I added header as you recommend, but still missing page, total, row and rows,

    <cfcontent type="text/json;charset=utf-8" />

    Regards,

    Iccsi,

    p_sim
    p_sim
    Participating Frequently
    July 27, 2013

    The mime header is incorrect. Please refer to the one that Adam responded.

    I
    iccsiAuthor
    Inspiring
    July 27, 2013

    it seems that JSON returned by cfc format is not the format jQuery grid expected.

    I got some format from web site like following,

    Regards,

    Iccsi,

    {

    "page":"1",

    "total":1,

    "records":"10",

    "rows":[

    {"id":"2:1","cell":["1","image","Chief Scout","Highest Award test","0"]},

    {"id":"2:2","cell":["2","image","Link Badge","When you are invested as a Scout, you may be eligible to receive a Link Badge. (See page 45)","0"]},

    {"id":"2:3","cell":["3","image","Pioneer Scout","Upon completion of requirements, the youth is invested as a Pioneer Scout","0"]},

    {"id":"2:4","cell":["4","image","Voyageur Scout Award","Voyageur Scout Award is the right after Pioneer Scout.","0"]},

    {"id":"2:5","cell":["5","image","Voyageur Citizenship","Learning about and caring for your community.","0"]},

    {"id":"2:6","cell":["6","image","Fish and Wildlife","Demonstrate your knowledge and involvement in fish and wildlife management.","0"]},

    {"id":"2:7","cell":["7","image","Photography","To recognize photography knowledge and skills","0"]},

    {"id":"2:8","cell":["8","image","Recycling","Demonstrate your knowledge and involvement in Recycling","0"]},

    {"id":"2:10","cell":["10","image","Voyageur Leadership ","Show leadership ability","0"]},

    {"id":"2:11","cell":["11","image","World Conservation","World Conservation Badge","0"]}

    ]}

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices