Can someone please help me figure out what I am doing wrong? Basically I have a form where users enter a bunch of values. On submit, the form values are dumped into a table in the DB.
The process is as follows: Users enter a set of values TWICE for the same ID using the same form. If the frist two values match, then a 3rd set is auto-updated (the user doesn't have to hand-enter the values this time; the UPDATE of the DB is to be done automatically). If they do not match, then the user sees the form a 3rd time. Basically, the 3rd set is the one that is critical and one that will be used for further calculations.
My code fails at the comparison + Auto-Update stage. i.e. if the 2 values match, the 3rd value isn't automatically entered. Anyone know what I am missing?
Here's my code:
-------------------------------------------------------------------------------
<cfquery name = "getValues"> <!--- Get required values for this ID --->
SELECT column1, column2, column3
FROM tableName
WHERE ID = #URL.ID#
</cfquery>
<cfoutput query = "getValues">
<cfif column1 EQ "">
<cfquery name = "updateValue1">
UPDATE tableName
SET column1 = #FORM.field#
WHERE ID = #URL.ID#
</cfquery>
<cfelseif column2 EQ "">
<cfquery name = "updateValue2">
UPDATE tableName
SET column2 = #FORM.field#
WHERE ID = #URL.ID#
</cfquery>
<cfquery name="compareValues"> <!--- Compare Values 1 and 2 --->
SELECT column1, column2
FROM tableName
WHERE ID = #URL.ID#
</cfquery>
<cfif #compareValues.column1# EQ #compareValues.column2#>
UPDATE tableName
SET column3 = #compareValues.column2#
WHERE ID = #URL.ID#
</cfif>
<cfelseif ((column3 EQ "NULL") OR (column1 NEQ column2))>
<cfquery name = "updateValue3">
UPDATE tableName
SET column3 = #FORM.field#
WHERE ID = #URL.ID#
</cfquery>
</cfif>
</cfoutput>
-----------------------------------------------------------------------------
BTW, the reason behind entering the data multiple times for the same ID is to make sure we have the correct values. The idea being that the at least one of the 3 values will be correct. If anyone has a better algorithm for achieving this, please let me know.
FYI, I am using CF MX7 and SQL Express 2005.
TIA!
Marked Solved 
