Building an Editable Grid with AJAX and ColdFusion Components

Introduction

In the past few months, the design pattern of combining Asynchronous JavaScript and XML (AJAX) to develop highly interactive Web applications has been growing in popularity. The individual technologies that compose AJAX are not recent developments; they have been around for some time and have been continuously updated and improved. However, it is the recent confluence of these technologies that is leading to interesting possibilities.

A common data editing interface consists in: an HTML table showing a list of database records. A form where you can edit each record. And some action pages to update, insert and delete records. In other words, when a user wants to change some data, there are a lot of navigation and page reloads involved.

In this tutorial, we will change an HTML table into an editable grid. That is, we will automatically create a form from grid cells using JavaScript and call some ColdFusion component methods - using AJAX - to update information.

If you are not familiar to AJAX or XMLHttpRequest, take a look at my former tutorial Building a Suggest List with XMLHttpRequest

Implementation

We'll work on an HTML table showing a list of employees. We have the following properties for each employee: id, name, email and salary

First, let's take a look at our work files:

employee.cfc
ColdFusion Component containing all methods to work with employee table.
list.cfm
Calls getList method and draws the HTML table - our grid.
grid.css
used by LIST.CFM. Style sheet rules that apply to LIST.CFM elements.
grid.js
Used by LIST.CFM. All JavaScript code needed to create form fields and to send requests.

Click here to download zip file. Extract all files in the same directory.

employee.cfc

Contains all methods to access database. In order to keep this tutorial simple and so you can online test the grid, we won't connect to a database. In each method, you'll note commented SQL sentences. This doesn't make this tutorial less general, as we are interested in client-server interaction without page reloads.

getList()
Access: public; returns a query object with all employee table.
add(required id, required name, required email, required salary)
Access : remote; validates arguments and inserts a record in the database. Returns new record id or an error message.
set(required rowId, required id, required name, required email, required salary)
Access : remote; validates arguments and updates a record in the database. Returns record id or an error message.
remove(required rowId)
Access : remote; remove a record from the database. Returns an empty string or an error message.
validate(required struct values)
Access : private; validates arguments for add and set methods and returns proper messages. Three validation rulues are implemented: id cannot be empty; name must contain at least two words; and salary must be a number.

list.cfm

Most important parts of LIST.CFM are shown below.


<head>
	...
	<link rel="stylesheet" type="text/css" href="grid.css">
	<script type="text/javascript" src="grid.js"></script>
	...
</head>

<body>

<cfinvoke component="employee"
	method="getList"
	returnvariable="qList"></cfinvoke>

<table class="grid" id="employee">
<caption>Employees List</caption>
<thead>
	<tr>
		<th colspan="2" class="buttons">
			<a class="new" href="#" onclick="newRecord(this);" 
			title="Add a record"><span>New</span></a>
		</th>
		<th id="id">ID</th>
		<th id="name">Name</th>
		<th id="email">email</th>
		<th id="salary">Salary</th>
	</tr>
</thead>
<tbody>
<cfoutput query="qList">
	<tr id="#qList.id#">
		<td><a class="edit" href="##" onclick="editRecord(this);" 
			title="Edit this record"><span>Edit</span></a></td>
		<td><a class="delete" href="##" onclick="deleteRecord(this);" 
			title="Delete this record"><span>Delete</span></a></td>
		<td>#qList.id#</td>	
		<td>#qList.name#</td>	
		<td>#qList.email#</td>	
		<td>#qList.salary#</td>	
	</tr>
</cfoutput>
</tbody>
</table>
...

In section HEAD, we load GRID.JS and GRID.CSS files. In section BODY, we call getList method, getting qList query object.

We need to remark some points when creating the HTML table, in order to keep general our JavaScript code:

  1. For the TABLE tag, we must set the class attribute to grid and id with component's name.
  2. THEAD and TBODY tags are required.
  3. TH tags' id must match to methods arguments names inside employee.cfc.
  4. Each row's id must be set to a database record unique identifier.

grid.js

This is the key point in our tutorial. All functions are simple, but they require former JavaScript knowledge. We'll go into these functions one by one:

General functions

createRow()
Adds a row to HTML table.
deleteRow()
Removes a row from HTML table.
wddxGetElement()
A WDDX serialized string is returned by any call to employee.cfc methods. This function extracts desired return message. E.g.: <wddxPacket version='1.0'><header/><data><string>Salary must be a number!</string></data></wddxPacket> Running function over this WDDX string, we get message 'Salary must be a number!'
httpRequest()
Call methods using XMLHttpRequest().

getColumnNames(tableObj)

Retrieves TH id's so they can be as arguments names when calling add and set methods.


function getColumnNames(tableObj) {
	var theadObj = tableObj.getElementsByTagName('THEAD')[0];
	var trObj = theadObj.getElementsByTagName('TR')[0];
	var thObjs = trObj.getElementsByTagName('TH');
	var arrColumnNames = new Array;
	for (var i=1;i<thObjs.length;i++)
		arrColumnNames.push(thObjs[i].id);
	return arrColumnNames;
}

A table object is used as a parameter. Function identifies THEAD object inside this table and, for the first TR, retrieves arguments names - TH's id, putting them into an array.

startEditing(trObj)

Transforms each cell (TD) in a row into a form field.


function startEditing(trObj) {
	var arrColumnNames = getColumnNames(trObj.parentNode.parentNode);
	var tdObjs = trObj.getElementsByTagName('TD');
	tdObjs[0].innerHTML = 
		'<a class="save" href="#" onclick="saveRecord(this);" ' + 
		'title="Save this record"><span>Save</span></a>';
	tdObjs[1].innerHTML = 
		'<a class="cancel" href="#" onclick="cancelEditRecord(this);" ' +
		'title="Cancel editing"><span>Cancel</span></a>';
	for (var i=2;i<tdObjs.length;i++)
		tdObjs[i].innerHTML = 
			'<input type="text" name="'+arrColumnNames[i-2]+
			'" id="input-'+	arrColumnNames[i-2]+'" value="'+tdObjs[i].innerHTML+
			'" default="'+tdObjs[i].innerHTML+'">';
	return false;
}

Steps:

  1. Gets arguments names
  2. Finds all cells (TD's) inside TR object (function argument)
  3. Changes links inside two first cells. From edit and delete to save and cancel
  4. For all other cells, puts its content inside a form field

stopEditing(trObj,reset)

Transforms each form field into its primary or current value, according to boolean argument reset


function stopEditing(trObj,reset) {
	var tdObjs = trObj.getElementsByTagName('TD');
	tdObjs[0].innerHTML = 
		'<a class="edit" href="#" onclick="editRecord(this);" ' +
		'title="Edit this record"><span>Edit</span></a>';
	tdObjs[1].innerHTML = 
		'<a class="delete" href="#" onclick="deleteRecord(this);" ' +
		'title="Delete this record"><span>Delete</span></a>';
	var inputObjs = trObj.getElementsByTagName('INPUT');
	for (var i=tdObjs.length-1;i>1;i--)
		if (reset) tdObjs[i].innerHTML = inputObjs[i-2].defaultValue;
		else tdObjs[i].innerHTML = inputObjs[i-2].value;
	return false;
}

Steps:

  1. Finds all cells (TD's) inside TR object (function argument)
  2. Changes links inside two first cells. From save and cancel to edit and delete
  3. For all other cells, sets its content to form fields properties value/defaultValue, according to reset value

newRecord(obj)

Used to create a new row at the end of the table and to make it editable. Code is quite simple, once it just calls createRow() and startEditing() functions.


function newRecord(obj) {
	var trObj = createRow(obj);
	startEditing(trObj);
}

editRecord(obj)

Used to make a row editable. Code is also quite simple, once it just gets TR oobject and calls startEditing() function.


function editRecord(obj) {
	//	  A      TD         TR
	var trObj = obj.parentNode.parentNode;
	startEditing(trObj);
}

cancelEditRecord(obj)

Used when user wnats to cancel all modifications he did to a row. If current row has an empty id, it menas it is a new row, so it should be removed calling deleteRow() function. Otherwise, stopEditing() function is called.


function cancelEditRecord(obj) {
	//           A      TD         TR    
	var trObj = obj.parentNode.parentNode;
	var rowId = trObj.id;
	if ((rowId) && (rowId != ""))  // reset all values
		stopEditing(trObj,true);
	else // remove TR from TABLE
		deleteRow(trObj);
}

deleteRecord(obj)

Used to delete a row from the database.


function deleteRecord(obj) {
	if (!confirm('Are you sure you want to delete this record?')) return;
	//	  A      TD         TR
	var trObj = obj.parentNode.parentNode;
	var rowId = trObj.id;
	var tableObj = trObj.parentNode.parentNode;
	// Call REMOVE method to delete record from database
	var url = tableObj.id + '.cfc?method=remove&rowId=' + rowId;
	var response = wddxGetElement(httpRequest(url),'string');
	if (response!="") alert(response);
	else deleteRow(trObj);
}

Steps:

  1. Calls component's remove method, providing current row's id as argument
  2. If a non empty string is returned, shows the error message. Otherwise, removes current row from HTML table calling deleteRow() function

saveRecord(obj)

Used to send all modifications to the database.


function saveRecord(obj) {
	//           A      TD         TR    
	var trObj = obj.parentNode.parentNode;
	//              TR     TBODY      TABLE
	var tableObj = trObj.parentNode.parentNode;
	var inputObjs = trObj.getElementsByTagName('INPUT');
	var rowId = trObj.id;
	var url = tableObj.id;
	// Call SET/ADD method to save record to the database
	if ((rowId) && (rowId != "")) 
		url += '.cfc?method=set&rowId=' + rowId;
	else 
		url += '.cfc?method=add';
	for (var i=0;i<inputObjs.length;i++)
		url += '&' + inputObjs[i].name + '=' + inputObjs[i].value;
	var response = wddxGetElement(httpRequest(url),'string');
	if (response.substr(0,2) != "id") alert(response);
	else {
		trObj.id = response.substr(3);
		stopEditing(trObj,false);
	}
}

Steps:

  1. Finds all form fields at current row
  2. Defines which method, set or add - according to TR's id
  3. Gets key-value pairs from form fields and sets up method calling url
  4. If returned string is like "id:[id]", sets TR's id to returned id and calls stopEditing() function. Otherwise, shows error message

Testing

Now you have all files, let's test our editable grid. To do this, browse LIST.CFM page.

You can click here, if you want.

Try records insertions, updatings and removals. Force some validation errors like, omitting ids, giving names with only one word, or typing non-numerical values for salary.

About This Tutorial
Author: Claudio Dias
Skill Level: Intermediate 
 
 
 
Platforms Tested: CFMX
Total Views: 159,372
Submission Date: September 16, 2005
Last Update Date: June 05, 2009
All Tutorials By This Autor: 3
Discuss This Tutorial
  • How would you change it to textarea instead of input texts?

  • This is a really cool tutorial, And I'm looking for the web 2.0 within this week and founded that this is the easyway to submit data back to the database, do you think you have any idea that can be save the sortable lists data back to the database ? http://demo.script.aculo.us/ajax/sortable_elements

  • Thank for your comments! I think you can encrypt row ids inside list method and decrypt them in the other methods.

  • Great tutorial. One comment: typically you won't want the viewer to see the id, especially for security reasons. How could you use the id without showing it to the users seeing how you are using the table object for this. Can you post an example or resource that shows how to do this.

  • One of my coworkers was talking about an editable datagrid, like a WinForms application, but web based. I told him I didn't think it was possible. I was wrong! Perfect tutorial!

  • CJ, Tutorial shows how to update the database for each row - by clicking Save button. You can easily change JS function to save all records at a time.

  • Could you make this so you could enter a new row and then update the database when you are finished?

  • Jansen, thanks for sharing!

  • John, You can get a voting example with all of the code by downloading cfajax.1.2.zip at http://www.indiankey.com/cfajax/project.asp. Jansen

  • Very nice! Do you have plans to write a tutorial for ajax voting? I would very much like to see a tutorial on voting. I have a site with thousands of pictures, and would like to use something like ajax (like amazon, or netflicks, or yahoo) to vote on pictures.

Advertisement


Website Designed and Developed by Pablo Varando.