ColdFusion

Data Update Fails

Please support our ColdFusion advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Apr 2009

Posts: 16

Reputation: SQL_n00b is an unknown quantity at this point

Solved Threads: 1

SQL_n00b

Offline

Newbie Poster

Data Update Fails

May 19th, 2009

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:
-------------------------------------------------------------------------------

 Help with Code Tags 
 ColdFusion Syntax (Toggle Plain Text) 
<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>
<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!

•