ColdFusion data source for jqGrid

[iccsi]

I am using jqGrid for my application and found the example from jQuery web site for first grid like following.

jqGrid expect url to GET data from server side code and data type is XML.

I would like to know can I use CFQUERY or CFSTOREPROC to feed jqGrid to create the grid.

Your information and help is great appreciated,

Regards,

Iccsi,

$(function () {

    $("#list").jqGrid({

        url: "example.php",

        datatype: "xml",

        mtype: "GET",

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

        colModel: [

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

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

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

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

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

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

        ],

        pager: "#pager",

        rowNum: 10,

        rowList: [10, 20, 30],

        sortname: "invid",

        sortorder: "desc",

        viewrecords: true,

        gridview: true,

        autoencode: true,

        caption: "My first grid"

    });

});

[p_sim]

Replace "example.php" with a CFM file that produces XML document as text (not ColdFusion XML object). That's it!

You can either get the data from database using cfquery or cfstoredproc. If you use MS-SQL Server or any enterprise DBMS, then I highly recommend to use cfstoredproc.

[iccsi]

I use following SQL to create a table and enter some data and cfc file to generate JSON data and change url to Example.cfc, but no luck,

I am looking for CSS and JS file to see any mistake there,

Thanks again for helping,

Iccsi,

CREATE TABLE invheader (                                                    

  invid int(11) NOT NULL AUTO_INCREMENT,                                            

  invdate date NOT NULL,                                                         

  client_id int(11) NOT NULL,                                                    

  amount decimal(10,2) NOT NULL DEFAULT '0.00',                                  

  tax decimal(10,2) NOT NULL DEFAULT '0.00',                                     

  total decimal(10,2) NOT NULL DEFAULT '0.00',                                   

  note char(100) DEFAULT NULL,                                

  PRIMARY KEY  (invid)

);

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

<cfquery name="qryLoc">

SELECT invid, invdate, client_id, amount, tax, notes  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["client_id"] = "#qryLoc.client_id#">

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

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

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

<cfset data  = row>

<cfset i = i + 1>

</cfloop>

<!— Caution !!! : turn off the CF debuging, because

garbage could be sezialised with the retrieved data —>

<cfreturn #serializeJSON(data)#>

   <cfreturn stcReturn>--->

</cfoutput>

</cffunction>

$(function () {

    $("#list").jqGrid({

        url: "Example.cfc?method=getLoc",

        datatype: "json",

        mtype: "GET",

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

        colModel: [

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

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

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

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

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

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

        ],

        pager: "#pager",

        rowNum: 10,

        rowList: [10, 20, 30],

        sortname: "invid",

        sortorder: "desc",

        viewrecords: true,

        gridview: true,

        autoencode: true,

        caption: "My first grid"

    });

});

[p_sim]

"no luck" -- What was the problem? Error or the grid was not displayed? If you suspect the JS or CSS causing the problem, then you should see the error from Firebug or Chrome Developer Tools.

[p_sim]

I was able to run jqGrid on my local machine. Basically, you only need to adapt the example code from the web site. And, you are all set!

<!--- xml_data.cfm --->

<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>

<!--- jqGrid.html --->

<html>

<head>

  <title>jqGrid</title>

  <link rel="stylesheet" type="text/css" media="screen" href="http://trirand.com/blog/jqgrid/themes/redmond/jquery-ui-custom.css" />

  <link rel="stylesheet" type="text/css" media="screen" href="http://trirand.com/blog/jqgrid/themes/ui.jqgrid.css" />

  <link rel="stylesheet" type="text/css" media="screen" href="http://trirand.com/blog/jqgrid/themes/ui.multiselect.css" />

  <script src="http://trirand.com/blog/jqgrid/js/jquery.js" type="text/javascript"></script>

  <script src="http://trirand.com/blog/jqgrid/js/jquery-ui-custom.min.js" type="text/javascript"></script>

  <script src="http://trirand.com/blog/jqgrid/js/jquery.layout.js" type="text/javascript"></script>

  <script src="http://trirand.com/blog/jqgrid/js/i18n/grid.locale-en.js" type="text/javascript"></script>

  <script type="text/javascript">

  $.jgrid.no_legacy_api = true;

  $.jgrid.useJSON = true;

  </script>

  <script src="http://trirand.com/blog/jqgrid/js/ui.multiselect.js" type="text/javascript"></script>

  <script src="http://trirand.com/blog/jqgrid/js/jquery.jqGrid.js" type="text/javascript"></script>

  <script src="http://trirand.com/blog/jqgrid/js/jquery.tablednd.js" type="text/javascript"></script>

  <script src="http://trirand.com/blog/jqgrid/js/jquery.contextmenu.js" type="text/javascript"></script>

  <script type="text/javascript">

  jQuery().ready(function (){

    jQuery("#list1").jqGrid({

        url:'http://localhost:8888/test/xml_data.cfm',

        datatype: "xml",

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

        colModel:[

          {name:'id',index:'id', width:75},

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

          {name:'name',index:'name', width:100},

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

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

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

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

        ],

        rowNum:10,

        autowidth: true,

        rowList:[10,20,30],

        pager: jQuery('#pager1'),

        sortname: 'id',

        viewrecords: true,

        sortorder: "desc",

        caption:"XML Example"

    })

    .navGrid('#pager1',{edit:false,add:false,del:false});

  });

  </script>

</head>

<body>

  <table id="list1"></table>

  <div id="pager1"></div>

</body>

</html>

[p_sim]

There was a JavaScript error, however. And, I found it by running Chrome Developer Tools. The jQuery code missing the closing brace and parenthesis.

  });

  </script>

And, of course you need to include jQuery library, jqGrid plug-in, CSS, etc.

[iccsi]

Thanks for the message and help,

Google Chrome does not show the Gird, but I go to Developer tool Console show the following error,

I got error message like following from Google Chrome

Uncaught ReferenceError: jQuery is not defined grid.locale-en.js:168

Uncaught ReferenceError: jQuery is not defined jquery.jqGrid.min.js:172

Uncaught SyntaxError: Unexpected token } jGrid.cfm:46

Regards,

Iccsi,

[p_sim]

If you got those errors, that means you were in the right direction to make the jqGrid working.

The first two errors "jQuery is not defined...." -- You include the libraries.

<script src="http://trirand.com/blog/jqgrid/js/i18n/grid.locale-en.js" type="text/javascript"></script>

<script src="http://trirand.com/blog/jqgrid/js/jquery.jqGrid.js" type="text/javascript"></script>

The last error is the one I mentioned in the previous post.

});

  </script>

[iccsi]

Thanks for the message and help,

it seems that Example.cfc does not return data to jQuery.cfm.

Regards,

iccsi,

[p_sim]

You can test by calling the CFC.

Example.cfc?method=getLoc

Does the method return any data? You might also need header.

[iccsi]

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

Yes, I do get data from Example.cfc,

Regards,

Iccsi,

[iccsi]

it seems that cfc does not return json data type that jquery grid looking for.

I just got some json format from web site that json type cfc returns do not have "page", "total", "records" and "rows"

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"]}

]}

[iccsi]

I see data when I use xml data like you code, but not cfc,

Example.cfc returns data, but not jQuery Grid does not see data from Example.cfc,

I would like to know can ColDFusion return XML data type?

Regards,

Iccsi,

[p_sim · Accepted solution]

As I mentioned before, you might need to specify the header when you return JSON from CFC.

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

I didn't test it, but you can try. Also, make sure that your JSON is formatted correctly.

"I would like to know can ColDFusion return XML data type?" -- Please see the ColdFusion documentation. It tells you what kind of data type you can return.

[iccsi]

Thanks a million for helping,

Regards,

iccsi,

[Adam Cameron.]

Just a note: the MIME type for JSON is application/json, not text/json. I agree the latter would make more sense, but there you go.

--

Adam

[iccsi]

Thnaks for the message,

I did not inlcude jquery js, once I have it then I got grid, but no data,

I am looking to why it does not have data,

Thanks again for helping,

Regards,

Iccsi,

[p_sim]

This page gives you an example how you should generate the XML (in PHP), but you can see the XML structure. Then, you adapt it to ColdFusion.

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