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

ColdFusion data source for jqGrid

  • July 27, 2013
  • 2 replies
  • 4728 views

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"

    });

});

    This topic has been closed for replies.
    Correct answer p_sim

    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,


    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.


    2 replies

    p_sim
    p_sim
    Participating Frequently
    July 27, 2013

    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

    p_sim
    p_sim
    Participating Frequently
    July 27, 2013

    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.

    I
    iccsiAuthor
    Inspiring
    July 27, 2013

    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
    p_sim
    Participating Frequently
    July 27, 2013

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

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