Tricklin

As I mentioned in my previous post, I planned on creating a "tutorial that will walk you through a real world working example of AJAX using ColdFusion and a simple MS Access Database." Well, here it is:

In order to represent a working example of AJAX, there are a few things we're going to need.

• database
• HTML file
• JavaScript (located within the HTML file in this example)
• ColdFusion file (note: any programming language can be used)
• prototype JavaScript library

The Database

The database used in this example is an extremely basic MS Access database consisting of one table that contains 6 fields:

Table Name:  Employee
Field Name	Data Type
EmpID	AutoNumber
FirstName	Text
LastName	Text
JobTitle	Text
Phone	Text
Fax	Text

Now that we know what we are going to be storing, let's take a look at the ".html" file.

HTML File

This file will consist of the form fields needed to populate the database, along with the placeholder we will use to show the information being stored:

<form name="addEmployee">
First Name:<input type="text" name="FirstName" id="FirstName" value="" size="30" />
<br />
Last Name:<input type="text" name="LastName" id="LastName" value="" size="30" />
<br />
Job Title:<input type="text" name="JobTitle" id="JobTitle" value="" size="30" />
<br />
Phone:<input type="text" name="Phone" id="Phone" value="" size="12" />
<br />
Fax:<input type="text" name="Fax" id="Fax" value="" size="12" />
<br />
<input type="button" name="submit" value="save" onclick="insertEmployee();">
</form>
<div id="EmployeeList"></div> //placeholder div

Notice how there is no method or action in the form tag? Both of these will be taken care of by the JavaScript.

The JavaScript

Once you've gotten all the form fields you need, you're now ready to start writing the JavaScript that will handle the AJAX portion. First, make sure you are referencing "prototype.js" (<script src="/scripts/prototype-1.4.0.js"></script>) . Now that prototype is in place, we can begin writing the necessary code to populate the "Employee" table. Notice from the xhtml code (above) the onclick event attached to the save button: onclick="insertEmployee();". This function is going to be called everytime a user clicks the "save" button. Let's take a look at this function:

function insertEmployee() {
    // begin form variables
    var fname = $F('FirstName');
    var lname = $F('LastName');
    var jtitle = $F('JobTitle');
    var phone = $F('Phone');
    var fax = $F('Fax');
    // end form variables
    var url = 'employee.cfm';
    var pars = 'FirstName='
        + fname
        + '&LastName='
        + lname
        + '&JobTitle='
        + jtitle
        + '&Phone='
        + phone
        + '&Fax='
        + fax
        + '&Action=Insert&rnd='
        + Math.random()*99999;
    var myAjax = new Ajax.Request(
        url,
        {
             method: 'post',
         &n bsp;  postBody: pars,
         &n bsp;  onComplete: listEmployees
        });
}

First of all, we're putting the values within each form field into variables. We'll then use these variables to pass them to the ColdFusion file, which in turn will insert these values into the database. The next step is to declare a variable that will contain the actual URL of this CF file. In this example, we're calling this variable url, and we're giving it the value of "employee.cfm". This value tells us that the CF file is located in the same directory as the ".html" file. Now that we have the values of each form field and the URL of the CF file declared, we're going to create another variable "pars" which will contain all the name/value pairs that will be passed to the CF file. In the code, we've written these name/value pairs separated by new lines, which improves readability. As you can see, these name/value pairs are passed to the CF file much like you'd pass them in a URL string. For example:

employee.cfm?FirstName=John&LastName=Doe&JobTitl e=Accountant...

One thing to take note of, however, is the fact that we are passing 2 additional name/value pairs that are not part of the form: "Action=Insert" and "rnd=[a random number]". The "Action=Insert" will make sense when we take a closer look at the CF file. The "rnd=[a random number]" is explained in detail in my previous post.

Now that we've got a good understanding of "pars" and "url", let's take a look at the actual request that we'll use to send to back-end:

    var myAjax = new Ajax.Request(
        url,
        {
             method: 'post',
         &n bsp;  postBody: pars,
         &n bsp;  onComplete: listEmployees
        });

What the above says, is that we're going to open an XMLHttpRequest that will "POST" the name/value pairs (pars) to "employee.cfm". Then, once the request is complete, the "listEmployees" function will be run. Now that we've got the insert request taken care of, let's take a look at what the CF file is doing on the back-end.

The ColdFusion File - "employee.cfm"

We know that we want to populate the database with the values in the form, and we know that the AJAX request will be "POSTing" this information. Since the request is using the "POST" method, we know we'll have to use the "FORM" variable (not URL) for grabbing the parameters being sent. Let's take a look at the CF code:

<cfif (IsDefined('Form.Action') AND Form.Action EQ "Insert")>
    <cflock name="Insert" type="EXCLUSIVE" timeout="30">
        <cfquer y name="insertemployee" Datasource="employee">
         &nb sp;  INSERT INTO Employee (FirstName, LastName, JobTitle, Phone, Fax)
         &nb sp;  VALUES ('#Form.FirstName#', '#Form.LastName#', '#Form.JobTitle#', '#Form.Phone#', '#Form.Fax#')
        </cfque ry>
    </cflock>
</cfif>

As you can see from the first line, this set of code will not be run unless the "Action" form parameter is defined, and that it's equal to "Insert". This is where the "&Action=Insert" name/value pair, added to the "pars" variable in the JavaScript code, comes into play. The next set of code uses a standard SQL statement to insert the data that is passed into the "Employee" table. The next set of code to get run is the query that will select everything from the "Employee" table, and then return these values in a table:

<cfquery name="listemployees" datasource="employee">
    SELECT *
    FROM Employee
    ORDER BY LastName
</cfquery>
<table cellpadding="4">
<thead>
    <tr>
        <td>&l t;b>Last Name</b></td>
        <td>&l t;b>First Name</b></td>
        <td>&l t;b>Job Title</b></td>
        <td>&l t;b>Phone</b></td>
        <td>&l t;b>Fax</b></td>
    </tr>
</thead>
<tbody>
<cfoutput query="listemployees">
    <tr id="#EmpID#">
        <td> #LastName#</td>
        <td> #FirstName#</td>
        <td> #JobTitle#</td>
        <td> #Phone#</td>
        <td> #Fax#</td>
    </tr>
</cfoutput>
</tbody>
</table>

This table is what gets returned to the ".html" page. Remember we said that the "listEmployees" function would be run when the request is completed. It is this function that is responsible for making this table appear on the page:

function listEmployees(originalRequest){
    $('EmployeeList').innerHTML = originalRequest.responseText;
    var doc = document.addEmployee;
    doc.FirstName.value = '';
    doc.LastName.value = '';
    doc.JobTitle.value = '';
    doc.Phone.value = '';
    doc.Fax.value = '';
}

When this function is run, the innerHTML of the "EmployeeList" placeholder div will contain the table that was created in "employee.cfm". Also added to this function is the clearing out of all the form fields.

There you have it: an example of inserting information into a database using AJAX and ColdFusion. As you can see from this example, you can use any programming language you prefer. The point of this tutorial was to explain the step-by-step process of using AJAX from front-end to back-end to front-end again, all without refreshing the users browser.

You can see this example in action by checking out a sample demo I've created for this tutorial. In this demo, you will see a little more functionality. Feel free to view the source of this file to see how other functions are handled.