Highlighted

Retrieving Data using jqGrid

Explorer ,
Sep 04, 2015

Copy link to clipboard

Copied

Hi-

I am trying to retrieve data into jqGrid but it seems that my data is not converted to json or maybe the case is something else. In short can't populate the jqGrid. My files are as follows

Users.cfm

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Untitled Document</title>

<link rel="stylesheet" type="text/css" media="screen" href="steel/grid.css" />

<link rel="stylesheet" type="text/css" media="screen" href="steel/jqModal.css" />

<script src="js/jquery-1.11.0.min.js"></script>

<script src="js/jquery.jqGrid.min.js" type="text/javascript"></script>

<script src="src/jqModal.js" type="text/javascript"></script>

<script src="src/jqDnR.js" type="text/javascript"></script>

<script src="src/jquery.fmatter.js" type="text/javascript"></script>

</head>

<body>

<table id="list" class="scroll" cellpadding="0" cellspacing="0"></table>

<div id="pager" class="scroll" style="text-align:center;"></div>

<div id="mysearch"></div>

<script>

$(document).ready(function()

        {

            $("#list").jqGrid(

            {

                url:"Users.cfc?method=getUsers", //CFC that will return the users

                datatype: "json", //We specify that the datatype we will be using will be JSON

                colNames:['ID','FirstName','LastName', 'DisplayName','UserName','UserAccountingCode','Phone'], //Column Names

                //The Column Model to define the data. Note you can make columns non sortable, specify width, alignment, etc.

                colModel :[

                    {name:'id',index:'id', width:50, sorttype:"int"},

                    {name:'FirstName',index:'FirstName', width:150, sorttype:"string"},

                    {name:'LastName',index:'LastName', width:150, align:"left",sorttype:"string"},

                    {name:'DisplayName',index:'DisplayName', width:150, align:"left",sorttype:"string"},

                    {name:'UserName',index:'UserName', width:150,align:"left",sorttype:"string"},

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

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

                ],

                pager: $('#pager'), //The div we have specified, tells jqGrid where to put the pager

                rowNum:4, //Number of records we want to show per page

                rowList:[4,8,12], //Row List, to allow user to select how many rows they want to see per page

                sortorder: "asc", //Default sort order

                sortname: "ID", //Default sort column

                viewrecords: true, //Shows the nice message on the pager

                imgpath: '/steel/images', //Image path for prev/next etc images

                caption: 'Users', //Grid Name

                height:'auto', //I like auto, so there is no blank space between. Using a fixed height can mean either a scrollbar or a blank space before the pager

                recordtext:'Total Records', //On the demo you will notice "7 Total Records" - The Total Reocrds text comes from here

                pgtext:'/',//You notice the 1/3, you can change the /. You can make it say 1 of 3

                editurl:"Users.cfc?method=getUsers",//Not used right now.

                toolbar:[true,"top"],//Shows the toolbar at the top. I will decide if I need to put anything in there later.

                //The JSON reader. This defines what the JSON data returned from the CFC should look like

                jsonReader: {

                    root: "ROWS", //our data

                    page: "PAGE", //current page

                    total: "TOTAL", //total pages

                    records:"RECORDS", //total records

                    cell: "", //not used

                    id: "0" //will default first column as ID

                    }

                }

            );      

      

        }

    );

</script>

</body>

</html>

Users.cfc

<cffunction name="getUsers" access="remote" returnformat="json">

     <cfargument name="page" required="no" default="1" hint="Page user is on">

    <cfargument name="rows" required="no" default="10" hint="Number of Rows to display per page">

    <cfargument name="sidx" required="no" default="" hint="Sort Column">

    <cfargument name="sord" required="no" default="ASC" hint="Sort Order">

        <cfset var arrUsers = ArrayNew(1)>

  

  

        <cfquery name="selUsers" datasource="myDataSource">

            SELECT

                ID, FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone

            FROM

                Users

            <!--- Sorting Here --->

            <cfif Arguments.sidx NEQ "">

                ORDER BY #Arguments.sidx# #Arguments.sord#

            <cfelse>

                ORDER BY ID #Arguments.sord#

            </cfif>

      

      

        </cfquery>

  

        <!--- Calculate the Start Position for the loop query.

        So, if you are on 1st page and want to display 4 rows per page, for first page you start at: (1-1)*4+1 = 1.

        If you go to page 2, you start at (2-)1*4+1 = 5 --->

        <cfset start = ((arguments.page-1)*arguments.rows)+1>

  

        <!--- Calculate the end row for the query. So on the first page you go from row 1 to row 4. --->

        <cfset end = (start-1) + arguments.rows>

  

        <!--- When building the array --->

        <cfset i = 1>

  

        <cfloop query="selUsers" startrow="#start#" endrow="#end#">

            <!--- Array that will be passed back needed by jqGrid JSON implementation --->  

            <cfset arrUsers = [#ID#,#FirstName#,#LastName#,#DisplayName#,#UserName#,#UserAccountingCode#,#Phone#]>

            <cfset i = i + 1>      

        </cfloop>

  

        <!--- Calculate the Total Number of Pages for your records. --->

        <cfset totalPages = Ceiling(selUsers.recordcount/arguments.rows)>

  

        <!--- The JSON return.

            Total - Total Number of Pages we will have calculated above

            Page - Current page user is on

            Records - Total number of records

            rows = our data

        --->

<cfset stcReturn = {total=#totalPages#,page=#Arguments.page#,records=#selUsers.recordcount#,rows=arrUsers}>

  

    <cfreturn stcReturn>

  

    </cffunction>

Please help.

Thanks

Tayyab Hussain

Views

1.6K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Retrieving Data using jqGrid

Explorer ,
Sep 04, 2015

Copy link to clipboard

Copied

Hi-

I am trying to retrieve data into jqGrid but it seems that my data is not converted to json or maybe the case is something else. In short can't populate the jqGrid. My files are as follows

Users.cfm

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Untitled Document</title>

<link rel="stylesheet" type="text/css" media="screen" href="steel/grid.css" />

<link rel="stylesheet" type="text/css" media="screen" href="steel/jqModal.css" />

<script src="js/jquery-1.11.0.min.js"></script>

<script src="js/jquery.jqGrid.min.js" type="text/javascript"></script>

<script src="src/jqModal.js" type="text/javascript"></script>

<script src="src/jqDnR.js" type="text/javascript"></script>

<script src="src/jquery.fmatter.js" type="text/javascript"></script>

</head>

<body>

<table id="list" class="scroll" cellpadding="0" cellspacing="0"></table>

<div id="pager" class="scroll" style="text-align:center;"></div>

<div id="mysearch"></div>

<script>

$(document).ready(function()

        {

            $("#list").jqGrid(

            {

                url:"Users.cfc?method=getUsers", //CFC that will return the users

                datatype: "json", //We specify that the datatype we will be using will be JSON

                colNames:['ID','FirstName','LastName', 'DisplayName','UserName','UserAccountingCode','Phone'], //Column Names

                //The Column Model to define the data. Note you can make columns non sortable, specify width, alignment, etc.

                colModel :[

                    {name:'id',index:'id', width:50, sorttype:"int"},

                    {name:'FirstName',index:'FirstName', width:150, sorttype:"string"},

                    {name:'LastName',index:'LastName', width:150, align:"left",sorttype:"string"},

                    {name:'DisplayName',index:'DisplayName', width:150, align:"left",sorttype:"string"},

                    {name:'UserName',index:'UserName', width:150,align:"left",sorttype:"string"},

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

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

                ],

                pager: $('#pager'), //The div we have specified, tells jqGrid where to put the pager

                rowNum:4, //Number of records we want to show per page

                rowList:[4,8,12], //Row List, to allow user to select how many rows they want to see per page

                sortorder: "asc", //Default sort order

                sortname: "ID", //Default sort column

                viewrecords: true, //Shows the nice message on the pager

                imgpath: '/steel/images', //Image path for prev/next etc images

                caption: 'Users', //Grid Name

                height:'auto', //I like auto, so there is no blank space between. Using a fixed height can mean either a scrollbar or a blank space before the pager

                recordtext:'Total Records', //On the demo you will notice "7 Total Records" - The Total Reocrds text comes from here

                pgtext:'/',//You notice the 1/3, you can change the /. You can make it say 1 of 3

                editurl:"Users.cfc?method=getUsers",//Not used right now.

                toolbar:[true,"top"],//Shows the toolbar at the top. I will decide if I need to put anything in there later.

                //The JSON reader. This defines what the JSON data returned from the CFC should look like

                jsonReader: {

                    root: "ROWS", //our data

                    page: "PAGE", //current page

                    total: "TOTAL", //total pages

                    records:"RECORDS", //total records

                    cell: "", //not used

                    id: "0" //will default first column as ID

                    }

                }

            );      

      

        }

    );

</script>

</body>

</html>

Users.cfc

<cffunction name="getUsers" access="remote" returnformat="json">

     <cfargument name="page" required="no" default="1" hint="Page user is on">

    <cfargument name="rows" required="no" default="10" hint="Number of Rows to display per page">

    <cfargument name="sidx" required="no" default="" hint="Sort Column">

    <cfargument name="sord" required="no" default="ASC" hint="Sort Order">

        <cfset var arrUsers = ArrayNew(1)>

  

  

        <cfquery name="selUsers" datasource="myDataSource">

            SELECT

                ID, FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone

            FROM

                Users

            <!--- Sorting Here --->

            <cfif Arguments.sidx NEQ "">

                ORDER BY #Arguments.sidx# #Arguments.sord#

            <cfelse>

                ORDER BY ID #Arguments.sord#

            </cfif>

      

      

        </cfquery>

  

        <!--- Calculate the Start Position for the loop query.

        So, if you are on 1st page and want to display 4 rows per page, for first page you start at: (1-1)*4+1 = 1.

        If you go to page 2, you start at (2-)1*4+1 = 5 --->

        <cfset start = ((arguments.page-1)*arguments.rows)+1>

  

        <!--- Calculate the end row for the query. So on the first page you go from row 1 to row 4. --->

        <cfset end = (start-1) + arguments.rows>

  

        <!--- When building the array --->

        <cfset i = 1>

  

        <cfloop query="selUsers" startrow="#start#" endrow="#end#">

            <!--- Array that will be passed back needed by jqGrid JSON implementation --->  

            <cfset arrUsers = [#ID#,#FirstName#,#LastName#,#DisplayName#,#UserName#,#UserAccountingCode#,#Phone#]>

            <cfset i = i + 1>      

        </cfloop>

  

        <!--- Calculate the Total Number of Pages for your records. --->

        <cfset totalPages = Ceiling(selUsers.recordcount/arguments.rows)>

  

        <!--- The JSON return.

            Total - Total Number of Pages we will have calculated above

            Page - Current page user is on

            Records - Total number of records

            rows = our data

        --->

<cfset stcReturn = {total=#totalPages#,page=#Arguments.page#,records=#selUsers.recordcount#,rows=arrUsers}>

  

    <cfreturn stcReturn>

  

    </cffunction>

Please help.

Thanks

Tayyab Hussain

Views

1.6K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Sep 04, 2015 0

Have something to add?

Join the conversation