When a user submits a form to a website, they don't always send the be best formed responses in the form fields. Take leading and trailing spaces for example. For whatever reason, some folks seem to rest on the long key before or after their thought is translated to the hands and they type the response.

When entering this information into a database, the best action to take is to just use the Trim() function around the form variable. This is particularly helpful if your code was expecting a number and not spaces (converting their response into a CHAR).

<cfqueryparam value="#Trim(FORM.SomeValue)#" cfsqltype="CF_SQL_VARCHAR">

Not every form submission goes into a database, but trimming the fields of leading or trailing spaces will still be necessary. Here is CFSCRIPT that uses the StructKeyExists() function to determine if a page has FORM submissions. Using DO and WHILE, the loop progresses through the FORM.FieldNames structure and replaces each entry with a trimmed value.

While we're at it, we are going to ReReplaceNoCase() any HTML tags that may have ended up in the form submission. Just place it into the onRequestStart of an Application.cfc page and let it take care of the rest.

<cffunction name="onRequestStart">
   <cfargument name="RequestName" required="yes" />
   <cfscript>
      //Automatic TRIM of all FORM field submissions
      if (StructKeyExists(FORM, "FieldNames") AND ListLen(FORM.FieldNames) GT 0) {
         FormIndex=0;
         do {FormIndex++;
            FORM[ListGetAt(FORM.FieldNames, FormIndex)]=Trim(FORM[ListGetAt(FORM.FieldNames, FormIndex)]);
            FORM[ListGetAt(FORM.FieldNames, FormIndex)]=REReplaceNoCase(FORM[ListGetAt(FORM.FieldNames, FormIndex)], "<[^>]*>", "", "ALL");}
         while (FormIndex LT ListLen(FORM.FieldNames));}
   </cfscript>
</cffunction>

The trim can be tested with a form page submission to itself with a CFDUMP of the form field and the Len() function.

<cfdump var="#Len(FORM.TestField)#">

<form action="<cfoutput>#CGI.SCRIPT_NAME#</cfoutput>" method="post">
   <input name="TestField" type="text" />
   <input name="submit" type="submit" />
</form>

Try this with and without the auto trim and see that the length of the TestField value will change with leading/trailing spaces.

If you find this post useful please leave a comment and let me know how you used the information.

On my website (not the blog section), the menu items in the left column cause the content on the right to change without the page reloading. This was accomplished using a JavaScript function on the entry page to force navigation of a CFDIV using ColdFusion.navigate instead of a binding. The function ColdFusion.navigate requires two arguments, the URL and the ID of the specific CFDIV to display the URL content.

On the main page, in the HEAD, place the JavaScript function that each menu button will execute when the onClick event fires. Each button in your menu sends the URL information to the JavaScript function, which then sends it to the ColdFusion.navigate function and forces the CFDIV with your specific ID to load the new content using Ajax.

We add the bindonload attribute to the CFDIV tag so that our default content, encapsulated in the CFDIV, can call the JavaScript function with the first page to be loaded.

<script type="text/javascript">
   fnAjaxPage = function(obj) {
      ColdFusion.navigate(obj , "Category");}
</script>

<!-- MENU CHOICES -->
<input name="MenuButton1" type="button" value="Page One" onClick="fnAjaxPage('PageOne.cfm');" /><br />
<input name="MenuButton2" type="button" value="Page Two" onClick="fnAjaxPage('PageTwo.cfm');" />

<!--- AJAX CONTENT BEGIN --->
<cfdiv bindonload="false" id="Category">
   <script type="text/javascript">fnAjaxPage(“PageOne.cfm");</script>
</cfdiv>

This example is based on the possibility of several different menu selections that will each result in a call to the ColdFusion.navigate function. This will cause the CFDIV to reload content without the entire page loading. Depending on the amount of content contained within the selected pages, the main page may shrink or grow with the amount of display information.

If you find this post useful please leave a comment and let me know how you used the information.

To lock an input form field on the client side to only allow numeric entries, you can use a short piece of JavaScript that will limit the keys pressed. For example, a form field that is intended to record a monetary value, but the user types letters, dollar signs, commas etc. If you do not sanitize these entries before attempting to update your datasource, the page will error.

The first function limits what can actually be typed into the form field using "0123456789." as the key. Just add a minus sign to allow the recording of financial deficits "0123456789.-". This string can be modified in any way you wish to limit the input of a specific form field.

Just because you prevent them from typing spurious characters into your form field, does not mean the user will not be able to paste them from their buffer. The next two functions will lock the input so that the right mouse will not function. The second prevents the contextual menu from appearing and allowing the user to paste directly into the form field. The third prevents the CTRL key which in combination with the V keyboard key would allow the user to also paste directly into the form field.

<script type="text/javascript">
   fnNumberOnly=function(myfield, e) {
      var key;
      var keychar;
      if (window.event)
         key=window.event.keyCode;
      else if (e)
         key=e.which;
      else
         return true;
      keychar=String.fromCharCode(key);
      if ((key==null)||(key==0)||(key==8)||(key==9)||(key==13)||(key==27))
         return true;
      else if ((("0123456789.").indexOf(keychar) > -1))
         return true;
      else
         return false;}

   fnNoRightMouse=function(event) {
      if (event.button==2) {
         alert("RIGHT-CLICK is disabled for this field.   ");}}

   fnNoCTRL=function(e) {
      var code=(document.all) ? event.keyCode:e.which;
      var msg="'CTRL' key is disabled for this field.   ";
      if (parseInt(code)==17) {
         alert(msg);
         window.event.returnValue=false;}}
</script>

Usage:

<input name="TextBox" type="text" onKeyPress="return fnNumberOnly(this, event)" onmousedown="fnNoRightMouse(event)" onkeydown="return fnNoCTRL(event)" />

If you find this post useful please leave a comment and let me know how you used the information.

The old way of creating two relational select statements involved JavaScript and the more items that were in the select statements, the longer the arrays and the slower the page. Every possible combination was programmed into the JavaScript array which meant that the client browser had to receive every item from the total sum of both select statements. If you used ColdFusion to shorten the programming for the two selects you only made it easy for yourself, the client browser still had to be stuffed.

With Ajax, we have a better way of conducting this business. The second select statement is only built after the first selection is made. This reduces the amount of information that the client browser must have and simplifies both the content and the ColdFusion source document.

Normally, I would just build an Ajax lookup into the same page (especially if it was the only place I was going to use it), the result would be escaped by placing it at the top of the page and using CFABORT at its conclusion. The two selects would also be populated by two queries, but to give you real data to test this procedure with I am performing a query of queries in the second select.

To start we build our first pseudo query, naturally you wouldn't hardcode into the page like this but instead pull from a datasource. This method is only to give you a working example.

<cfscript>
   GetColor=QueryNew("ColorID, ColorName");
   QueryAddRow(GetColor, 2);
   QuerySetCell(GetColor, "ColorID", 1, 1);
   QuerySetCell(GetColor, "ColorName", "Red", 1);
   QuerySetCell(GetColor, "ColorID", 2, 2);
   QuerySetCell(GetColor, "ColorName", "Blue", 2);
</cfscript>

Next wrap the query around the OPTION of a SELECT tag to build your first decision model. As a result of changing the option, a CFDIV will be bound to a page to drive the contents of the second decision model. In this example I am sending it back to the same page using the CGI.SCRIPT_NAME substitution.

<select name="SelectColor">
   <cfoutput query="GetColor">
      <option value="#GetColor.ColorID#">#GetColor.ColorName#</option>
   </cfoutput>
</select>
<cfdiv bind="URL:#CGI.SCRIPT_NAME#?ColorID={SelectColor@change}" />

The first decision model presents the viewer with two color choices; red and blue. From that decision, a second decision model is formed with choices of physical objects that possess that color choice. At the top of this same page, check for the existence of the ColorID URL variable as passed by the CFDIV BIND change. In this example we are hardcoding a second query, but for practical use you would be referencing a real data source and not using a query of queries. Using the results of the URL filtered second query you build a second SELECT tag with relational OPTION values based on the first decision model.

<cfif StructKeyExists(URL, "ColorID")>
   <cfscript>
      GetItem=QueryNew("ItemID, ItemName, ColorID");
      QueryAddRow(GetItem, 4);
      QuerySetCell(GetItem, "ItemID", 1, 1);
      QuerySetCell(GetItem, "ItemName", "Apple", 1);
      QuerySetCell(GetItem, "ColorID", 1, 1);
      QuerySetCell(GetItem, "ItemID", 2, 2);
      QuerySetCell(GetItem, "ItemName", "Rose", 2);
      QuerySetCell(GetItem, "ColorID", 1, 2);
      QuerySetCell(GetItem, "ItemID", 3, 3);
      QuerySetCell(GetItem, "ItemName", "Sky", 3);
      QuerySetCell(GetItem, "ColorID", 2, 3);
      QuerySetCell(GetItem, "ItemID", 4, 4);
      QuerySetCell(GetItem, "ItemName", "Ocean", 4);
      QuerySetCell(GetItem, "ColorID", 2, 4);
   </cfscript>
   <cfquery name="GetItem" dbtype="query">
      SELECT   ItemID, ItemName
      FROM   GetItem
      WHERE   ColorID = <cfqueryparam value="#URL.ColorID#" cfsqltype="CF_SQL_INTEGER">
   </cfquery>
   <select name="SelectItem">
      <cfoutput query="GetItem">
         <option value="#GetItem.ItemID#">#GetItem.ItemName#</option>
      </cfoutput>
   </select>
   <cfabort>
</cfif>

This same model could be chained together to give you an unlimited number of relational selects, not just two. Each successive CFDIV would have to reside in the previous page. So, for a third relational select we would put a CFDIV on the same page as the SELECT that drives the change.

If you find this post useful please leave a comment and let me know how you used the information.

How to loop through form names and update a database where the form names are variable and without using the Evaluate() function.

Naming form elements the same as the database record element makes sense. It also helps keeps the two scopes aligned which will make troubleshooting and modifications a lot easier. In other words, FORM.Record should equal QueryName.Record. What can we do if the existence of the form elements is dependant on a query result and not always contains the exact same results when editing the represented record?

For example, there are four tables in a database (TablePeople, ListCourse, ListDuty, and BridgeTraining). The TablePeople is tracking the personnel who are accomplishing training and their duty type by ID, ListCourse is listing the courses to be accomplished, ListDuty is a listing of the different duty types in the organization, and BridgeTraining is holding the accomplished course items by ID and the personnel by ID. Each person in TablePeople will have entries in BridgeTraining, but depending on which job they hold in the organization, may have a different number of entries.

TablePeople (PeopleID, PeopleName, DutyID)
ListCourse (CourseID, CourseName)
ListDuty (DutyID, DutyName)
BridgeTraining (RecurID, PeopleID, CourseID, CourseDate)

When selecting people to update, you could just list all course possibilities in the company and cleverly disable input for those that are not available for the specific duty type or conditionally check each one before it is rendered to see if it should appear. You could also filter our those records that are not needed during the UPDATE query. I would prefer to limit the list of possible choices from ListCourse to only those that are specified as a result of ListDuty.

Assuming each CourseName was a single word (not sentence, not just a number, and not starting with a number), if you need more text to explain the course title then add an addition element to ListCourse and call it CourseTitle; we are only interested in CourseName as a name for the input into a form element. So, for brevity, a nice short course listing might look something like:

ListCourse
1 = CPR
2 = ISO9000
3 = HIPAA

When we select the first employee to edit, they only have course ID 1 and 3 required. So our form input has form names CPR and HIPPA. During the update query, we are going to loop over ListCourse and check to see if the corresponding form element exists and update that specific record.

<cfoutput query="ListCourse">
   <cfif StructKeyExists(FORM, "#ListCourse.CourseName#")>
      <!--- update BridgeTraining using #FORM["#ListCourse.CourseName#"]# where FORM.PeopleID --->
      <cfquery name="UpdateTraining" datasource="MyDatabase">
         UPDATE   BridgeTraining
         SET      CourseDate = <cfqueryparam value="#FORM['#ListCourse.CourseName#']#" cfsqltype="CF_SQL_TIMESTAMP">
         WHERE   PeopleID = <cfqueryparam value="#FORM.PeopleID#" cfsqltype="CF_SQL_INTEGER"> AND
               CourseID = <cfqueryparam value="#ListCourse.CourseID#" cfsqltype="CF_SQL_INTEGER">
      </cfquery>
   </cfif>
</cfoutput>

Notice that in the StructKeyExists and in the update query I am not using the Evaluate() function. Search Google for "avoid using evaluate" for more information. This examples presumes that you have already created entries in the BridgeTraining table when you either input the new employee or you changed their DutyID.

If you find this post useful please leave a comment and let me know how you used the information.