[rikkp]

Has anyone out there been having this same problem?
I have a coldfusion registration form that inserts the form data into the database.
The CFtry/cfcatch throws a password conflict error even though the password submitted does not exist.
Below is my code for both the form and ation page.
form code:

<cfform action="StuRegFrmAct.cfm" method="post" name="register">
 <cfinput name="username" type="text" size="20" message="Pease create a user name." required="yes" value="">
     <cfinput name="pass" type="text" size="20" message="Please create a password" required="yes" value="">
     <cfinput name="email" type="text" id="email" size="20" required="no" value
 
        <input type="hidden" name="id" value="" />
	<input type="hidden" name="roles" value="student" />
        <input type="hidden" name="register" value="register" />
        <input name="submit" type="submit" value="Register" />
</cfform>

Action page:

<cftry>
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.register") AND FORM.register EQ "register">  
<cfquery datasource="backpacklab">
  INSERT INTO users (id,  pass, username, email, roles) 
  VALUES
   (
     '#FORM.id#'
     ,
       
	 '#Trim(#FORM.pass#'
,
         '#Trim(#FORM.username#'
     ,    
      <cfif IsDefined("FORM.email") AND #FORM.email# NEQ "">
       '#Trim(#FORM.email#'

 <cfelse>
    null
    </cfif>   
          , 
      
   '#FORM.roles#'
   )
  </cfquery>
</cfif>

<cfcatch type="Database">
 The password choosen is already in use.
 Please go back and select a<b> different </b>password.

<cfabort>
</cfcatch>
  </cftry>
<p>Your new ad has been successfully submitted. 
<br />
<cfoutput>
Your Username is: #FORM.username#

<br />
Your password is:
<b>#FORM.pass#</CFOUTPUT>

[Craig328]

Hm. Well, I see two things that could help you out right off the bat:

You're using the Trim() function but you're missing a closing ")" bracket.

Your CFCATCH is telling you about a password conflict because that's the message you're telling it to give you...no matter what the error is. After the CFCATCH tag, try dropping in a <CFDUMP VAR="#cfcatch#"> and have a look at what he error actually is. It'll for certain be the missing closing bracket on the Trim function but it could be something else as well.

Good luck!

This post has been edited by Craig328: 04 April 2012 - 07:05 AM

[rikkp]

Sorry, I must have messed up when I copied the code and pasted it into the post here.
Here is the actual code once again.

Form page:

<cfform action="TeachRegFrmAct.cfm" method="post" name="teachRegister">

    <cfinput name="username" type="text" size="20" required="yes" message="please enter a username">        
<cfinput name="pass" type="text" size="20" required="yes" message="Please enter a password">
  <cfinput name="city" type="text" size="20">
  <select size="1" name="state">
<option>Please Select</option>
 <option>Alabama</option>
<option>Alaska</option>
</select>

 <select name="country">
        <option value="United States" selected="selected">United States</option>
        <option value="Afghanistan">Afghanistan</option>
        <option value="Albania"> Albania </option>
        <option value="Algeria"> Algeria </option>
</select>
<cfinput name="email" type="text" size="20">
<cfinput name="roles" type="hidden" value="teacher">
<input type="hidden" name="id" value="null">
    <input type="hidden" name="register" value="member"> 
    <input name="submit" type="submit" value="Register">
</cfform>

[code]

Action page:
[code]
<cftry>
 <cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.register") AND FORM.register EQ "member">
<cfquery datasource="tol">
   INSERT INTO users (id, username, pass, roles, state, country, email, city) VALUES
   (
    '#FORM.id#',
     '#Trim(FORM.username)#',
     '#Trim(FORM.pass)#',
    '#Trim(FORM.roles)#',
    '#Trim(FORM.state)#',
     '#Trim(FORM.country)#',
  <cfif IsDefined("FORM.email") AND #FORM.email# NEQ "">
    '#Trim (FORM.email)#'
      <cfelse>
      NULL
  </cfif>,
  <cfif IsDefined("FORM.city") AND #FORM.city# NEQ "">
  '#Trim(FORM.city)#'
  <cfelse>
  NULL
  </cfif>
   )
  </cfquery>
  </cfif>
   <cfcatch type="Database">

 PASSWORD CONFLICT: password already in use.please try again
<cfabort>
</cfcatch>
  </cftry>
DATABASE  RECORD CONFIRMATION:
Welcome #FORM.username#
You have just become our newest member

[Craig328]

Ah. Well that probably solves problem #1. The remaining issue that persists is that no matter what error is being thrown, your manual error message says it's due to a password issue when, in fact, you don't really know that.

Replace lines 54-58 of your form process page that currently shows as this:

<cfcatch type="Database">
PASSWORD CONFLICT: password already in use.please try again
<cfabort>
</cfcatch>

with this:

<cfcatch type="Database">
<cfdump var="#cfcatch#">
<cfabort>
</cfcatch>

The actual error reason should be outputted to the screen plain as day with not only a description of the exception but what caused it, where it's being caused and on what page.

[rikkp]

Here is my new code. First is the error information from CFDUMP
This is a MS access database and all field data types are set to TEXT except the ID (auto number)
ErrorCode: 3079 [empt string]
Message: Data type mismatch in criteria expression.
SQLState: [empty string]

array 1
struct
ID: cfquery
line: 27
type: CFM
-------------------------------------------------------------------------------------------
SQL
INSERT INTO users (id, username, pass, roles, state, country, email, city)
VALUES ( '' , 'un' , 'pass' , '1234' , 'Alaska' , 'United States' , 'email' , 'city' )
-------------------------------------------------------------------------------------------
form page:

<cfform action="TeachRegFrmAct.cfm" method="post" name="teachRegister">
    <cfinput name="username" type="text" size="20" required="yes" message="please enter a username">    
    <cfinput name="pass" type="text" size="20" required="yes" message="Please enter a password">
   <cfinput name="city" type="text" size="20" required="yes" message="Please enter city">
 <select size="1" name="state">
		<option>Please Select</option>
                                <option>Alabama</option>
                                <option>Alaska</option>
                                <option>Arizona</option>
</select>
<select name="country">
        <option value="United States" selected="selected">United States</option>
        <option value="Afghanistan">country 2</option>
        <option value="Albania"> country 3 </option>
        <option value="Algeria"> country 4 </option>
</select>
<cfinput name="email" type="text" size="20" required="no">
<cfinput name="roles" type="text" size="10">
<input type="hidden" name="id" value="" />
    <input type="hidden" name="register" value="member"> 
    <input name="submit" type="submit" value="Register" />
</cfform>

action page:

<cftry>
 <cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.register") AND FORM.register EQ "member">
<cfquery datasource="backpacklab">
   INSERT INTO users (id, username, pass, roles, state, country, email, city) 
   VALUES
   (
'#FORM.id#' ,
'#Trim(FORM.username)#'  ,
'#Trim(FORM.pass)#'    ,
<cfif IsDefined("FORM.roles") AND #FORM.roles# EQ "1234">
'#Trim(FORM.roles)#'
<cfelse> NULL</cfif>   ,
'#Trim(FORM.state)#'   ,
'#Trim(FORM.country)#' ,
<cfif IsDefined("FORM.email") AND #FORM.email# NEQ "">
'#Trim (FORM.email)#' <cfelse>NULL</cfif>  ,
'#Trim(FORM.city)#'
 )
  </cfquery>
  </cfif>
  
  <cfcatch type="Database">  
<cfdump var="#cfcatch#">  
<cfabort>  
</cfcatch> 

</cftry>

[Craig328]

Okay, so it's not a CF code error per se as it's coming back as a SQL error. Specifically, it's saying the problem is that you're trying to insert an empty string. You also said that the ID field is an auto number. That all in mind, look again at the SQL you're trying to run:

-------------------------------------------------------------------------------------------
SQL
INSERT INTO users (id, username, pass, roles, state, country, email, city)
VALUES ( '' , 'un' , 'pass' , '1234' , 'Alaska' , 'United States' , 'email' , 'city' )
-------------------------------------------------------------------------------------------

Setting a field to auto increment means that the database will supply the value itself. Your query is attempting to insert a value ('') into the id field. Now, I haven't worked directly on Access for a grateful number of years but it is the little retarded brother to SQL Server and I know if you try to insert a value into a field that is set to auto increment, it'll puke all over itself.

Try removing the id and blank value from your SQL statement and see if that runs.