SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    May 2009
    Location
    Baltimore, MD
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Data Update Fails

    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 first 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 fills the form a 3rd time. Basically, the 3rd set is the one that is critical and the one that will be used for further calculations.

    My code fails at the comparison + Auto-Update stage. i.e. when the 2 values match, the 3rd value isn't automatically entered. Anyone know what I am missing?

    Here's my code:
    -------------------------------------------------------------------------------
    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#>
                         <cfquery name = "autoUpdateValue3">
    		          UPDATE	tableName
    			  SET	column3 = #compareValues.column2# 
    			  WHERE	ID = #URL.ID#
                         </cfquery>
    		</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!
    Last edited by sql_n00b; May 20, 2009 at 05:39.

  2. #2
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I hate to suggest this, because it's so simple that it's probably not it, but have you tried single quotes around your values?

    Code:
    SET	column1 = '#FORM.field#'
    WHERE	ID = '#URL.ID#'
    And you know to use <cfqueryparam> on the final version, right? Once you add the <cfqueryparam>, you likely won't need to bother with the quotes because it seems to add them for you. At least it does for me.

  3. #3
    SitePoint Member
    Join Date
    May 2009
    Location
    Baltimore, MD
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for replying cydewaze. Yeah, I am using <cfqueryparam> on the final version. I didn't use it here just to keep things uncomplicated. Also, since the value that is to be entered in the DB is a number (float), I did not use single quotes.

    The AUTO-UPDATE when Value 1 = Value 2 is the single point of failure in the code. The code works fine otherwise.

    Any other suggestions to point to where I might be screwing things up?

  4. #4
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    How come that 4th query doesn't have a <cfquery> tag around it?

  5. #5
    SitePoint Member
    Join Date
    May 2009
    Location
    Baltimore, MD
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cydewaze View Post
    How come that 4th query doesn't have a <cfquery> tag around it?
    Ooops, sorry about that!
    I have fixed my original post now.

  6. #6
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks, that makes it easier to see where you're having the problem. I'm wondering if the problem is in your cfelseif statement and how it's handling the null value.

    Maybe try checking against "" instead of null?

  7. #7
    SitePoint Member
    Join Date
    May 2009
    Location
    Baltimore, MD
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cydewaze View Post
    Thanks, that makes it easier to see where you're having the problem. I'm wondering if the problem is in your cfelseif statement and how it's handling the null value.

    Maybe try checking against "" instead of null?
    Oh, 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?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •