Password Confirmation in Registration Form

I got it working now.
However there is one error in the above code that was causing the error. The comma used at the end of '#Form.password#'. Removing that, the code works fine.

I have just modified the code.
<cfif IsDefined("FORM.password") AND #FORM.password# NEQ "">
'#FORM.password#'
<cfelse>
NULL
</cfif>

The above codenow reads as below.
<cfif IsDefined("FORM.password") AND #FORM.password# NEQ "">
<cfif #FORM.password# NEQ #FORM.password2#>
<cflocation url="test.cfm" addtoken="yes">
<cfelseif '#FORM.password#'>
<cfelse>
NULL
</cfif>
</cfif>

The code does have the onsubmit event handler that calls the javascript function.
function checkPassword(form)
{
if (form.password.value!=form.password2.value)
{
alert("Pass1 not equal Pass2");
return false;
}
return true;
}

<form action="<cfoutput>#CurrentPage#</cfoutput>" method="post" name="form1" onsubmit="MM_validateForm('firstName','','R','lastName','','R',
'userName','','R','password','','R','password2','','R','email_address','',
'NisEmail','accessLevel','','R','address','','R','city','','R','county','','R','telephoneNumber','','R');return document.MM_returnValue";"return checkPassword();">

Now according to the coldfusion coding, it first checks to see if form.password is
defined and not equal to a blank value. It then checks to see if form.password is not
equal to form.password2, it then redirects to another page. The redirection works.
But if both passwords are equal then this results is an error. If i enter a numerical password,
the error is shown as below.
Error Executing Database Query.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near ' 'bd@ftml.net' , 'Limited' , '2' at line 21

On entering just letters as the password, I get a different error.
cannot convert the value "asd" to a boolean

where am i going wrong?

add an onsubmit event handler to your form tag to call your js function.

Also, on the cold fusion side, do your check before you insert the record, not after.