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!

Here's what I just found:

If I include a statement like Today is <cfoutput>#Now()#</cfoutput> just before the <cfif #compareValues.column1# EQ #compareValues.column2#> loop begins, I see the timestamp on the second iteration.

That leads me to believe that even before the query named updateValue2 is run, the code execution has already proceeded to the Today is #Now()# statement. So it turns out there is an error in the logic flow.

How do I fix this?

This question has already been answered. Start a new discussion instead.