17 Replies Latest reply on Jul 27, 2013 10:46 AM by Adam Cameron.

    ColdFusion data source for jqGrid

    iccsi Level 1

      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"

          });

      });

        • 1. Re: ColdFusion data source for jqGrid
          p.sim Level 3

          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.

          • 2. Re: ColdFusion data source for jqGrid
            p.sim Level 3

            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

            • 3. Re: ColdFusion data source for jqGrid
              iccsi Level 1

              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[i]  = 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"

                  });

              });

              • 4. Re: ColdFusion data source for jqGrid
                p.sim Level 3

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

                • 5. Re: ColdFusion data source for jqGrid
                  p.sim Level 3

                  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>

                  • 6. Re: ColdFusion data source for jqGrid
                    p.sim Level 3

                    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.

                    • 7. Re: ColdFusion data source for jqGrid
                      iccsi Level 1

                      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,

                      • 8. Re: ColdFusion data source for jqGrid
                        p.sim Level 3

                        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>

                        • 9. Re: ColdFusion data source for jqGrid
                          iccsi Level 1

                          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,

                          • 10. Re: ColdFusion data source for jqGrid
                            iccsi Level 1

                            Thanks for the message and help,

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

                             

                             

                            Regards,

                             

                            iccsi,

                            • 11. Re: ColdFusion data source for jqGrid
                              p.sim Level 3

                              You can test by calling the CFC.

                               

                              Example.cfc?method=getLoc

                               

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

                              • 12. Re: ColdFusion data source for jqGrid
                                iccsi Level 1

                                [{"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":"J uly, 03 2013 00:00:00","client_id":1,"note":"test","amount":20.00},{"tax":50.00,"invid":3,"invdate":"J uly, 15 2013 00:00:00","client_id":1,"note":"test","amount":20.00}]

                                 

                                Yes, I do get data from Example.cfc,

                                 

                                 

                                Regards,

                                 

                                Iccsi,

                                • 13. Re: ColdFusion data source for jqGrid
                                  iccsi Level 1

                                  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,

                                  • 14. Re: ColdFusion data source for jqGrid
                                    iccsi Level 1

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

                                    ]}

                                    • 15. Re: ColdFusion data source for jqGrid
                                      p.sim Level 3

                                      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.


                                      • 16. Re: ColdFusion data source for jqGrid
                                        iccsi Level 1

                                        Thanks a million for helping,

                                         

                                         

                                        Regards,

                                         

                                         

                                        iccsi,

                                        • 17. Re: ColdFusion data source for jqGrid
                                          Adam Cameron. Level 5

                                          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