Using jQuery based jqGrid with ColdFusion: Part 4: Full CRUD grid with Custom Search Screen
So, previously I blogged about basic jqGrid implementations with Filter/Search and Inline Edit capabilities. The grid here now builds on all that, and implements Add and Delete functionality. I also changed the theme to the new steel theme (available in version 3.4 zip), its much more professional looking compared to the others in my opinion.
The code is commented, so hopefully that makes things clear. The main thing we do different, is we add custom buttons to the navgrid for search and refresh. When search button is clicked, we display our filter div, which has our custom search form. When the refresh button is clicked, we reset the search form, pass in a grid URL without the search parameters in it. This is basically a reset of the grid to the original url.
We have added the gridSearch function, which will be called from our Search dialog. It gets the form values for the search, and sends it accross to the CFC with added parameters to the grid. The search/filter dialog code, looks like:
The main thing here is that the classes applied to the div, form and TD elements, use the default stylesheet to make our dialog look similar to the Add/Edit screens. You can also generate this using jQuery if you want, but I don't mind writing HTML.
And finally, our good old CFC.
Full Souce Code: Here .
So, thats a pretty complete implementation of the jqGrid. Probably, my last post in the series, atleast until the next version is released, or as always, I find something that excites me in the grid and blog about it. We should also have in the jQuery UI 1.7 version their implementation of the Grid. It will be interesting to see how powerful it is.
Remember to check out the documentation and examples of jqGrid on their site, there is a lot more you can do with it including having subgrids, master/detail type views, toolbar search, etc. It is quite powerful. 3.4 is being released soon, which will add more features.
This implementation doesn't exactly follow the previous posts. For this grid I am using:
- Inbuilt Form based Add, Edit and Delete functionality
- Custom Built Search Dialog Screen
- Custom Refresh Action
- Use jqModal for the custom search dialog.
See The Demo. DB being changed realtime, any operations you perform will happen.
And now lets look at the code.
The HTML is still simple,1 <body>
2 <cfinclude template="user3_search.cfm">
3 <table id="list" class="scroll" cellpadding="0" cellspacing="0"></table>
4 <div id="pager" class="scroll" style="display:block"></div>
5 </body>
The cfinclude builds the search form for us, and we will go over that later. The javascript,
2 <cfinclude template="user3_search.cfm">
3 <table id="list" class="scroll" cellpadding="0" cellspacing="0"></table>
4 <div id="pager" class="scroll" style="display:block"></div>
5 </body>
1 <script>
2 $(document).ready(function()
3 {
4 //Our Search/Filter window, we tell jqModal to treat it as a Modal dialog window
5 $('#filter').jqm();
6 var selectedRow = 0;
7 $("#list").jqGrid(
8 {
9 url:'Users3.cfc?method=getUsers', //CFC that will return the users
10 datatype: 'json', //We specify that the datatype we will be using will be JSON
11 colNames:['ID','First Name','Last Name', 'Display Name','User Name','User Accounting Code','Phone'], //Column Names
12 /*
13 The Column Model to define the data. Note you can make columns non sortable, specify width, alignment, etc.
14 We also specify the editoptions, edittype=text shows a nice textbox for inline edit.
15 We have also specified Edit Rules, to say which fields are required/not required for add/edit
16 */
17 colModel :[
18 {name:'id',index:'id', width:50,resizable:false, sorttype:"int",resizable:false,editrules:{readonly:true}},
19 {name:'FirstName',index:'FirstName', width:150,resizable:false, sorttype:"text",editable:true,edittype:"text",editoptions:{size:30,maxlength:50},editrules:{required:true}},
20 {name:'LastName',index:'LastName', width:150,resizable:false, align:"left",sorttype:"text",editable:true,edittype:"text",editoptions:{size:30,maxlength:50},editrules:{required:true}},
21 {name:'DisplayName',index:'DisplayName', width:150,resizable:false, align:"left",sorttype:"text",editable:true,edittype:"text",editoptions:{size:30,maxlength:50},editrules:{required:true}},
22 {name:'UserName',index:'UserName', width:150,resizable:false,align:"left",sorttype:"text",editable:true,edittype:"text",editoptions:{size:30,maxlength:50},editrules:{required:true}},
23 {name:'UserAccountingCode',index:'UserAccountingCode', width:150,resizable:false, sortable:false,editable:true,edittype:"text",editoptions:{size:15,maxlength:15},editrules:{required:false}},
24 {name:'Phone',index:'Phone', width:150,resizable:false, sortable:false,search:false,editable:true,edittype:"text",editoptions:{size:15,maxlength:12},editrules:{required:false}}
25 ],
26 pager: $('#pager'), //The div we have specified, tells jqGrid where to put the pager
27 rowNum:20, //Number of records we want to show per page
28 rowList:[20,30,40,50], //Row List, to allow user to select how many rows they want to see per page
29 sortorder: "asc", //Default sort order
30 sortname: "ID", //Default sort column
31 viewrecords: true, //Shows the nice message on the pager
32 imgpath: '/Test/themes/steel/images', //Image path for prev/next etc images
33 caption: 'Users', //Grid Name
34 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
35 mtype:'GET',
36 recordtext:'Total Records', //On the demo you will notice "7 Total Records" - The Total Reocrds text comes from here
37 pgtext:' of', //You notice the 1/3, you can change the /. You can make it say 1 of 3
38 editurl:"Users3.cfc?method=addeditUser", //The Add/Edit function call
39 toolbar:[true,"top"], //Shows the toolbar at the top. We will use it to display user feedback
40 //Things to do when grid is finished loading
41 loadComplete:function(){
42 //We get the Userdata for the grid.
43 var recorddata = $("#list").getUserData();
44 //show the msg in the toolbar
45 $("#t_list").html(recorddata.MSG);
46 },
47 //The JSON reader. This defines what the JSON data returned from the CFC should look like
48 jsonReader: {
49 root: "ROWS", //our data
50 page: "PAGE", //current page
51 total: "TOTAL", //total pages
52 records:"RECORDS", //total records
53 userdata:"USERDATA", //Userdata we will pass back for feedback
54 cell: "", //Not Used
55 id: "0" //Will default to frist column
56 }
57 }
58 );//End document.ready function
59
60 //Set toolbar text color to red
61 $("#t_list").css("color","red");
62
63 //Here we use the navGrid to display the Search button with label Filter, and disable the add/edit/delete buttons.
64 $("#list").navGrid(
65 "#pager",{edit:true,edittext:"Edit",addtext:"Add",deltext:"Delete",del:true,search:false,refresh:false},//We will specify custom search/refresh buttons
66 {reloadAfterSubmit:true,afterSubmit:commonSubmit,closeAfterEdit:true,width:"400"},//Options for the Edit Dialog
67 {reloadAfterSubmit:true,afterSubmit:commonSubmit,closeAfterAdd:false,width:"400"},//Options for the Add Dialog
68 {url:"Users3.cfc?method=delUser",closeAfterDelete:true,reloadAftersubmit:true,caption:"Delete User",msg:"Delete selected User(s)",width:"400"}//Options for Del Dialog
69 ).navButtonAdd('#pager',{caption:"Search",title:"Toggle Search",buttonimg:'/Test/themes/steel/images/find.gif', onClickButton: function(id)//We add the Search button.
70 {
71 //If the search screen is hidden
72 if(jQuery("#filter").css("display")=="none")
73 {
74 //We display it
75 $("#filter").jqmShow();
76 }else
77 {
78 //Otherwise its being displayed, so we hide it
79 $("#filter").jqmHide();
80 }
81 }
82 }).navButtonAdd('#pager',{caption:"Refresh",title:"Reload",buttonimg:'/Test/themes/steel/images/refresh.gif',onClickButton:function(id)//The refresh button
83 {
84 //We reset our search form (clear any form values)
85 resetForm();
86 //We reset the Grid URL as we change it for the search in the gridSearch function below. This resets the grid to its original state
87 $("#list").setGridParam(
88 {
89 url:"Users3.cfc?method=getUsers",
90 page:1
91 }
92 ).trigger("reloadGrid");//Reload grid trigger
93 }
94 });
95 });//End navigator manipulations
96
97 //This function will perform the search for us
98 function gridSearch()
99 {
100 //Get the values from the sarch form. Very simple using jquery.
101 var fn = $("#firstname_search").val();
102 var ln = $("#lastname_search").val();
103 var dn = $("#displayname_search").val();
104 var un = $("#username_search").val();
105 var uac = $("#useraccountingcode_search").val();
106 //We set the grid URL and pass in some extra params for the search.
107 $("#list").setGridParam(
108 {
109 url:"Users3.cfc?method=getUsers&FirstName="+fn+"&LastName="+ln+"&DisplayName="+dn+"&UserName="+un+"&UserAccountingCode="+uac,
110 page:1
111 }).trigger("reloadGrid");//Reload grid trigger
112 $("#filter").jqmHide();//Hide our search/filter window dialog
113 }
114
115 //This function is called after an add/edit happens. We just take the MSG from the response and display it in the toolbar.
116 //Note currently, since we reload the grid after the add/edit, the msg will only be visible for a short second or so
117 function commonSubmit(data,params)
118 {
119 var a = eval( "(" + data.responseText + ")" );//Convert returned string into JSON
120 $("#t_list").html(a.USERDATA.MSG);
121 resetForm();//Clear our search form
122 return true;
123 }
124
125 //Clear our search form
126 function resetForm()
127 {
128 document.FilterForm.reset();
129 }
130
131 </script>
2 $(document).ready(function()
3 {
4 //Our Search/Filter window, we tell jqModal to treat it as a Modal dialog window
5 $('#filter').jqm();
6 var selectedRow = 0;
7 $("#list").jqGrid(
8 {
9 url:'Users3.cfc?method=getUsers', //CFC that will return the users
10 datatype: 'json', //We specify that the datatype we will be using will be JSON
11 colNames:['ID','First Name','Last Name', 'Display Name','User Name','User Accounting Code','Phone'], //Column Names
12 /*
13 The Column Model to define the data. Note you can make columns non sortable, specify width, alignment, etc.
14 We also specify the editoptions, edittype=text shows a nice textbox for inline edit.
15 We have also specified Edit Rules, to say which fields are required/not required for add/edit
16 */
17 colModel :[
18 {name:'id',index:'id', width:50,resizable:false, sorttype:"int",resizable:false,editrules:{readonly:true}},
19 {name:'FirstName',index:'FirstName', width:150,resizable:false, sorttype:"text",editable:true,edittype:"text",editoptions:{size:30,maxlength:50},editrules:{required:true}},
20 {name:'LastName',index:'LastName', width:150,resizable:false, align:"left",sorttype:"text",editable:true,edittype:"text",editoptions:{size:30,maxlength:50},editrules:{required:true}},
21 {name:'DisplayName',index:'DisplayName', width:150,resizable:false, align:"left",sorttype:"text",editable:true,edittype:"text",editoptions:{size:30,maxlength:50},editrules:{required:true}},
22 {name:'UserName',index:'UserName', width:150,resizable:false,align:"left",sorttype:"text",editable:true,edittype:"text",editoptions:{size:30,maxlength:50},editrules:{required:true}},
23 {name:'UserAccountingCode',index:'UserAccountingCode', width:150,resizable:false, sortable:false,editable:true,edittype:"text",editoptions:{size:15,maxlength:15},editrules:{required:false}},
24 {name:'Phone',index:'Phone', width:150,resizable:false, sortable:false,search:false,editable:true,edittype:"text",editoptions:{size:15,maxlength:12},editrules:{required:false}}
25 ],
26 pager: $('#pager'), //The div we have specified, tells jqGrid where to put the pager
27 rowNum:20, //Number of records we want to show per page
28 rowList:[20,30,40,50], //Row List, to allow user to select how many rows they want to see per page
29 sortorder: "asc", //Default sort order
30 sortname: "ID", //Default sort column
31 viewrecords: true, //Shows the nice message on the pager
32 imgpath: '/Test/themes/steel/images', //Image path for prev/next etc images
33 caption: 'Users', //Grid Name
34 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
35 mtype:'GET',
36 recordtext:'Total Records', //On the demo you will notice "7 Total Records" - The Total Reocrds text comes from here
37 pgtext:' of', //You notice the 1/3, you can change the /. You can make it say 1 of 3
38 editurl:"Users3.cfc?method=addeditUser", //The Add/Edit function call
39 toolbar:[true,"top"], //Shows the toolbar at the top. We will use it to display user feedback
40 //Things to do when grid is finished loading
41 loadComplete:function(){
42 //We get the Userdata for the grid.
43 var recorddata = $("#list").getUserData();
44 //show the msg in the toolbar
45 $("#t_list").html(recorddata.MSG);
46 },
47 //The JSON reader. This defines what the JSON data returned from the CFC should look like
48 jsonReader: {
49 root: "ROWS", //our data
50 page: "PAGE", //current page
51 total: "TOTAL", //total pages
52 records:"RECORDS", //total records
53 userdata:"USERDATA", //Userdata we will pass back for feedback
54 cell: "", //Not Used
55 id: "0" //Will default to frist column
56 }
57 }
58 );//End document.ready function
59
60 //Set toolbar text color to red
61 $("#t_list").css("color","red");
62
63 //Here we use the navGrid to display the Search button with label Filter, and disable the add/edit/delete buttons.
64 $("#list").navGrid(
65 "#pager",{edit:true,edittext:"Edit",addtext:"Add",deltext:"Delete",del:true,search:false,refresh:false},//We will specify custom search/refresh buttons
66 {reloadAfterSubmit:true,afterSubmit:commonSubmit,closeAfterEdit:true,width:"400"},//Options for the Edit Dialog
67 {reloadAfterSubmit:true,afterSubmit:commonSubmit,closeAfterAdd:false,width:"400"},//Options for the Add Dialog
68 {url:"Users3.cfc?method=delUser",closeAfterDelete:true,reloadAftersubmit:true,caption:"Delete User",msg:"Delete selected User(s)",width:"400"}//Options for Del Dialog
69 ).navButtonAdd('#pager',{caption:"Search",title:"Toggle Search",buttonimg:'/Test/themes/steel/images/find.gif', onClickButton: function(id)//We add the Search button.
70 {
71 //If the search screen is hidden
72 if(jQuery("#filter").css("display")=="none")
73 {
74 //We display it
75 $("#filter").jqmShow();
76 }else
77 {
78 //Otherwise its being displayed, so we hide it
79 $("#filter").jqmHide();
80 }
81 }
82 }).navButtonAdd('#pager',{caption:"Refresh",title:"Reload",buttonimg:'/Test/themes/steel/images/refresh.gif',onClickButton:function(id)//The refresh button
83 {
84 //We reset our search form (clear any form values)
85 resetForm();
86 //We reset the Grid URL as we change it for the search in the gridSearch function below. This resets the grid to its original state
87 $("#list").setGridParam(
88 {
89 url:"Users3.cfc?method=getUsers",
90 page:1
91 }
92 ).trigger("reloadGrid");//Reload grid trigger
93 }
94 });
95 });//End navigator manipulations
96
97 //This function will perform the search for us
98 function gridSearch()
99 {
100 //Get the values from the sarch form. Very simple using jquery.
101 var fn = $("#firstname_search").val();
102 var ln = $("#lastname_search").val();
103 var dn = $("#displayname_search").val();
104 var un = $("#username_search").val();
105 var uac = $("#useraccountingcode_search").val();
106 //We set the grid URL and pass in some extra params for the search.
107 $("#list").setGridParam(
108 {
109 url:"Users3.cfc?method=getUsers&FirstName="+fn+"&LastName="+ln+"&DisplayName="+dn+"&UserName="+un+"&UserAccountingCode="+uac,
110 page:1
111 }).trigger("reloadGrid");//Reload grid trigger
112 $("#filter").jqmHide();//Hide our search/filter window dialog
113 }
114
115 //This function is called after an add/edit happens. We just take the MSG from the response and display it in the toolbar.
116 //Note currently, since we reload the grid after the add/edit, the msg will only be visible for a short second or so
117 function commonSubmit(data,params)
118 {
119 var a = eval( "(" + data.responseText + ")" );//Convert returned string into JSON
120 $("#t_list").html(a.USERDATA.MSG);
121 resetForm();//Clear our search form
122 return true;
123 }
124
125 //Clear our search form
126 function resetForm()
127 {
128 document.FilterForm.reset();
129 }
130
131 </script>
1 <div id="filter" class="modalwin jqmWindow jqmID1" style="margin-left:5px;">
2 <div id="filterlist" class="modalwin jqmWindow jqmID3" style="top: 48px; left: 6px; z-index: 950; display: block;">
3 <div id="filterheader" class="modalhead">
4 <table width="100%">
5 <tbody>
6 <tr>
7 <td class="modaltext" style="cursor: move;">Search Users</td>
8 <td style="text-align: right;">
9 <a class="jqmClose" href="javascript:void(0);">
10 <img border="0" src="/Test/themes/steel/images/ico-close.gif"/>
11 </a>
12 </td>
13 </tr>
14 </tbody>
15 </table>
16 </div>
17 <div id="FitlerDiv" class="modalcontent">
18 <form id="FilterForm" class="FormGrid" name="FilterForm">
19 <table id="Filter_list" class="EditTable" cellspacing="0" border="0" cellpading="0">
20 <tbody>
21 <tr id="FormError" style="display: none;"></tr>
22 <tr id="tr_FirstName" class="FormData">
23 <td class="CaptionTD">
24 First Name:
25 </td>
26 <td class="DataTD">
27 <input id="firstname_search" class="FormElement" type="text" size="30" maxlength="50" name="firstname_search"/>
28 </td>
29 </tr>
30 <tr id="tr_LastName" class="FormData">
31 <td class="CaptionTD">
32 Last Name:
33 </td>
34 <td class="DataTD">
35 <input id="lastname_search" class="FormElement" type="text" size="30" maxlength="50" name="lastname_search"/>
36 </td>
37 </tr>
38 <tr id="tr_DisplayName" class="FormData">
39 <td class="CaptionTD">
40 Display Name:
41 </td>
42 <td class="DataTD">
43 <input id="displayname_search" class="FormElement" type="text" size="30" maxlength="50" name="displayname_search"/>
44 </td>
45 </tr>
46 <tr id="tr_UserName" class="FormData">
47 <td class="CaptionTD">
48 User Name:
49 </td>
50 <td class="DataTD">
51 <input id="username_search" class="FormElement" type="text" size="30" maxlength="50" name="username_search"/>
52 </td>
53 </tr>
54 <tr id="tr_UserAccountingCode" class="FormData">
55 <td class="CaptionTD">
56 User Accounting Code:
57 </td>
58 <td class="DataTD">
59 <input id="useraccountingcode_search" class="FormElement" type="text" size="30" maxlength="50" name="useraccountingcode_search"/>
60 </td>
61 </tr>
62 <tr id="Act_Buttons">
63 <td class="EditButton" colspan="2">
64 <input id="sData" class="EditButton" type="submit" value="Search" onClick="return gridSearch()"/>
65 <input id="cData" class="EditButton" type="reset" value="Clear"/>
66 </td>
67 </tr>
68 </tbody>
69 </table>
70 </form>
71 </div>
72 </div>
73 </div>
2 <div id="filterlist" class="modalwin jqmWindow jqmID3" style="top: 48px; left: 6px; z-index: 950; display: block;">
3 <div id="filterheader" class="modalhead">
4 <table width="100%">
5 <tbody>
6 <tr>
7 <td class="modaltext" style="cursor: move;">Search Users</td>
8 <td style="text-align: right;">
9 <a class="jqmClose" href="javascript:void(0);">
10 <img border="0" src="/Test/themes/steel/images/ico-close.gif"/>
11 </a>
12 </td>
13 </tr>
14 </tbody>
15 </table>
16 </div>
17 <div id="FitlerDiv" class="modalcontent">
18 <form id="FilterForm" class="FormGrid" name="FilterForm">
19 <table id="Filter_list" class="EditTable" cellspacing="0" border="0" cellpading="0">
20 <tbody>
21 <tr id="FormError" style="display: none;"></tr>
22 <tr id="tr_FirstName" class="FormData">
23 <td class="CaptionTD">
24 First Name:
25 </td>
26 <td class="DataTD">
27 <input id="firstname_search" class="FormElement" type="text" size="30" maxlength="50" name="firstname_search"/>
28 </td>
29 </tr>
30 <tr id="tr_LastName" class="FormData">
31 <td class="CaptionTD">
32 Last Name:
33 </td>
34 <td class="DataTD">
35 <input id="lastname_search" class="FormElement" type="text" size="30" maxlength="50" name="lastname_search"/>
36 </td>
37 </tr>
38 <tr id="tr_DisplayName" class="FormData">
39 <td class="CaptionTD">
40 Display Name:
41 </td>
42 <td class="DataTD">
43 <input id="displayname_search" class="FormElement" type="text" size="30" maxlength="50" name="displayname_search"/>
44 </td>
45 </tr>
46 <tr id="tr_UserName" class="FormData">
47 <td class="CaptionTD">
48 User Name:
49 </td>
50 <td class="DataTD">
51 <input id="username_search" class="FormElement" type="text" size="30" maxlength="50" name="username_search"/>
52 </td>
53 </tr>
54 <tr id="tr_UserAccountingCode" class="FormData">
55 <td class="CaptionTD">
56 User Accounting Code:
57 </td>
58 <td class="DataTD">
59 <input id="useraccountingcode_search" class="FormElement" type="text" size="30" maxlength="50" name="useraccountingcode_search"/>
60 </td>
61 </tr>
62 <tr id="Act_Buttons">
63 <td class="EditButton" colspan="2">
64 <input id="sData" class="EditButton" type="submit" value="Search" onClick="return gridSearch()"/>
65 <input id="cData" class="EditButton" type="reset" value="Clear"/>
66 </td>
67 </tr>
68 </tbody>
69 </table>
70 </form>
71 </div>
72 </div>
73 </div>
1 <cfcomponent output="false">
2 <cffunction name="getUserData" access="private" returntype="query" hint="function that returns all users from database sorted">
3 <cfargument name="sidx" required="no" default="" hint="Sort Column">
4 <cfargument name="sord" required="no" default="ASC" hint="Sort Order">
5 <cfset var selUsers = "">
6
7 <cfquery name="selUsers" datasource="RIADemo" result="res">
8 SELECT
9 ID, FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone
10 FROM
11 Users
12 ORDER BY #sidx# #sord#
13 </cfquery>
14
15 <cfreturn selUsers>
16 </cffunction>
17
18 <cffunction name="getUsers" access="remote" returnformat="json">
19 <cfargument name="ID" required="no" default="0" hint="Search parameter">
20 <cfargument name="FirstName" required="no" default="" hint="Search parameter">
21 <cfargument name="LastName" required="no" default="" hint="Search parameter">
22 <cfargument name="DisplayName" required="no" default="" hint="Search parameter">
23 <cfargument name="UserName" required="no" default="" hint="Search parameter">
24 <cfargument name="UserAccountingCode" required="no" default="" hint="Search parameter">
25 <cfargument name="page" required="no" default="1" hint="Page user is on">
26 <cfargument name="rows" required="no" default="10" hint="Number of Rows to display per page">
27 <cfargument name="sidx" required="no" default="" hint="Sort Column">
28 <cfargument name="sord" required="no" default="ASC" hint="Sort Order">
29 <cfset var strMsg = "">
30 <cfset var strMsgType = "Success">
31 <cfset var strReturn = "">
32 <cfset var selUsers = "">
33 <cfset var blnSearch = true>
34
35 <cftry>
36 <cfquery name="selUsers" datasource="RIADemo" result="res">
37 SELECT
38 ID, FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone
39 FROM
40 Users
41 WHERE
42 <!--- Always true, hopefully for all Databases --->
43 1=1
44 <cfif Val(Arguments.ID) GT 0>
45 AND ID = #Val(Arguments.ID)#
46 </cfif>
47 <cfif Arguments.FirstName NEQ "">
48 AND FirstName LIKE '%#Arguments.FirstName#%'
49 </cfif>
50 <cfif Arguments.LastName NEQ "">
51 AND LastName LIKE '%#Arguments.LastName#%'
52 </cfif>
53 <cfif Arguments.DisplayName NEQ "">
54 AND DisplayName LIKE '%#Arguments.DisplayName#%'
55 </cfif>
56 <cfif Arguments.UserName NEQ "">
57 AND UserName LIKE '%#Arguments.UserName#%'
58 </cfif>
59 <cfif Arguments.UserAccountingCode NEQ "">
60 AND UserAccountingCode LIKE '%#Arguments.UserAccountingCode#%'
61 </cfif>
62 ORDER BY #sidx# #sord#
63 </cfquery>
64 <cfcatch type="any">
65 <!--- Send back error message if search produced bad result --->
66 <cfset blnSearch = false>
67 <cfset strMsgType = "Error">
68 <cfset strMsg = "There was an issue with the Search. An Error Report has been submitted to Support.">
69 <cfset selUsers = getUserData(sidx=sidx,sord=sord)>
70 </cfcatch>
71 </cftry>
72
73 <!--- Search was good, but we got no records. So we return that message back --->
74 <cfif blnSearch AND selUsers.RecordCount EQ 0>
75 <cfset strMsgType = "Error">
76 <cfset strMsg = "No Records found matching search criteria">
77 <cfelse>
78 <!--- All good, send back total records message --->
79 <cfset strMsg = "#selUsers.RecordCount# Records Retreived">
80 </cfif>
81
82 <!--- User data ftw --->
83 <cfset userdata = {type="#strMsgType#",msg="#strMsg#"}>
84
85 <!--- Get JSON Format for the grid from a private function --->
86 <cfset strReturn = getGridJSONFormat(page=Arguments.page,rows=Arguments.rows,qryRecords=selUsers,userdata=userdata)>
87
88 <cfreturn strReturn>
89 </cffunction>
90
91 <cffunction name="addeditUser" access="remote" hint="Add or Edit User Data" returnformat="json">
92 <cfargument name="FirstName" required="yes" hint="Field that was Added or editted">
93 <cfargument name="LastName" required="yes" hint="Field that was Added or editted">
94 <cfargument name="DisplayName" required="yes" hint="Field that was Added or editted">
95 <cfargument name="UserName" required="yes" hint="Field that was Added or editted">
96 <cfargument name="UserAccountingCode" required="yes" hint="Field that was Added or editted">
97 <cfargument name="Phone" required="yes" hint="Field that was Added or editted">
98 <cfargument name="ID" required="yes" hint="the User that was Added or editted">
99 <cfargument name="oper" required="no" default="edit" hint="Whether this is an add or edit">
100 <cfset var strMsg = "">
101 <cfset var strMsgType = "Success">
102 <cfset var userdata = "">
103
104 <cfif oper EQ "edit">
105 <cfset strMsg = "User Editted">
106 <!--- Take the data, update your record. Simple. --->
107 <cftry>
108 <cfquery name="editUser" datasource="RIADemo">
109 UPDATE
110 Users
111 SET
112 FirstName = '#Arguments.FirstName#',
113 LastName = '#Arguments.LastName#',
114 DisplayName = '#Arguments.DisplayName#',
115 UserName = '#Arguments.UserName#',
116 UserAccountingCode = '#Arguments.UserAccountingCode#',
117 Phone = '#Arguments.Phone#'
118 WHERE
119 ID = #Val(Arguments.ID)#
120 </cfquery>
121 <cfcatch type="any">
122 <!--- Error, return message --->
123 <cfset strMsgType = "Error">
124 <cfset strMsg = "Error occured when Editting User. An Error report has been submitted to support.">
125 </cfcatch>
126 </cftry>
127 <cfelse>
128 <cfset strMsg = "User Added">
129 <cftry>
130 <cfquery name="addUser" datasource="RIADemo">
131 INSERT INTO USERS(FirstName,LastName,DisplayName,UserName,UserAccountingCode,Phone)
132 VALUES ('#Arguments.FirstName#',
133 '#Arguments.LastName#',
134 '#Arguments.DisplayName#',
135 '#Arguments.UserName#',
136 '#Arguments.UserAccountingCode#',
137 '#Arguments.Phone#')
138 </cfquery>
139 <cfcatch>
140 <!--- Error, return message --->
141 <cfset strMsgType = "Error">
142 <cfset strMsg = "Error occured when Adding User. An Error report has been submitted to support.">
143 </cfcatch>
144 </cftry>
145 </cfif>
146 <!--- We just need to pass back some user data for display purposes --->
147 <cfset userdata = {type="#strMsgType#",msg="#strMsg#"}>
148 <cfset strReturn = {userdata=#userdata#}>
149 <cfreturn strReturn>
150 </cffunction>
151
152 <cffunction name="delUser" access="remote" hint="Delete Selected User" returnformat="json">
153 <cfargument name="id" required="yes" hint="id to delete">
154 <cfset var strMsg = "">
155 <cfset var strMsgType = "Success">
156 <cfset var userdata = "">
157 <cfset var delUser = "">
158
159 <cftry>
160 <cfset strMsg = "Deleted User">
161 <cfquery name="delUser" datasource="RIADemo">
162 DELETE FROM Users WHERE ID = #Val(Arguments.id)#
163 </cfquery>
164 <cfcatch>
165 <!--- Error, return message --->
166 <cfset strMsgType = "Error">
167 <cfset strMsg = "Error occured when Deleting User. An Error report has been submitted to support.">
168 </cfcatch>
169 </cftry>
170
171 <cfset userdata = {type="#strMsgType#",msg="#strMsg#"}>
172 <cfset strReturn = {userdata=#userdata#}>
173
174 <cfreturn strReturn>
175 </cffunction>
176
177 <cffunction name="getGridJSONFormat" access="private" hint="get JSON Format for query for grid" retunformat="json">
178 <cfargument name="page" required="no" default="1" hint="Page user is on">
179 <cfargument name="rows" required="no" default="10" hint="Number of Rows to display per page">
180 <cfargument name="qryRecords" required="yes" hint="Query to convert to JSON format">
181 <cfargument name="userdata" required="no" default="">
182 <cfset var arrUsers = ArrayNew(1)>
183 <cfset var start = 0>
184 <cfset var end = 0>
185 <cfset var totalPages = 0>
186 <cfset var i = 1>
187 <cfset strReturn = "">
188
189 <!--- Calculate the Start Position for the loop query.
190 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.
191 If you go to page 2, you start at (2-)1*4+1 = 5 --->
192 <cfset start = ((arguments.page-1)*arguments.rows)+1>
193
194 <!--- Calculate the end row for the query. So on the first page you go from row 1 to row 4. --->
195 <cfset end = (start-1) + arguments.rows>
196
197 <cfloop query="qryRecords" startrow="#start#" endrow="#end#">
198 <!--- Array that will be passed back needed by jqGrid JSON implementation --->
199 <cfset arrUsers[i] = [#ID#,#FirstName#,#LastName#,#DisplayName#,#UserName#,#UserAccountingCode#,#Phone#]>
200 <cfset i = i + 1>
201 </cfloop>
202
203 <!--- Calculate the Total Number of Pages for your records. --->
204 <cfset totalPages = Ceiling(qryRecords.recordcount/arguments.rows)>
205
206 <!--- The JSON return --->
207 <cfset strReturn = {total=#totalPages#,page=#Arguments.page#,records=#qryRecords.recordcount#,rows=arrUsers,userdata=#userdata#}>
208 <cfreturn strReturn>
209 </cffunction>
210 </cfcomponent>
2 <cffunction name="getUserData" access="private" returntype="query" hint="function that returns all users from database sorted">
3 <cfargument name="sidx" required="no" default="" hint="Sort Column">
4 <cfargument name="sord" required="no" default="ASC" hint="Sort Order">
5 <cfset var selUsers = "">
6
7 <cfquery name="selUsers" datasource="RIADemo" result="res">
8 SELECT
9 ID, FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone
10 FROM
11 Users
12 ORDER BY #sidx# #sord#
13 </cfquery>
14
15 <cfreturn selUsers>
16 </cffunction>
17
18 <cffunction name="getUsers" access="remote" returnformat="json">
19 <cfargument name="ID" required="no" default="0" hint="Search parameter">
20 <cfargument name="FirstName" required="no" default="" hint="Search parameter">
21 <cfargument name="LastName" required="no" default="" hint="Search parameter">
22 <cfargument name="DisplayName" required="no" default="" hint="Search parameter">
23 <cfargument name="UserName" required="no" default="" hint="Search parameter">
24 <cfargument name="UserAccountingCode" required="no" default="" hint="Search parameter">
25 <cfargument name="page" required="no" default="1" hint="Page user is on">
26 <cfargument name="rows" required="no" default="10" hint="Number of Rows to display per page">
27 <cfargument name="sidx" required="no" default="" hint="Sort Column">
28 <cfargument name="sord" required="no" default="ASC" hint="Sort Order">
29 <cfset var strMsg = "">
30 <cfset var strMsgType = "Success">
31 <cfset var strReturn = "">
32 <cfset var selUsers = "">
33 <cfset var blnSearch = true>
34
35 <cftry>
36 <cfquery name="selUsers" datasource="RIADemo" result="res">
37 SELECT
38 ID, FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone
39 FROM
40 Users
41 WHERE
42 <!--- Always true, hopefully for all Databases --->
43 1=1
44 <cfif Val(Arguments.ID) GT 0>
45 AND ID = #Val(Arguments.ID)#
46 </cfif>
47 <cfif Arguments.FirstName NEQ "">
48 AND FirstName LIKE '%#Arguments.FirstName#%'
49 </cfif>
50 <cfif Arguments.LastName NEQ "">
51 AND LastName LIKE '%#Arguments.LastName#%'
52 </cfif>
53 <cfif Arguments.DisplayName NEQ "">
54 AND DisplayName LIKE '%#Arguments.DisplayName#%'
55 </cfif>
56 <cfif Arguments.UserName NEQ "">
57 AND UserName LIKE '%#Arguments.UserName#%'
58 </cfif>
59 <cfif Arguments.UserAccountingCode NEQ "">
60 AND UserAccountingCode LIKE '%#Arguments.UserAccountingCode#%'
61 </cfif>
62 ORDER BY #sidx# #sord#
63 </cfquery>
64 <cfcatch type="any">
65 <!--- Send back error message if search produced bad result --->
66 <cfset blnSearch = false>
67 <cfset strMsgType = "Error">
68 <cfset strMsg = "There was an issue with the Search. An Error Report has been submitted to Support.">
69 <cfset selUsers = getUserData(sidx=sidx,sord=sord)>
70 </cfcatch>
71 </cftry>
72
73 <!--- Search was good, but we got no records. So we return that message back --->
74 <cfif blnSearch AND selUsers.RecordCount EQ 0>
75 <cfset strMsgType = "Error">
76 <cfset strMsg = "No Records found matching search criteria">
77 <cfelse>
78 <!--- All good, send back total records message --->
79 <cfset strMsg = "#selUsers.RecordCount# Records Retreived">
80 </cfif>
81
82 <!--- User data ftw --->
83 <cfset userdata = {type="#strMsgType#",msg="#strMsg#"}>
84
85 <!--- Get JSON Format for the grid from a private function --->
86 <cfset strReturn = getGridJSONFormat(page=Arguments.page,rows=Arguments.rows,qryRecords=selUsers,userdata=userdata)>
87
88 <cfreturn strReturn>
89 </cffunction>
90
91 <cffunction name="addeditUser" access="remote" hint="Add or Edit User Data" returnformat="json">
92 <cfargument name="FirstName" required="yes" hint="Field that was Added or editted">
93 <cfargument name="LastName" required="yes" hint="Field that was Added or editted">
94 <cfargument name="DisplayName" required="yes" hint="Field that was Added or editted">
95 <cfargument name="UserName" required="yes" hint="Field that was Added or editted">
96 <cfargument name="UserAccountingCode" required="yes" hint="Field that was Added or editted">
97 <cfargument name="Phone" required="yes" hint="Field that was Added or editted">
98 <cfargument name="ID" required="yes" hint="the User that was Added or editted">
99 <cfargument name="oper" required="no" default="edit" hint="Whether this is an add or edit">
100 <cfset var strMsg = "">
101 <cfset var strMsgType = "Success">
102 <cfset var userdata = "">
103
104 <cfif oper EQ "edit">
105 <cfset strMsg = "User Editted">
106 <!--- Take the data, update your record. Simple. --->
107 <cftry>
108 <cfquery name="editUser" datasource="RIADemo">
109 UPDATE
110 Users
111 SET
112 FirstName = '#Arguments.FirstName#',
113 LastName = '#Arguments.LastName#',
114 DisplayName = '#Arguments.DisplayName#',
115 UserName = '#Arguments.UserName#',
116 UserAccountingCode = '#Arguments.UserAccountingCode#',
117 Phone = '#Arguments.Phone#'
118 WHERE
119 ID = #Val(Arguments.ID)#
120 </cfquery>
121 <cfcatch type="any">
122 <!--- Error, return message --->
123 <cfset strMsgType = "Error">
124 <cfset strMsg = "Error occured when Editting User. An Error report has been submitted to support.">
125 </cfcatch>
126 </cftry>
127 <cfelse>
128 <cfset strMsg = "User Added">
129 <cftry>
130 <cfquery name="addUser" datasource="RIADemo">
131 INSERT INTO USERS(FirstName,LastName,DisplayName,UserName,UserAccountingCode,Phone)
132 VALUES ('#Arguments.FirstName#',
133 '#Arguments.LastName#',
134 '#Arguments.DisplayName#',
135 '#Arguments.UserName#',
136 '#Arguments.UserAccountingCode#',
137 '#Arguments.Phone#')
138 </cfquery>
139 <cfcatch>
140 <!--- Error, return message --->
141 <cfset strMsgType = "Error">
142 <cfset strMsg = "Error occured when Adding User. An Error report has been submitted to support.">
143 </cfcatch>
144 </cftry>
145 </cfif>
146 <!--- We just need to pass back some user data for display purposes --->
147 <cfset userdata = {type="#strMsgType#",msg="#strMsg#"}>
148 <cfset strReturn = {userdata=#userdata#}>
149 <cfreturn strReturn>
150 </cffunction>
151
152 <cffunction name="delUser" access="remote" hint="Delete Selected User" returnformat="json">
153 <cfargument name="id" required="yes" hint="id to delete">
154 <cfset var strMsg = "">
155 <cfset var strMsgType = "Success">
156 <cfset var userdata = "">
157 <cfset var delUser = "">
158
159 <cftry>
160 <cfset strMsg = "Deleted User">
161 <cfquery name="delUser" datasource="RIADemo">
162 DELETE FROM Users WHERE ID = #Val(Arguments.id)#
163 </cfquery>
164 <cfcatch>
165 <!--- Error, return message --->
166 <cfset strMsgType = "Error">
167 <cfset strMsg = "Error occured when Deleting User. An Error report has been submitted to support.">
168 </cfcatch>
169 </cftry>
170
171 <cfset userdata = {type="#strMsgType#",msg="#strMsg#"}>
172 <cfset strReturn = {userdata=#userdata#}>
173
174 <cfreturn strReturn>
175 </cffunction>
176
177 <cffunction name="getGridJSONFormat" access="private" hint="get JSON Format for query for grid" retunformat="json">
178 <cfargument name="page" required="no" default="1" hint="Page user is on">
179 <cfargument name="rows" required="no" default="10" hint="Number of Rows to display per page">
180 <cfargument name="qryRecords" required="yes" hint="Query to convert to JSON format">
181 <cfargument name="userdata" required="no" default="">
182 <cfset var arrUsers = ArrayNew(1)>
183 <cfset var start = 0>
184 <cfset var end = 0>
185 <cfset var totalPages = 0>
186 <cfset var i = 1>
187 <cfset strReturn = "">
188
189 <!--- Calculate the Start Position for the loop query.
190 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.
191 If you go to page 2, you start at (2-)1*4+1 = 5 --->
192 <cfset start = ((arguments.page-1)*arguments.rows)+1>
193
194 <!--- Calculate the end row for the query. So on the first page you go from row 1 to row 4. --->
195 <cfset end = (start-1) + arguments.rows>
196
197 <cfloop query="qryRecords" startrow="#start#" endrow="#end#">
198 <!--- Array that will be passed back needed by jqGrid JSON implementation --->
199 <cfset arrUsers[i] = [#ID#,#FirstName#,#LastName#,#DisplayName#,#UserName#,#UserAccountingCode#,#Phone#]>
200 <cfset i = i + 1>
201 </cfloop>
202
203 <!--- Calculate the Total Number of Pages for your records. --->
204 <cfset totalPages = Ceiling(qryRecords.recordcount/arguments.rows)>
205
206 <!--- The JSON return --->
207 <cfset strReturn = {total=#totalPages#,page=#Arguments.page#,records=#qryRecords.recordcount#,rows=arrUsers,userdata=#userdata#}>
208 <cfreturn strReturn>
209 </cffunction>
210 </cfcomponent>
Helpful Links:
jqGrid Documentation
jqGrid Examples (Excellent resource, up to date)
jqGrid Forums
One problem. My key field is userID not id. I could not get the edit or delete functions to work initially. My workaround was to set a key=true colModel property with the userid colModel properties. Then I changed the cfargument attributes in the cfc addeditUser and delUser methods to id instead of userID. While this works, when I view the response in firebug I see this error message: The USERID parameter to the delUser function is required but was not passed in.
I see a similar error message when I run your demo even though it is clearly working. Any reason to be concerned?
Thanks again.
Very nice write up and thanks again for saving me some serious time.
Fantastic tutorial/example! Thanks!
When I debugging this error in VS highlight everything starting from Ln.19
which is var selectedRow = 0; till Ln.71 which is closing " ); "
I have my js in folder called JavaScript so my includes for all js look like this:
<script language="JavaScript" src="JavaScript/jquery.jqGrid.js" type="text/javascript"></script>
I also change in jqGrid.js var pathtojsfiles from js to
var pathtojsfiles = "JavaScript/";
Which bring another question? In the same jqGrids.js page we have reference to the "var combinedIncludeURL = "combine.php?type=javascript&files=" and all the modules includes js files (like grid.base.js") - where this files coming from. I did not see them include in your user3. cfm. page.
You would probably need CF8 for the JSON support.
This might be what you want: http://www.coldfusion-ria.com/Blog/index.cfm/2009/...
The demo no longer works (it got overwritten by this post), but the code is there.
How does this play together with CF session handling?
First in the url statement which calls a method in my cfc: The session.bizorg_objid identifies a specific user so I only return their data.
url:'Users.cfc?method=GetAllCases&myacct=<cfoutput>#session.bizorg_objid#</cfoutput>', //CFC that will return the users
This is the cfc method being called: I only posted the parameter section as the actual code is what Kumar has posted with the SQL modified to handle my passed in session var.
<cffunction name="GetAllCases" access="remote" returnformat="json">
<cfargument name="page" required="no" default="1" hint="Page user is on">
<cfargument name="rows" required="no" default="15" 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">
<cfargument name="myacct" required="yes" hint="Biz Org to get ">
<cfargument name="listtyp" required="no" default="" hint="Type of list to bring back ">
<cfargument name="nd" required="no" default="0">
<cfargument name="_search" required="no" default="false" hint="Whether search is performed by user on data grid">
<cfargument name="searchField" required="no" default="" hint="Field to perform Search on">
<cfargument name="searchOper" required="no" default="" hint="Search Operator Short Form">
<cfargument name="searchString" required="no" default="" hint="Search Text">
Other grid params can also use session vars: Each of my users have a profile which allows them control of certain aspects of the grid. ie. the number of lines (or rows) in the grid and even which columns they want to have in their grid.
This is a direct use the session var to set a grid param:
rowNum: <cfoutput>#session.maxlines#</cfoutput>, //Number of records we want to show per page
Or within a function call:
//Things to do when grid is finished loading
loadComplete:function(){
// Hide the site name column per user settings
var hidesite = <cfoutput>#session.SiteDD#</cfoutput>;
if(hidesite == 1){
$("#slist").hideCol("x_site_name");
}
For the above code to work you need to declare the column name in your column properties section, like so.
{name:'x_site_name',index:'x_site_name', align:"left",sorttype:"text"},
And remember that number of "{name" fields must be equal the number of fields in the "colNames:[" array. and that the returned data must contain all of the possilbe data columns, even hidden columns.
Another example is using it in the Formatter parameter:
{name:"x_creation_dt",index:"x_creation_dt", align:"left",formatter:'date', formatoptions:{srcformat: 'd/m/Y H:i:s',newformat: '<cfoutput>#session.myGridDateFormat#</cfoutput>'}, width:90, resizable:false},
--------
I hope this helps my fellow CF'ers.
Jim
Excellent work on the jqGrid. I tried your example and it works perfectly in firefox; but not in IE. I am using IE7 and it does not display the grid at all.
Any suggestions would be helpful.
Have you just tried a simple grid that only displays data without any add/edit/search funtionalities? Does that work in IE?
I have a number of grids based on Kumar's examples that work in IE7 and FF.
I recommend using firebug in FF to debug the code.
Here is my sample code for just the grid.
<html>
<head>
<link rel="stylesheet" type="text/css" media="screen" href="css/grid.css" />
<link rel="stylesheet" type="text/css" media="screen" href="css/form.import.css" />
<link rel="stylesheet" type="text/css" media="screen" href="css/jqModal.css" />
<script src="js/jquery-1.3.2.min.js" type="text/javascript"></script>
<script src="js/jquery.jqGrid.js" type="text/javascript"></script>
<script src="js/jqModal.js" type="text/javascript"></script>
<script src="js/jqDnR.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function(){
$("#list").jqGrid({
url:'InvoiceMgr.cfc?method=getInvoices',
datatype: 'json',
colNames:['Inv No','Date', 'Amount','Tax','Total','Notes'],
colModel :[
{name:'id',index:'id', width:60, sorttype:"int"},
{name:'invdate',index:'invdate', width:90, sorttype:"date"},
{name:'amount',index:'amount', width:80, align:"right",sorttype:"float"},
{name:'tax',index:'tax', width:80, align:"right",sorttype:"float"},
{name:'total',index:'total', width:80,align:"right",sorttype:"float"},
{name:'note',index:'note', width:150, sortable:false}],
pager: $('#pager'),
rowNum:10,
rowList:[10,20,30],
sortorder: "desc",
viewrecords: true,
imgpath: '/images',
caption: 'Invoices',
jsonReader: {
root: "ROWS",
page: "PAGE",
total: "TOTAL",
cell: "",
id: "id"
}}
);
});
</script>
</head>
<body>
<table id="list" class="scroll"> <div id="pager" class="scroll" style="text-align:center;"> </body>
</html>
and here is the cfc
<cfcomponent displayname="InvoiceMgr" output="false">
<cffunction name="getInvoices" access="remote" output="false" returnformat="json">
<cfset var invoices = ArrayNew(1) />
<cfset var q=querynew("invid,invdate,amount,tax,total,note")>
<Cfset queryaddrow(q,1)>
<cfset querysetcell(q,"invid",1)>
<cfset querysetcell(q,"invdate",1)>
<cfset querysetcell(q,"amount",1)>
<cfset querysetcell(q,"tax",1)>
<cfset querysetcell(q,"total",1)>
<cfset querysetcell(q,"note",1)>
<cfloop query="q">
<cfset invoices[currentrow] = [#invid#, #invdate#, #amount#, #tax#, #total#, #note#]>
</cfloop>
<cfset str = {total=1, page=1, records=#q.recordcount#, rows=invoices}>
<cfreturn str />
</cffunction>
</cfcomponent>
Thanks guys for your time.
I just copied your code and get a fallowing error in FF:
$ is not defined
http://localhost/jqGrid/index.cfm
Line 15
this is my code till line 16
<html>
<head>
<link rel="stylesheet" type="text/css" media="screen" href="themes/steel/form.import.css" />
<link rel="stylesheet" type="text/css" media="screen" href="themes/steel/form.import.css" />
<link rel="stylesheet" type="text/css" media="screen" href="themes/steel/jqModal.css" />
<link rel="stylesheet" type="text/css" href="http://yui.yahooapis.com/2.6.0/build/fonts/fonts.c...;
<script src="jquery.js" type="text/javascript"></script>
<script src="jquery.jqGrid.js" type="text/javascript"></script> <script src="js/jqModal.js" type="text/javascript"></script>
<script src="js/jqDnR.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function(){
This code works fine for me in all the major browsers.
Please make sure you have all your JS files in proper locations.
I check all my files and they in a write place.
Unfortunately I do not have any luck with jqGrid, although I need it make working ASAP.
I am almost 2 month fighting with this code and still in dump.
Even a simple version from (lesson1) is not working for me It is not bringing any data.
Any chance I can email you my code and you can check it out?
Thanks
Michael
I don't think there is anything wrong with the code because if you copy-paste the above code it should work fine.
If you are getting error "$ is not defined", this means it cannot find the JS files. For simplicity you can keep all your JS files in the current folder where your cfm file is.
Also,make sure you download the latest jquery libraries.
Hope this helps.
Thanks for the very nice article with painstaking and clear comments. Great and very appreciable.
Quick question, in the comment:
"//Here we use the navGrid to display the Search button with label Filter, and disable the add/edit/delete buttons."
You are referring to disable of add/edit/disable, I believe it is enable?! Please correct me if I am wrong.
Thanks again.
-Praveen
You are right, that should be enable the add/edit/delete buttons.
All your examples have been working great..
I was trying to include a select box to edit one of the fields.
For edittype:select, is there a way to populate the drop-down dynamically from a cfc/cfm.
In the documentation it says we can use 'surl' to specify a url for the select box; but i couldn't make it to work.
Any suggestions would be greatly appreciated.
Can you please suggest how to change the font-size within the "Add/Edit" dialog-box? Basically how to format the contents within the dialog box, when one clicks the Add/Edit button from the navigation pane ?
Also, what and where is the "#t_list" element and how it is defined ? When I try to add the below statement:
//Set toolbar text color to red
$("#t_list").css("color","red");
after adding the below jQGrid option,
toolbar: [true, "top"]
the toolbar does not change in color to red.....Am I missing anything?
Thanks for your time. Looking forward more of your other tutorials with jQGrid!
-Praveen
Finally I am able to run you example from tutorial 4.
Only one problem I am facing now, if I am moving Users3.cfc to the separate folder (com/users3.cfc.). I am obviously change all references to the cfc in the users3.cfm on com/Users3.cfc
Everything is working, except when I am trying to add/edit user it showing me Red message Processing& and stop here (i.e. do not closed edit form and refreshed grid). If I close manually add/edit form and refresh all updates is there. I do not have this when I am deleting the user.
Any suggestions will be very much appreciated.
Thanks
Michael
The way the pages and et al calculation, has a bug. To see what I am referring to, do the following on the demo.:
(1) Goto the ">>", i.e. the last page
(2) Change the number of records to be viewed to "50"
and the result is obvious.
To resolve this, please refer to the jQGrid demo's PHP code.
-Praveen
I would suggest you install firebug and see the debug messages. There could be a JS error.
"One problem. My key field is userID not id. I could not get the edit or delete functions to work initially. My workaround was to set a key=true colModel property with the userid colModel properties. Then I changed the cfargument attributes in the cfc addeditUser and delUser methods to id instead of userID. While this works, when I view the response in firebug I see this error message: The USERID parameter to the delUser function is required but was not passed in."
If my key column is named anything other than ID I can't seem to get the add/edit functionality to work. I have tried Harold's workaround but to no avail.
Does anyone have any experiences/success with this?
Cheers,
Michael
i have try jquery it is really good and ligth weight grid to display data
but i found some issue i want to show appropriate user define message to my end users so how can i do this,
thanks in advance
dinesh
http://www.coldfusion-ria.com/Blog/index.cfm/2009/...
If you are still having issues, try asking on the jqGrid forums.
@Praveen, the t_list element I beleive is generated by the jqGrid for the toolbar, we don't explictly define it. You will have to check the jqGrid code, but also, jqGrid comes with its own CSS, so you can modify the CSS to achieve a different look.
@Dinesh,
jqGrid can read userdata in the following format:
USERDATA : {type:value,msg:text}
So, you will have to pass the data back to the grid in that format, then you can read the data and display it to the user. I did it for one of my posts,
http://www.coldfusion-ria.com/Blog/index.cfm/2009/...
http://www.secondpersonplural.ca/jqgriddocs/_2eb0f...
contains the colModel properties definition
I've tried to run this sample code several times, I keep getting the same error of '$("list").jqGrid is not a function'. I don't get it as it is defined and set in the script source.
Any help?
thanks in advance
It looks like there is an error in your CFC when it tries to process the add/edit/delete data that is sent to it.
I suggest using Firebug to see what is returned back from the CFC, or try to use the inbuilt Ajax debugger with ColdFusion. An alternative is to imitate the CFC function call in a test file to unit test your functions.
i'm only modifying the query odbc and the name of the table and it doens't work ¿¿?? Colud you, please, upload/email me a zip file whit the 'Full Source Code', please: only copy, paste and change the query...
sorry...
thnks
I would also suggest that getting the full source code to only replace the table names and queries for your project doesn't accomplish the object of learning jQuery/jqGrid with ColdFusion. What I have here I don't think would work for everyone, especially looking at the custom search screen implementation.
I am using the above grid and is working great. The only issue is when you laod the grid first time, it is showing the "Loading..." text at the top. It is staying there for ever until I reload it by clicking on sort or any other button like refresh...How to hide that Loading... div? I couldnt figure out that.
Thanks
Suds
First off thanks for the Tutorial works and looks great...
I have a question on your series iam not sure if your are done with this one or not. I like how you use a custom form for the search feature. Ive been trying to use a similar custom form for my add and edit features. i ve been looking on the web and cant seem to find anything that works. Do u have any suggestions on how to go about this.
Thanks
I would also recommend you check out the latest jqGrid release, as it has good inbuilt add/edit capabilities.
http://www.trirand.com/jqgridwiki/doku.php?id=wiki..., I was able to customize
the form pretty easily once I saw this.
Just set the edittype parameter in the colModel, the only weird thing is selects.
I haven't found a way to dynamically generate the option tags .
I see that the jqModal window doesn't show in IE8. I am having the same issue with my implementation. Any idea how to resolve this?
Jerry
Have you upgraded the JQuery Library to v.1.4? There are a tremendous number of IE centric fixes in this release
Thanks for the 'spot on' suggestion. I had isolated the issue to a browser detection issue. IE8 was being detected as IE6 causing it to execute code in jqModal that threw an exception. Apparently, jQuery 1.4.2 has resolved this issue. Life is good, again.
I really like this approach to doing the search form for jqGrid. It is a more straightforward approach than using the jqGrid built in search and the usability is much better.
Thanks for your help.
Jerry
Most of the problems that I had setting this up, were in the colModel, it has to line up with what's being returned in the json string from the ColdFusion method.
Use Firebug to see what's coming back from the CF method, and code the colModel accordingly
Thanks for your help. It is still not working in CF MX7. Here is a CFDUMP of what getUsers function returned:
struct
PAGE 1
RECORDS 5
ROWS array
1 struct
1 1
2 John
3 Doe1
4 John Doe1
5 jd1
6 10001
7 999-999-9999
2 struct
1 2
2 John
3 Doe2
4 John Doe2
5 jd2
6 10002
7 999-999-9999
3 struct
1 3
2 John
3 Doe3
4 John Doe3
5 jd3
6 10003
7 999-999-9999
4 struct
1 4
2 John
3 Doe4
4 John Doe4
5 jd4
6 10004
7 999-999-9999
5 struct
1 5
2 John
3 Doe5
4 John Doe5
5 jd5
6 10005
7 999-999-9999
TOTAL 1
USERDATA struct
MSG 5 Records Retreived
TYPE Success
But no grid was pop up. Is this a MX7 issue?
Thanks,
Mike
You've got returntype="json" on the end of the opening cffunction tag correct?
Yes I have returntype="json" on the end of the opening cffunction tag. I didn't change the source code, except for datasource name. I am using tutorial 4.
Thanks,
Mike