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.

This implementation doesn't exactly follow the previous posts. For this grid I am using:

  1. Inbuilt Form based Add, Edit and Delete functionality
  2. Custom Built Search Dialog Screen
  3. Custom Refresh Action
  4. 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,

view plain print about
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,

view plain print about
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
98function 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
117function 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
126function resetForm()
127{
128    document.FilterForm.reset();
129}
130
131</script>

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:

view plain print about
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>

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.

view plain print about
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>

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.

Helpful Links:
jqGrid Documentation
jqGrid Examples (Excellent resource, up to date)
jqGrid Forums

Feel free to contact me if you have any questions and I will try to help you out.