Replacing CFGrid with DataTables, Part 3: Editable Grids 12


Bet you thought I forgot about this series, eh? 🙂 Got caught up dealing with the SOAP stuff – that was simple to build with Zend but ended up not working with the legacy system we were working with so had to redo it to break out of Zend all together to get it working.  Was two weeks of headache though before I finally got it all figured out.

Anyway, time to switch back to ColdFusion for a bit and finish this last entry in replacing CFGrid with DataTables!  Alas during the intervening time, things changed in the DataTables front with the release of v1.10 (see upgrade note for major differences!).  So what was a fairly straight apples-to-apples comparison (CFGRID w/ certain flags set vs DataTables + 1 extension) isn't as easy to do.  Still, I wanted to at least cover a really basic way of doing some functions so time to pull out the custom coding, yeah!  Which is one beauty of using it with DataTables and jQuery, so much easier to customize!

For these examples I'm using the video game tables from part 1, but updated to use the same sort of Ajax loading as part 2. For simplicity's sake, this example just deals with a single table of data rather than a properly normalized set of tables. I'm also using just the core default features of CFGrid, without doing any additional JS to augment functionality.

So first our view code. For visual niceness, I included Pure CSS and some really basic CSS stylings because plain HTML tends to be ugly :-P.  Oh and in both examples, I'm utilizing CDNs for jQuery, Pure, and DataTables rather than local hosted and HTML 5 rather than XHTML 4.01.

<!doctype html>
<html>
<head>
	<meta charset="utf-8">
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<meta name="description" content="Blahdy blah blah" />
	<meta name="robots" content="index, follow" />
	<title>CF Grid - Editing In Grid Example</title>
	<link rel="stylesheet" href="http://yui.yahooapis.com/pure/0.5.0/pure-min.css">
	<link href="/cfgridtodt.css" rel="stylesheet" type="text/css">
	<!--[if lte IE 8]>
		<link rel="stylesheet" href="https://yui-s.yahooapis.com/pure/0.5.0/grids-responsive-old-ie-min.css">
	<![endif]-->
	<!--[if gt IE 8]><!-->
		<link rel="stylesheet" href="https://yui-s.yahooapis.com/pure/0.5.0/grids-responsive-min.css">
	<!--<![endif]-->
</head>

<body>
	<div id="layout">
		<h1>CF Grid - Editing In Grid Example</h1>
		
		<cfform>
			<cfgrid 
			   name="VideoGameList"
			   align="Top"
			   autoWidth="yes"
			   bgColor="FFF"
			   colHeaderBold="yes"
			   format="html"
			   gridDataAlign="left"
			   gridLines="yes"
			   bind="cfc:cfgrid.getVideoGamesForTable({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})"
			   sort="yes"
			   stripeRowColor="FC6"
			   stripeRows="yes"
			   selectmode="edit"
			   onChange="cfc:cfgrid.updateVideoGames({cfgridaction},{cfgridrow},{cfgridchanged})"
			   insert="yes"
			   delete="yes"
			   >
				 
			   <cfgridcolumn name="videogameid" display="no" />
			   <cfgridcolumn name="title" header="Title" target="_blank" width="300" />
			   <cfgridcolumn name="dateacquired" header="Acquired" dataalign="center" type="date" width="100" />
			   <cfgridcolumn name="genre" header="Genre" width="200" />
			   <cfgridcolumn name="num_discs" header="## Discs" width="50" />
			   <cfgridcolumn name="esrb_rating" header="ESRB" width="50" />
			   <cfgridcolumn name="publisher" header="Publisher" width="200" />
			   <cfgridcolumn name="platform" header="Platform" width="200" />
			   <cfgridcolumn name="seriesname" header="Series Name" width="200" />
			</cfgrid>
		</cfform>
	</div>
</body>
</html>

Now our component.  It's similar to the one for Part 2 except an additional function has been added to process the grid updates.

<cfcomponent>
	<!--- Our function to populate our grid --->
	<cffunction name="getVideoGamesForTable" access="remote" returntype="struct" output="false">	  
		<cfargument name="Page" type="string" required="false" default="" />
		<cfargument name="PageSize" type="string" required="false" default="" />
		<cfargument name="GridSortColumn" type="string" required="false" default="dateacquired" />
		<cfargument name="GridSortDirection" type="string" required="false" default="DESC" />
		 
		<cfset var startRow = (Arguments.Page-1) * Arguments.PageSize />
		<cfset var endRow = Arguments.Page * Arguments.PageSize />
		<cfset var qVideoGames = "" />
		 
		<cfif NOT LEN(TRIM(Arguments.GridSortColumn))>
			<cfset Arguments.GridSortColumn = "dateacquired" />
			<cfset Arguments.GridSortDirection = "DESC" />
		</cfif>
		 
		<!--- obviously in a real-world use case, we'd have some validations here to be sure the sort and page stuff were valid entries; and a try/catch in case of query failure --->
		 
		<cfquery name="qVideoGames" datasource="#application.dsn#">
			SELECT videogameid, title, dateacquired, num_discs, esrb_rating, publisher, genre, platform, seriesname
			FROM videogames
			ORDER BY #Arguments.GridSortColumn# #Arguments.GridSortDirection#
		</cfquery>
		 
		<cfreturn QueryConvertForGrid(qVideoGames, Arguments.Page, Arguments.PageSize) />
	</cffunction>
	
	<!--- the function called by the grid when a change is made --->
	<cffunction name="updateVideoGames" access="remote" returntype="void" output="false">	  
		<cfargument name="CFGridAction" type="any" required="false" default="" />
		<cfargument name="CFGridRow" type="any" required="false" default="" />
		<cfargument name="CFGridChanged" type="any" required="false" default="" />
		
		<cfset var sVideoGame = Arguments.CFGridRow />
		
		<!--- in a real world scenario - these actions would be passed to a service layer to handle things! --->
		<cfset var lFields = "title,dateacquired,genre,num_discs,esrb_rating,publisher,platform,seriesname" />
		
		<cfswitch expression="#Arguments.CFGridAction#">
		<!--- Values updated --->
		<cfcase value="u">
			<cfset var sqlType = "cf_sql_varchar" />
			<cfset var IsValidRequest = true />
		
			<!--- we use a loop even though it only does one change at a time in case we do add the multi option --->
			<cfloop collection="#Arguments.CFGridChanged#" item="changedColumn">
				<!--- we can't tell them something went wrong, but we can at least protect our database --->
				<cfif Not ListFindNoCase(lFields, changedColumn)>
					<cfset IsValidRequest = false />
				</cfif>
				
				<cfif IsValidRequest>
					<cfset sqlType = "cf_sql_varchar" />
					
					<!--- some basic checks --->
					<cfswitch expression="#changedColumn#">
						<cfcase value="num_discs">
							<cfset sqlType = "cf_sql_integer" />
							
							<cfif Not IsValid("integer", NewValue) OR NewValue LTE 0 OR NewValue GTE 5>
								<cfset IsValidRequest = false />
							</cfif>
						</cfcase>
						<cfcase value="dateacquired">
							<cfset sqlType = "cf_sql_date" />
				
							<cfif Not IsValid("date", NewValue) OR NewValue GT NOW()>
								<cfset IsValidRequest = false />
							</cfif>
						</cfcase>
					</cfswitch>
					
					<cfif IsValidRequest>
						<cftry>
							<cfquery datasource="#application.dsn#">
								UPDATE videogames 
								SET #changedColumn# = <cfqueryparam value="#TRIM(Arguments.CFGridChanged[changedColumn])#" cfsqltype="#sqlType#" />
								WHERE videogameid = #sVideoGame.videogameid#
							</cfquery>
							<!--- keep our aJax from throwing back a too detailed error --->
							<cfcatch><cfthrow message="Unable to save, check results and try again" /></cfcatch>
						</cftry>
					</cfif>
				</cfif>
			</cfloop>
		</cfcase>
		<!--- inserted new row --->
		<cfcase value="i">
			<cfquery datasource="#application.dsn#">
				INSERT INTO videogames (title, dateacquired, num_discs, esrb_rating, publisher, genre, platform, seriesname)
				VALUES (
					<cfqueryparam value="#TRIM(sVideoGame['title'])#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(sVideoGame['title']))#" />, 
					<cfqueryparam value="#DateFormat(LEFT(sVideoGame['dateacquired'], 10), "yyyy-mm-dd")#" cfsqltype="cf_sql_date" null="#NOT LEN(TRIM(sVideoGame['dateacquired']))#" />, 
					<cfqueryparam value="#TRIM(sVideoGame['num_discs'])#" cfsqltype="cf_sql_integer" null="#NOT LEN(TRIM(sVideoGame['num_discs']))#" />, 
					<cfqueryparam value="#TRIM(sVideoGame['esrb_rating'])#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(sVideoGame['esrb_rating']))#" />, 
					<cfqueryparam value="#TRIM(sVideoGame['publisher'])#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(sVideoGame['publisher']))#" />, 
					<cfqueryparam value="#TRIM(sVideoGame['genre'])#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(sVideoGame['genre']))#" />, 
					<cfqueryparam value="#TRIM(sVideoGame['platform'])#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(sVideoGame['platform']))#" />, 
					<cfqueryparam value="#TRIM(sVideoGame['seriesname'])#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(sVideoGame['seriesname']))#" />
				)					
			</cfquery>
		</cfcase>
		<!--- deleted one or more rows --->
		<cfcase value="d">
			<cfquery datasource="#application.dsn#">
				DELETE FROM videogames
				WHERE videogameid = #sVideoGame['videogameid']#
			</cfquery>
		</cfcase>
		</cfswitch>
	</cffunction>
</cfcomponent>

Since these are pretty dynamic things, for this post I did little video snippets showing their functions.

It "works" but as is/out of the box. With a few modifications to the settings and CFC you can also allow editing, inserting, deleting of multiple rows (I think).  However…

  • The table doesn't auto refresh after updating – but it does on insert and delete; there is a refresh button to the footer though that isn't the most user friendly way to handle it, IMHO, if it isn't in multiple edit mode
  • No built in way to pass back results to tell if the action succeeded or not – have to hand roll your own JS
  • If an insert does fail, you can't just hit save again, have to do a whole new insert and copy/paste or type it back in – not user friendly!
  • You can make a cfgridcolumn offer a drop list of options, but there isn't built in way to load those options from Ajax call
  • And as with all the CFGRID stuff – the libraries are already dated out of the box and isn't likely to be updated for months/years if ever and the generated code is clunky
  • If breaks if you have newer versions of Firebug running in Firefox
  • The auto width doesn't really work well, it still squishes stuff if you don't set sizes on most columns, and table is not responsive

On the pro side, for very basic table editing, it is fast enough to get going once you actually can translate the abysmal Adobe docs (or just Google and glean from Stack Overflow like I did LOL). 

So how about DataTables? As I mentioned in the beginning, the core DataTable functions don't include editing but there is an extension available to do it. Or at least, there was. Unfortunately, the free jEditable jQuery plug in used to play nicely with DataTables, but it now appears to be an abandoned project so I wouldn't recommend it.  The official Editor extension for and from DataTables looks awesome but it has a huge con: it is NOT free and it is NOT open source.  There is a 15 day free trial, then you have to buy a licenses.  Those licenses are nicely done per developer rather than site/user, but still, that's $119 for a single developer (with multi-developer licenses available for businesses). 

Fortunately, we can still get the same functionality for free by writing our own code, and it still has some nice improvements to the CFGRID code.

<!doctype html>
<html>
<head>
	<title>DataTables - Editing In Grid Example</title>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<meta name="description" content="Blahdy blah blah" />
	<meta name="robots" content="index, follow" />

	<!--- Pure CSS using Yahoo CDNs --->
	<link rel="stylesheet" href="http://yui.yahooapis.com/pure/0.5.0/pure-min.css">
	
	<!--[if lte IE 8]>
		<link rel="stylesheet" href="http://yui.yahooapis.com/pure/0.5.0/grids-responsive-old-ie-min.css">
	<![endif]-->
	<!--[if gt IE 8]><!-->
		<link rel="stylesheet" href="http://yui.yahooapis.com/pure/0.5.0/grids-responsive-min.css">
	<!--<![endif]-->
	
	<!--- jQuery using Google CDNs --->
	<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
	<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/jquery-ui.min.js"></script>
	<link rel="stylesheet" href="//ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/themes/flick/jquery-ui.css">

	<!--- Data Tables stuff: using DT's CDN and the new way of doing UI intergration --->
	<link type="text/css" href="//cdn.datatables.net/plug-ins/28e7751dbec/integration/jqueryui/dataTables.jqueryui.css" rel="stylesheet" />
	<script type="text/javascript" src="//cdn.datatables.net/1.10.0/js/jquery.dataTables.js"></script>
	<script type="text/javascript" src="//cdn.datatables.net/plug-ins/be7019ee387/integration/jqueryui/dataTables.jqueryui.js"></script>
	
	<!--- Block UI for avoiding double submissions --->
	<script type="text/javascript" src="jquery.blockUI-2.64.0.js"></script>
	
	<!--- custom stuff --->
	<link href="cfgridtodt.css" rel="stylesheet" type="text/css">
	<script type="text/javascript" src="datatables.js"></script>
</head>

<body>
<div id="layout">
	<h1>DataTables - Editing In Grid Example</h1>
	<div id="resultBox" class="message hideOnLoad"></div>
	<table id="videoGameList" class="sortedTable pure-form display"><!--- pure-form is for Pure, display is for DataTables --->
	<thead>
		<tr>
			<th>Title</th>
			<th>Acquired</th>
			<th>Genre</th>
			<th># Discs</th>
			<th>ESRB</th>
			<th>Publisher</th>
			<th>Platform</th>
			<th>Series Name</th>
			<th><!--- delete icon column ---></th>
		</tr>
	</thead>
	<tbody></tbody>
	<tfoot>
		<tr id="addRow" class="hideOnLoad">
			<th><input name="title" id="title" type="text" class="pure-input-1" placeholder="Game Title" /></th>
			<th><input name="dateacquired" id="dateacquired" type="text" class="pure-input-1" placeholder="Acquired" value="<cfoutput>#DateFormat(Now(), "mm/dd/yyyy")#</cfoutput>" /></th>
			<th><input name="genre" id="genre" type="text" class="pure-input-1" placeholder="Genre" /></th>
			<th><input name="num_discs" id="num_discs" type="text" class="pure-input-1" placeholder="# Discs" maxlength="1" /></th>
			<th><input name="esrb_rating" id="esrb_rating" type="text" class="pure-input-1" placeholder="ESRB" maxlength="1" /></th>
			<th><input name="publisher" id="publisher" type="text" class="pure-input-1" placeholder="Publisher" /></th>
			<th><input name="platform" id="platform" type="text" class="pure-input-1" placeholder="Platform" /></th>
			<th><input name="seriesname" id="seriesname" type="text" class="pure-input-1" placeholder="Series Name" /></th>
			<th><!--- no delete on add row, use the Cancel button! ---></th>
		</tr>
		<tr>
			<th colspan="9">
				<div id="refreshButton" class="tableButton">Refresh</div>
				<div id="insertButton" class="tableButton">Insert</div>
				<div id="saveButton" class="tableButton">Save</div>
				<div id="cancelButton" class="tableButton">Cancel</div>
			</th>
		</tr>
	</tfoot>
	</table>
</div>

<!--- Simple dialog box for deletions --->
<div id="deleteConfirm" title="Delete Game" class="dialogBoxes">
	<p>This game will be <strong style="color: #600;">deleted permanently</strong>; <strong>are you sure you want to delete <span id="gameName"></span></strong>?</p>
</div>

</body>
</html>

In our CFC we have our function for loading the table, then the functions for saving edits to fields, adding new records, and deleting records.

<cfcomponent>
	<cfsetting showdebugoutput="false" />
	
	<cffunction name="getVideoGamesForTable" access="remote" returnformat="JSON" returntype="any" output="false">	
		<cfargument name="sEcho" type="numeric" required="true" default="1" hint="DataTables: sEcho counter" />
		<cfargument name="iDisplayStart" type="numeric" required="false" default="0" hint="DataTables: page iDisplayStart indicator" />
		<cfargument name="iDisplayLength" type="numeric" required="false" default="10" hint="DataTables: how many records per page" />
		<cfargument name="sSearch" type="string" required="false" default="" />
		
		<cfset var sortField = '' />
		<cfset var sortFieldValue = '' />
		<cfset var sortFieldDirValue = '' />
		<cfset var sortFieldDir = '' />
		<cfset var orderFields = "" />
		<cfset var cleanSearchCriteria = TRIM(Arguments.sSearch) />
		<cfset var FilterOrderBy = "dateacquired DESC" />
		<cfset var aColumnArray = ListToArray("title,dateacquired,genre,num_discs,esrb_rating,publisher,platform,seriesname") />
		<cfset var sGridData = StructNew() />
		<cfset var aData = arrayNew(1) />
		
		<cfset var qAllVideoGames =  "" />
		<cfset var qVideoGames = "" />
		<cfset var qFilteredVideoGames = "" />
		
		<!--- If we have a sort argument, loop through and build out or order by --->
		<cfif IsDefined("Arguments.iSortCol_0")>
			<cfloop from="0" to="#Arguments.iSortingCols#" index="x">
				<cfset sortField = 'iSortCol_#x#' />
				<cfset sortFieldDir = 'sSortDir_#x#' />
				<cfif IsDefined("Arguments.#sortField#") EQ "YES">
					<cfset sortFieldValue = Arguments[#sortField#] + 1 />
					<cfset sortFieldDirValue = Arguments[#sortFieldDir#] />
					<cfset orderFields = ListAppend(orderFields, aColumnArray[sortFieldValue] & " " & sortFieldDirValue) />
				</cfif>
			</cfloop>
			<cfset FilterOrderBy = orderFields />
		</cfif>
		
		<cfquery name="qAllVideoGames" datasource="#application.dsn#">
			SELECT COUNT(DISTINCT videogames.videogameid) AS allRecordCount
			FROM videogames
		</cfquery>
		
		<cfset var startRow = 1 />
		<cfset var endRow = qAllVideoGames.RecordCount />
 
		<cfif Arguments.iDisplayLength NEQ -1>
			<cfset startRow = Arguments.iDisplayStart + 1 />
			<cfset endRow = startRow + Arguments.iDisplayLength />
		</cfif>
 
		<cfquery name="qFilteredVideoGames" datasource="#application.dsn#">
			SELECT DISTINCT videogameid, title, dateacquired, genre, num_discs, esrb_rating, publisher, platform, seriesname
			FROM videogames
			WHERE 1=1
				<cfif cleanSearchCriteria NEQ "">
					AND (
						title LIKE <cfqueryparam value="%#cleanSearchCriteria#%" cfsqltype="cf_sql_varchar" />
						OR dateacquired LIKE '%#cleanSearchCriteria#%'
						OR esrb_rating LIKE '%#cleanSearchCriteria#%'
						OR publisher LIKE '%#cleanSearchCriteria#%'
						OR platform LIKE '%#cleanSearchCriteria#%'
						OR seriesname LIKE '%#cleanSearchCriteria#%'
					)
				</cfif>
			<cfif FilterOrderBy NEQ "">ORDER BY #FilterOrderBy#</cfif>
		</cfquery>
		
		<cfif Arguments.iDisplayLength NEQ -1>
			<cfset startRow = Arguments.iDisplayStart + 1 />
			<cfset maxRows = Arguments.iDisplayLength />
		</cfif>
 
		<!--- Loop our results, implementing our filtering and pagination and putting into an array to be returned --->
		<cfoutput query="qFilteredVideoGames" startrow="#startRow#" maxrows="#maxRows#">
			<cfset sRowData = StructNew() />
			<cfset sRowData["pkey"] = qFilteredVideoGames.videogameid />
			
			<cfset sGridData = structNew() />
			<cfset sGridData["DT_RowId"] = qFilteredVideoGames.videogameid />
			<cfset sGridData["DT_RowData"] = sRowData />
			<cfset sGridData["title"] = qFilteredVideoGames.title />
			<cfset sGridData["dateacquired"] = DateFormat(qFilteredVideoGames.dateacquired, 'mm/dd/yyyy') />
			<cfset sGridData["genre"] = qFilteredVideoGames.genre />
			<cfset sGridData["num_discs"] = qFilteredVideoGames.num_discs />
			<cfset sGridData["esrb_rating"] = qFilteredVideoGames.esrb_rating />
			<cfset sGridData["publisher"] = qFilteredVideoGames.publisher />
			<cfset sGridData["platform"] = qFilteredVideoGames.platform />
			<cfset sGridData["seriesname"] = qFilteredVideoGames.seriesname />
			<cfset sGridData["delete"] = '<img class="deleteButton" src="delete.png" alt="delete' & qFilteredVideoGames.title & '" />' />
 
			<cfset ArrayAppend(aData, sGridData) />
		</cfoutput>
 
		<cfset sGridReturn["sEcho"] = JavaCast("int", Arguments.sEcho) />
		<cfset sGridReturn["recordsTotal"] = qAllVideoGames.allRecordCount />
		<cfset sGridReturn["recordsFiltered"] = qFilteredVideoGames.RecordCount />
		<cfset sGridReturn["data"] = aData />
		
		<cfreturn sGridReturn />
	</cffunction>
	
	<!--- function for processing data change--->
	<cffunction name="updateGameData" access="remote" returnformat="JSON" returntype="struct" output="false">	 
		<cfargument name="VideoGameID" type="numeric" required="false" default="" />
		<cfargument name="ChangedField" type="string" required="false" default="" />
		<cfargument name="NewValue" type="string" required="false" default="" />
		
		<cfset var lFields = "title,dateacquired,genre,num_discs,esrb_rating,publisher,platform,seriesname" />
		<cfset var validESRBs = "E,E10+,T,M,AO" />
		<cfset var validPlatforms = "Sony Playstation,Sony PS2,Sony PS3,Nintendo Wii,PC" />
		<cfset var sqlType = "cf_sql_varchar" />
		<cfset var sResults = StructNew() />
		<cfset sResults['ResultCode'] = 200 />
		<cfset sResults['ResultMessages'] = ArrayNew(1) />
		
		<!--- 
			Again, in a real world scenario - these actions would be passed to a service layer to handle things and there would be better data validation! 
			For now, just doing just really basic validations to demonstration being able to pass back messages!
		--->
		<cfif Not ListFind(lFields, Arguments.ChangedField)>
			<cfset sResults['ResultCode'] = 310 />
			<cfset ArrayAppend(sResults['ResultMessages'], Arguments.ChangedField & " is not an editable field") />
		<cfelseif Arguments.ChangedField NEQ "seriesname" AND TRIM(Arguments.NewValue) EQ "">
			<cfset sResults['ResultCode'] = 310 />
			<cfset ArrayAppend(sResults['ResultMessages'], Arguments.ChangedField & " is required") />
		</cfif>
		
		<!--- If we pass the very core checks, continue --->
		<cfif sResults['ResultCode'] EQ 200>
			<!--- checks for specific fields --->
			<cfswitch expression="#Arguments.ChangedField#">
				<cfcase value="num_discs">
					<cfset sqlType = "cf_sql_integer" />
					
					<cfif Not IsValid("integer", Arguments.NewValue) OR Arguments.NewValue LTE 0 OR Arguments.NewValue GTE 5>
						<cfset sResults['ResultCode'] = 340 />
						<cfset ArrayAppend(sResults['ResultMessages'], "Number of discs must be a valid integer between 1 and 5") />
					</cfif>
				</cfcase>
				<cfcase value="dateacquired">
					<cfset sqlType = "cf_sql_date" />
		
					<cfif Not IsValid("date", Arguments.NewValue) OR Arguments.NewValue GT NOW()>
						<cfset sResults['ResultCode'] = 340 />
						<cfset ArrayAppend(sResults['ResultMessages'], "Date acquired must be a valid date and cannot be in the future") />
					</cfif>
				</cfcase>
				<cfcase value="esrb_rating">
					<cfif Not ListFind(validESRBs, Arguments.NewValue)>
						<cfset sResults['ResultCode'] = 340 />
						<cfset ArrayAppend(sResults['ResultMessages'], "ESRB does not have that rating") />
					</cfif>
				</cfcase>
				<cfcase value="platform">
					<cfif Not ListFind(validPlatforms, Arguments.NewValue)>
						<cfset sResults['ResultCode'] = 340 />
						<cfset ArrayAppend(sResults['ResultMessages'], "I don't own that platform") />
					</cfif>
				</cfcase>
			</cfswitch>
			
			<!--- still good? do our update and if it fails, return the error --->
			<cfif sResults['ResultCode'] EQ 200>
				<cftry>
					<cfquery datasource="#application.dsn#">
						UPDATE videogames 
						SET #Arguments.ChangedField# = <cfqueryparam value="#TRIM(Arguments.NewValue)#" cfsqltype="#sqlType#" />
						WHERE videogameid = #Arguments.VideoGameID#
					</cfquery>
					
					<cfcatch>
						<cfset sResults['ResultCode'] = 300 />
						<cfset ArrayAppend(sResults['ResultMessages'], "An error occurred: " & cfcatch.Message) />
					</cfcatch>
				</cftry>
			</cfif>
		</cfif>
			
		<cfreturn sResults />
	</cffunction>
	
	<!--- process our inserted rows --->
	<cffunction name="insertNewGame" access="remote" returnformat="JSON" returntype="struct" output="false">	 
		<cfargument name="title" type="string" required="false" default="" />
		<cfargument name="dateacquired" type="string" required="false" default="" />
		<cfargument name="num_discs" type="string" required="false" default="" />
		<cfargument name="esrb_rating" type="string" required="false" default="" />
		<cfargument name="publisher" type="string" required="false" default="" />
		<cfargument name="genre" type="string" required="false" default="" />
		<cfargument name="platform" type="string" required="false" default="" />
		<cfargument name="seriesname" type="string" required="false" default="" />
		
		<cfset var validESRBs = "E,E10+,T,M,AO" />
		<cfset var validPlatforms = "Sony Playstation,Sony PS2,Sony PS3,Nintendo Wii,PC" />
		<cfset var sResults = StructNew() />
		<cfset sResults['ResultCode'] = 200 />
		<cfset sResults['ResultMessages'] = ArrayNew(1) />
		
		<!--- check data --->
		<cfif TRIM(Arguments.title) EQ "">
			<cfset sResults['ResultCode'] = 340 />
			<cfset ArrayAppend(sResults['ResultMessages'], "Title is required") />
		</cfif>
		
		<cfif TRIM(Arguments.dateacquired) EQ "" OR Not IsValid("date", Arguments.dateacquired) OR Arguments.dateacquired GT NOW()>
			<cfset sResults['ResultCode'] = 340 />
			<cfset ArrayAppend(sResults['ResultMessages'], "Date acquired must be a valid date and cannot be in the future") />
		</cfif>

		<cfif TRIM(Arguments.num_discs) EQ "" OR Not IsValid("integer", Arguments.num_discs) OR Arguments.num_discs LTE 0 OR Arguments.num_discs GTE 5>
			<cfset sResults['ResultCode'] = 340 />
			<cfset ArrayAppend(sResults['ResultMessages'], "Number of discs must be a valid integer between 1 and 5") />
		</cfif>

		<cfif TRIM(Arguments.esrb_rating) EQ "" OR Not ListFind(validESRBs, Arguments.esrb_rating)>
			<cfset sResults['ResultCode'] = 340 />
			<cfset ArrayAppend(sResults['ResultMessages'], "ESRB does not have that rating") />
		</cfif>

		<cfif TRIM(Arguments.publisher) EQ "">
			<cfset sResults['ResultCode'] = 340 />
			<cfset ArrayAppend(sResults['ResultMessages'], "Publisher is required") />
		</cfif>
		
		<cfif TRIM(Arguments.genre) EQ "">
			<cfset sResults['ResultCode'] = 340 />
			<cfset ArrayAppend(sResults['ResultMessages'], "Genre is required") />
		</cfif>
		
		<cfif TRIM(Arguments.platform) EQ "" OR Not ListFind(validPlatforms, Arguments.platform)>
			<cfset sResults['ResultCode'] = 340 />
			<cfset ArrayAppend(sResults['ResultMessages'], "I don't own that platform") />
		</cfif>
		
		<cfif sResults['ResultCode'] EQ 200>
			<cftry>
				<cfquery datasource="#application.dsn#">
					INSERT INTO videogames (title, dateacquired, num_discs, esrb_rating, publisher, genre, platform, seriesname)
					VALUES (
						<cfqueryparam value="#TRIM(Arguments.title)#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(Arguments.title))#" />, 
						<cfqueryparam value="#DateFormat(LEFT(Arguments.dateacquired, 10), "yyyy-mm-dd")#" cfsqltype="cf_sql_date" null="#NOT LEN(TRIM(Arguments.dateacquired))#" />, 
						<cfqueryparam value="#TRIM(Arguments.num_discs)#" cfsqltype="cf_sql_integer" null="#NOT LEN(TRIM(Arguments.num_discs))#" />, 
						<cfqueryparam value="#TRIM(Arguments.esrb_rating)#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(Arguments.esrb_rating))#" />, 
						<cfqueryparam value="#TRIM(Arguments.publisher)#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(Arguments.publisher))#" />, 
						<cfqueryparam value="#TRIM(Arguments.genre)#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(Arguments.genre))#" />, 
						<cfqueryparam value="#TRIM(Arguments.platform)#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(Arguments.platform))#" />, 
						<cfqueryparam value="#TRIM(Arguments.seriesname)#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(Arguments.seriesname))#" />
					)	
				</cfquery>
				
				<cfcatch>
					<cfset sResults['ResultCode'] = 300 />
					<cfset ArrayAppend(sResults['ResultMessages'], "An error occurred: " & cfcatch.Message) />
				</cfcatch>
			</cftry>
		</cfif>
		
		<cfreturn sResults />
	</cffunction>
	<cffunction name="deleteGame" access="remote" returnformat="JSON" returntype="struct" output="false">	 
		<cfargument name="VideoGameID" type="numeric" required="false" default="" />
		
		<cfset var sResults = StructNew() />
		<cfset sResults['ResultCode'] = 200 />
		<cfset sResults['ResultMessages'] = ArrayNew(1) />
		
		<!--- just make sure we have a game ID --->
		<cfif TRIM(Arguments.VideoGameID) EQ "" OR Not IsNumeric(Arguments.VideoGameID)>
			<cfset sResults['ResultCode'] = 340 />
			<cfset ArrayAppend(sResults['ResultMessages'], "Invalid video game id") />
		</cfif>
		
		<cfif sResults['ResultCode'] EQ 200>
			<cftry>
				<cfquery datasource="#application.dsn#">
					DELETE FROM videogames
					WHERE videogameid = <cfqueryparam value="#TRIM(Arguments.VideoGameID)#" cfsqltype="cf_sql_integer"/>
				</cfquery>
				
				<cfcatch>
					<cfset sResults['ResultCode'] = 300 />
					<cfset ArrayAppend(sResults['ResultMessages'], "An error occurred: " & cfcatch.Message) />
				</cfcatch>
			</cftry>
		</cfif>
		
		<cfreturn sResults />
	</cffunction>
</cfcomponent>

And finally the JavaScript. Just over 200 lines of code which isn't too bad at all. I heavily commented so it's easier to follow along versus me explaining it all in paragraph form.

// to filter out ColdFusion's AJAX security leader, if necessary
$.ajaxSetup({
	dataFilter:function(data,type) {
		if (data.substring(0,2) == "//") {
			//remove "//", if there is one
			data = data.substring(2,data.length)
		}
		return data;
	}
});

// place holders for variables we need to reference from multiple places
var selectedCell, cellColumn, cellRow, aRowData, originalCellValue, VideoGameID, selectedField;

$(document).ready(function(){
	// style our buttons then hide all but insert
	$(".tableButton").button();
	$(".tableButton:not(#insertButton)").button().hide();
	
	/*
		notice we're setting our table to a variable name this time for easier manipulation/reference; also the dataTable 
		set up code has been updated to reflect DataTables 1.10's code
		
		One exception to this is I am using sAjaxSource instead of the newer ajax.  This is because ColdFusion 9.02 seems to be completely
		unable to handle the new variables passed with 1.10 - it isn't properly seeing them as arrays.  So by using sAjaxSource, we
		have DataTables activate a legacy mode that indicates it should use the old style variables (while still letting us use the new
		DataTable version overall
	*/
	$.fn.dataTable.ext.legacy.ajax = true;
	var tVideoGames = $('#videoGameList').DataTable({
		autoWidth: false,
		pagingType: "full_numbers",
		order: [[ 1, "desc" ]],
		serverSide: true,
		processing: false,
		ajax: "datatables.cfc?method=getVideoGamesForTable",
		columns: [
			{ data: "title" },
			{ data: "dateacquired" },
			{ data: "genre" },
			{ data: "num_discs" },
			{ data: "esrb_rating" },
			{ data: "publisher" },
			{ data: "platform" },
			{ data: "seriesname" },
			{ data: "delete" }
		],
		columnDefs: [
			{ width: "10em", className: "editable alignCenter", targets: [ 1 ] },
			{ width: "5em", className: "editable alignCenter", targets: [ 3, 4 ] },
			{ className: "editable", targets: [0, 2, 5, 6, 7]},
			{ width: "1em", className: "deleteCol", orderable: false, targets: [ 8 ] }
		]
	});
	
	/*
		edit cells - if you would like to let people edit multiple fields, I'd remove the save on blur and instead use the 
		save button like we do with the insert button
	*/
	// flip text to input field on click of a table cell that is editable
	$("#videoGameList").on("click", ".editable", function(){
		// make an array of our base field names/IDs; in order of columns in table!
		var aFields = new Array('title', 'dateacquired', 'genre', 'num_discs', 'esrb_rating', 'publisher', 'platform', 'seriesname');

		// hide any previous message
		$("#resultBox").hide();
		
		// use if to avoid issues with double clicking!
		if ($(this).html().indexOf("<input") == -1) {
			// get the info on our cells, row, and our game id; I went more verbose here for easier following along
			selectedCell = tVideoGames.cell(this);
			cellColumn = selectedCell.index().column;
			cellRow = selectedCell.index().row;
			aRowData = tVideoGames.row(cellRow).data();
			originalCellValue = selectedCell.data();
			VideoGameID = aRowData['DT_RowId'];
			selectedField = aFields[cellColumn];
			
			// set up our field
			var fieldToUse = '<input name="' + selectedField + '" id="' + selectedField + '" type="text" class="editField pure-input-1" data-originalvalue="' + originalCellValue + '" value="' + originalCellValue + '" />';

			// load in the new field after clearing the original cell and focus it
			selectedCell.data(fieldToUse);
			$("input", this).focus();
		}
	});
	// on blurring of a field, save the edit if one was made
	$("#videoGameList").on("blur", ".editField", function(){
		var newValue = $(this).val();
		
		if (originalCellValue != newValue) {
			var ColumnHeader = tVideoGames.column(cellColumn).header();
			var ChangedColumn = $(ColumnHeader).text();
			
			// if we're changing the title, do a little flip otherwise it puts the input field in the success message LOL
			if (ChangedColumn == "Title") {
				gameChanged = originalCellValue;
			}
			else {
				gameChanged = aRowData['title'];
			}
		
			$.blockUI({ message: '<h1>Just a moment...</h1>' });
			$.ajax({
				url: "datatables.cfc?method=updateGameData",
				dataType: "json",
				type: "POST",
				async: false,
				data: {
					VideoGameID: VideoGameID,
					ChangedField: selectedField,
					NewValue: newValue
				},
				success: function(data) {
					// process the results and display either a success or the error
					if (data['ResultCode'] == 200){
						$("#resultBox").html(ChangedColumn + ' of ' + gameChanged + ' changed successfully');
						$("#resultBox").removeClass("error").addClass("success").show();
						
						// flip our cell back to text with our nice new value
						selectedCell.data(newValue);
						
						// show the refresh button in case they want to reapply sorting and the like
						$("#refreshButton").show();
					}
					 else {
						$("#resultBox").html('');
						
						$.each(data['ResultMessages'], function(index, value) { 
							$("#resultBox").append("<div>" + value + "</div>");
						});
						
						$("#resultBox").removeClass("results").addClass("error").show().focus();
						
					}
				}					
			});
			$.unblockUI();
		}
		// if no change made, flip it back to text
		else {
			selectedCell.data(originalCellValue);
		}
	});
	
	//  if someone clicks a delete icon
	$("#videoGameList tbody").on("click", ".deleteButton", function(){
		// get cell/row data
		selectedCell = tVideoGames.cell($(this).parent());
		cellRow = selectedCell.index().row;
		aRowData = tVideoGames.row(cellRow).data();
		VideoGameID = aRowData['DT_RowId'];
		VideoGameTitle = aRowData['title'];
		
		// make oure delete message nice and specific
		$("#gameName").html(VideoGameTitle);
		
		// delete dialog config
		$("#deleteConfirm").dialog({
			modal: true,
			width: 400,
			height: 300,
			buttons: {
				"Delete Game": function() {
					$.blockUI({ message: '<h1>Just a moment...</h1>' });
					$.ajax({
						url: "datatables.cfc?method=deleteGame",
						dataType: "json",
						type: "POST",
						async: false,
						data: { VideoGameID: VideoGameID },
						success: function(data) {
							// process the results, 200 is good, anything else give them the error
							if (data['ResultCode'] == 200){
								$("#resultBox").html(VideoGameTitle + ' deleted successfully');
								$("#resultBox").removeClass("error").addClass("success").show();
								
								// refresh the table
								tVideoGames.draw();
							}
							 else {
								$("#resultBox").html('');
								
								$.each(data['ResultMessages'], function(index, value) { 
									$("#resultBox").append("<div>" + value + "</div>");
								});
								
								$("#resultBox").removeClass("results").addClass("error").show().focus();
								
							}
						}					
					});
					$.unblockUI();
					$(this).dialog("close");
				},
				Cancel: function() {
					$(this).dialog("close");
				}
			}
		});
	});
	
	// refresh button is pretty easy
	$("#refreshButton").click(function(){
		$("#resultBox").hide();
		$(".tableButton:not(#insertButton)").hide();
		tVideoGames.draw();
	});
	
	// so is the insert button
	$("#insertButton").click(function(){
		$("#addRow, #saveButton, #cancelButton").show();
		$("#insertButton").hide();
	});
	
	// the save button is only used for inserts in this example; it would be fairly easy to update it for allowing multiple field edits too
	$("#saveButton").click(function(){
		$.blockUI({ message: '<h1>Just a moment...</h1>' });
		$.ajax({
			url: "datatables.cfc?method=insertNewGame",
			dataType: "json",
			type: "POST",
			async: false,
			data: {
				title: $("#title").val(),
				dateacquired: $("#dateacquired").val(),
				genre: $("#genre").val(),
				num_discs: $("#num_discs").val(),
				esrb_rating: $("#esrb_rating").val(),
				publisher: $("#publisher").val(),
				platform: $("#platform").val(),
				seriesname: $("#seriesname").val()
			},
			success: function(data) {
				if (data['ResultCode'] == 200){
					$("#resultBox").html($("#title").val() + ' add successfully');
					$("#resultBox").removeClass("error").addClass("success").show();
					
					// refresh the table
					tVideoGames.draw();
					
					$("#addRow input").val('');
					$("#addRow, .tableButton").hide();
					$("#insertButton, #refreshButton").show();
				}
				 else {
					$("#resultBox").html('');
					
					$.each(data['ResultMessages'], function(index, value) { 
						$("#resultBox").append("<div>" + value + "</div>");
					});
					
					$("#resultBox").removeClass("results").addClass("error").show().focus();
				}
			}					
		});
		$.unblockUI();
	});
	
	// cancel button is also only used for inserts - blank the add row, rehide it, and reset our buttons
	$("#cancelButton").click(function(){
		$("#addRow input").val('');
		$("#addRow, .tableButton:not(#insertButton)").hide();
		$("#insertButton").show();
	});
});

And with all that, here is it's video showing:

So again the main con with DataTables if you want grid editing like this is either paying for the Editor extension or having to write your own JS.  Seriously, though, it didn't take me too long to write that code and most of it is pretty simple using jQuery and the DataTables API functions.  Took maybe two days, and quite a bit of that was being interrupted by other stuff and having to relearn DataTables for the new version, which has some pretty awesome stuff.

That pretty much finishes my series on DataTables and ColdFusion.  Doing the same in PHP would be similar, just the Ajax side would change (obviously) and I think PHP may be able to read in the newer variables and can avoid the legacy jump.  If you'd like the code for this example, you can download it here.  It also includes the SQL to make the table definition if you want to try it out on your own system.

  • Peter

    Hi, I all and make some test…. But, I have many questions:

    1st. What do you think ? It’s correct: cfgrid.getVideoGamesForTable({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})” ? because the names of arguments is: Page, PageSize … and I have a error like this:

    The specified CFC cfgrid could not be found.
    Can you help me ?
    Thank you very much ! 🙂

    • It can’t find your cfgrid.cfc. Is it in the same folder or in your component path?

  • kmouse

    I got this to work but having problems passing in a Variable (either via URL or hidden form element) into the cfc. I’ve never really done this via js form before so I’m sure it’s something simple. I will re-read the notes to try and work it out, suggestions very much appreciated!

    • Is the hidden form element you want to pull a value from a single field that’s common to the whole form? If so, you should be able to add it to the data values for the appropriate Ajax call, similar to how the fields are done on lines 225-232.

      • kmouse

        Again – newbie – that example is for the insert, I want to include it on the “get”. I’m going to make them insert on a different page (other issues), I just want to “get” for a specific project (in the video game example, just one type of platform). . . But, yes, it’s a single value for the whole form – a filtered result set. Thanks SO very much!!!

        • Ah, so you want to add it to the dataTables population bit, lines 30-54? In that case, you would need to expand the ajax bit. I used the short form for my example since I wasn’t passing in any parameters. So you would change it from something like this:

          ajax: “datatables.cfc?method=getVideoGamesForTable”,

          To something like this:
          ajax: {
          url: “datatables.cfc?method=getVideoGamesForTable”,
          data: {
          platform: $(“#limitplatform”).val()
          },
          },

          Is that what you were looking for?

  • Karl D.

    Hi, Great tutorial.

    With the newest datatables you can use “ajax :” and pass your parameters into the CFC as JSON data, which will automatically be converted to a coldfusion datatypes like an array of structs.

    You must use the “argumentCollection” as a Ajax parameter and JSON.stringify() the data. See below.

    ajax: {
    url : “data.cfc”,
    type : “POST”,
    data : function ( d ) {
    d.method = ‘datatables’;
    d.argumentCollection = JSON.stringify(d);
    },
    dataType : “json”
    }

    Your CFC will receive the following as parameters/arguments:

    1. columns : array of structs
    2. draw: string
    3. length: string
    4. order : array of structs
    5. search: struct
    6. start : string
    7. All the usual datatables parameters as separate variables.
    8. Any custom parameters you want to add, like “method” name.

    pic:
    http://i.imgur.com/4z6zw43.jpg

    Thanks!
    Karl D.

    • Good to know, thanks 🙂

  • CFdeveloper

    Nice tutorial. Do you have any updates for datatables 1.10.x especially sorting? Thanks

    • Unfortunately no, as I now primarily work in PHP instead of ColdFusion

  • Haring Jakobe

    Hi, your example is very useful 🙂 , and I learned a lot from it. Just a couple of question though, may I know coz I need to put a dropdown on the dataTable where user can select values instead of input a string/text ?
    Pls help.
    thank you in advance! 🙂

    • This example is for an older version of DataTables. I’d recommend looking at their website to see current examples.