Using ColdFusion and Ajax to check for an existing database record

This post is more than 2 years old.

A user on Twitter (@sean010101, or Sean21 to his friends), asked about using cfinput to validate that a value does not collide with entries in the database. I assume he was talking about the validation aspects of cfform. That is not something I make use of myself. There's a lot of built in validation rules with cfform, but I normally would make use of the jQuery Validate plugin. That being said - his query - validating to see if a record exists - interested me so I decided to dig a bit to see what was possible.

My goal was this. Given a form with one field, a name field, validate that the value entered by the user did not match an existing last name in the art database that ships with ColdFusion. I decided to first try a simple message base solution. By that I meant the user would be told the last name was a match.

<cfform name="mainForm"> New Artist: <cfinput name="artist"><br/> <cfdiv bind="cfc:test.checkArtist({artist@keyup})" >

</cfform>

In this example, I've used cfdiv to bind to a CFC. It listens in for changes to the artist form field and specifically makes use of the keyup event listener. I know I kind of poo poo on the front end Ajax stuff ColdFusion uses, but I really like how simple this is. Let's look at the CFC.

component {

remote function checkArtist(string n) {
	var q = new com.adobe.coldfusion.query();
	q.setDatasource("cfartgallery");
	q.setSQL("select lastname from artists where upper(lastname) = :name");
	q.addParam(name="name",value="#ucase(arguments.n)#",cfsqltype="cf_sql_varchar");
	var res = q.execute().getResult();
	if(res.recordCount) return "This artist already exist.";
	return "";
}

}

As you can see, I do a simple database check and if there is an exact match, I return an error. You can demo this yourself here: http://www.coldfusionjedi.com/demos/aug312011/test3.cfm. Try "Weber" as a name and you will see it return a match.

Ok - now that works, but it doesn't actually validate per se in that it should also prevent you from submitting the form, right? The first thing I considered was a cfinput/type=button that used a bind as well. What you may not know, and I had forgotten myself too, that you can bind to a specific HTML attribute. I was going to use this to bind to the disabled attribute of the button, but in HTML, the presence of disabled as an attribute means the item is disabled. You can't do (as far as I know), disabled="false". So I decided to go another route.

<cfajaxproxy bind="cfc:test.checkArtist({artist@keyup})" onSuccess="handleConflict"> <script> function handleConflict(r){ if (r == true) { document.getElementById("warning").innerHTML="This artist exists."; document.getElementById("submitBtn").setAttribute("disabled", "disabled"); } else { document.getElementById("warning").innerHTML=""; document.getElementById("submitBtn").removeAttribute("disabled"); } } </script>

<cfform name="mainForm"> New Artist: <cfinput name="artist"><br/> <div id="warning"></div> <input type="submit" id="submitBtn" value="Save"> </cfform>

In this version, I use a cfajaxproxy to create a bind between the form field and a JavaScript function. What may not be immediately clear is that ColdFusion is handling all the Ajax for me. I basically tell it what CFC to run and what function to call on success. Now I can take the result (I modified the CFC to return true or false) and do my own message. Also note the addition of code to add or remove the disabled attribute. Here's the new CFC method:

remote function checkArtist(string n) { var q = new com.adobe.coldfusion.query(); q.setDatasource("cfartgallery"); q.setSQL("select lastname from artists where upper(lastname) = :name"); q.addParam(name="name",value="#ucase(arguments.n)#",cfsqltype="cf_sql_varchar"); var res = q.execute().getResult(); return res.recordCount==1; }

Pretty simple, right? You can test this one here: http://www.coldfusionjedi.com/demos/aug312011/test2.cfm As before, use "Weber" as a matched result. Tomorrow I'll do an example where I still use ColdFusion on the back end but replace all the front end code with jQuery.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Martin posted on 9/1/2011 at 1:45 PM

Hi Ray,
Great post! I was wondering...
If you fill in the form and hit enter, won't that submit the form as well - whether the save button is (available &) clicked or not?

Comment 2 by Raymond Camden posted on 9/1/2011 at 3:35 PM

Ah good point. With the one field, it submits anyway. You can fix that. I was thinking the real form would have a few more fields in it.

Comment 3 by Joshua Siok posted on 9/1/2011 at 11:20 PM

I realize this is just a how-to example and not specific to real world usage, but If you had more than one artist with the same last name, you'd end up returning false, because the recordcount wouldn't be 1. I would suggest using "return res.recordCount != 0". Thus, any amount of records would return true (indicating the record is already there).

Comment 4 by Raymond Camden posted on 9/1/2011 at 11:23 PM

Well the point was that we don't allow 2 at all, so the db should be safe.

Comment 5 by Claude posted on 9/8/2011 at 9:56 PM

The way you wrote your cfc, I notice no cfcomponent tags or cffunction tags. Is this implied or are you writing this in a different fashion?

love your blog.
Thanks

Comment 6 by Raymond Camden posted on 9/8/2011 at 10:12 PM

That's the script based form of CFCs. You don't have to use them if you prefer the tags.

Comment 7 by Richie posted on 2/15/2014 at 1:15 AM

what does this component look like in a tag form??

Comment 8 by Raymond Camden posted on 2/15/2014 at 2:37 AM

Just a regular cfquery tag.