using Compare a form input to a DB query issue. Please help

[Irish-Phoenix]

Hello;

I'm trying to make a sign up form for customers, I have written the code, I have a similar version working in a log in, so I tweeked it out to work with server sided form validation and even if there is not a record in the database, it throws the error and says it does exist. I'm only placing the code in for the query, a snipet. This is what I've done:

<cfset error = "">

<cfif structKeyExists(form, "sendcomments")>

<cfif not len(trim(form.clName))>
<cfset error = error & "<p>Your Full Name!</p>">
</cfif>

<cfquery name="qVerify" datasource="#APPLICATION.dataSource#">
   SELECT  ContactID, shipEmail
   FROM Orders
   WHERE shipEmail = <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.clEmail#">
</cfquery>
<cfset comparison = Compare(FORM.clEmail, qVerify.shipEmail)>
<cfif comparison eq 1>
<cfset error = error & "<i>This EMAIL Address is already in our records!<br> Please go back to the log in, and use our password retrevial system.</i>">
</cfif>

... <!--- more code here, I didn't want to get into it all --->

</cfif>

I can't figure out why this doesn't work properly. Can someone help me or is there even a site with a tutorial out there on something like this that I haven't found? I went to live docs, and was reading about query of queries and that isn't really what I'm trying to do here. I'm trying to compair a form input with what is in the database.

Please help... frustrated at this point.

Thank you.

[ilssac]

I don't think you are using the compare function correctly.  From the documentation. "If the two strings are equal, it returns zero. If the first string is  "less than" the second string, it returns -1. If the first string is  "greater than" the first, it returns 1"

Since you are most likely getting a result that looks something like compare("myEmail@myServer.com",""):  since "myEmail@myServer.com" is going to be greater then an empty string "" it will result in a compare value of 1.

You then test for one, and provide a result that indicates that the value where actually the same.

Message was edited by: Ian Skinner P.S.  I don't suppose there is anyway I can tell this double cursed forum software not to put silly little envelopes next to my sample and bogus email strings is there?

[Irish-Phoenix]

so I need to use -1 instead of 1? Or do I go for 0 and leave the cfif compairison to look for -1?

Sorry, trying to wrap my head around this...

[ilssac]

What do you want to know?

That the first string is less then the second string (-1) OR that the two strings are the same (0) OR that the first string is greater then the second string(1).

And, just for a good exercise, why are you using the compare function for this requirement?

[Irish-Phoenix]

I'm trying to compare what the user puts into the form as their new account to the records in the database, this

is to avoid duplicate records and accounts in the site, so if the (in this case) email address is matched in the database, it

makes you either go log in, or create a totally new account.

I also had it using 0 and it was still telling me there was a record in the database with the same email address and there isn't. So I went to 1 and it did the same thing.

So obviously, I'm doing something wrong.
Is there a better way to do this.

[ilssac]

Irish-Phoenix wrote:

So obviously, I'm doing something wrong.
Is there a better way to do this.

Answer one. 

If you just want to test the equality of two strings. I would probably just use an equality operator, i.e. (EQ or EQUALS).

<cfif form.aField EQ dataQry.aColumn>

Answer two.

When code is not behaving like one expects.  It is a good idea to dig into the running applicaiton and see what it is actually doing.  One example of something you could have tried.

<cfoutput>-:#FORM.clEmail#:- -:#qVerify.shipEmail#:-  : #Compare(FORM.clEmail, qVerify.shipEmail)#</cfoutput>

You may notice my extra characters (-: :-).  I like to put stuff like that around strings to clearly show if a string is empty or contains whitespace.  Which is not always clear in HTML output.

[Irish-Phoenix]

ok, this is working! THANK YOU

One last question, when I have the user fill out the proper info, I insert them into the database and create a session string, right now I'm getting an error, am I doing this properly? Here is that part:

<cfquery datasource="#APPLICATION.dataSource#" dbtype="ODBC">
INSERT INTO Orders
(CreditName, ShipAddress, ShipCity, ShipState, shipZip, shipEmail, password)
VALUES (<cfqueryparam cfsqltype="cf_sql_varchar" value="form.clName">,
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.clAdd#">,
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.clCity#">,
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.clState#">,
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.clZip#">,
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.clEmail#">,
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.f1#">)
</cfquery>
<cflock scope="Session" type="EXCLUSIVE" TIMEOUT="20">
<cfset SESSION.uscl = structNew()>
<cfset SESSION.uscl.isLoggedIn = "Yes">
<cfset SESSION.uscl.clName = IsValidUser.clName>
</cflock>

this is my error:

Element CLNAME is undefined in ISVALIDUSER.

The error occurred in C:\Websites\187914kg3\accManage\signUp.cfm: line 158 Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 92 Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 1

156 : <cfset SESSION.uscl = structNew()> 157 : <cfset SESSION.uscl.isLoggedIn = "Yes"> 158 : <cfset SESSION.uscl.clName = IsValidUser.clName> 159 : </cflock> 160 :

Should I be using the database user that was just inserted? how do I do this so that "if they are logged in" I can have my other code show
Welcome: myemail@mydomain.com

in all the areas of the site... like it does for this forum actually.

[Dan_Bracuk]

That type of error message is slightly ambiguous.  It could mean the structure doesn't exist or it could mean that the structure exists, but that particular key does not.  Your action is the same in either case.  Find out where you think you created that variable and see if you can figure out why you didn't.

[ilssac]

Well the error message is telling the world that "IsValidUser.clName" is not a valid variable.

Your code example shows me nothing about where that variable might be comming from.

So your task would be to track down why you think "isValidUser.clName" is a good variable and why the ColdFusion server disagrees with you.

[Irish-Phoenix]

that's what I thought... I'll get back to you on this part. Let me mess around with a couple of different angles of creating this...

Thank you for all the help so far. You have no idea how much I appreciate it.

[JR__Bob__Dobbs]

You could query the database to see if any Order records have the same email as the form.  If a record exists display your error message.

<!--- use TOP 1 here (if supported by your database software) so you only get one record back, not every match --->
<cfquery name="qVerify" datasource="#APPLICATION.dataSource#">
   SELECT TOP 1 ContactID, shipEmail
   FROM Orders
   WHERE shipEmail = <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.clEmail#">
</cfquery>

<cfif qVerify.recordCount gt 0>
     <!--- record already exists for this email address --->
</cfif>