We help IT Professionals succeed at work.

Coldfusion - Value in one field to update value in another field in same table

Medium Priority
234 Views
Last Modified: 2013-12-16
A table needs to be updated in a form on a daily basis.  For example, ColA at the end of the day will become ColB.  So when the data entry starts, the data in ColA is copied to ColB and then the new data is entered for ColA.  THe first query does retrieve the records but I am having trouble getting the second query to run and have changed it to pseudocod for clarity.   When I submit, the update query does not run and copy the values from ColA to ColB.  Any help is appreciated.
<cfquery  name="getDailyNavs" datasource="Daily_Nav">
SELECT     Fund_Name, nav_now, nav_past, nav_now - nav_past AS Change
FROM         tbl_Daily_Nav
ORDER BY ID
</cfquery>
 
<cfif IsDefined("form.submitButton")>
			
		<cfquery datasource="#Daily_Nav#">
		   UPDATE  tbl_Daily_Nav
           SET nav_now = nav_past
           WHERE  (ID = ID)
		</cfquery>
		
	
</cfif>
 
 
 
 
 
 
<body>
 
<table width="600" border="1">
 
<tr>
<td>Fund</td><td>Close</td><td>Previous</td><td>Change</td>
</tr>
 
 <form method="post" preloader="no">
  <cfoutput query="getDailyNavs">
  <tr>
  
     <td width="173">#Fund_Name#</td>
     <td width="144"> <input type="text" name="nav_now" value="#dollarFormat(nav_now)#"></td>
     <td width="261"> <input type="text" name="nav_past" value="#dollarFormat(nav_past)#"></td>
  
 
   <td width="261">#DecimalFormat(change)#</td>
 
  </tr>
 
   </cfoutput>
 
 
<input type="submit" name="submitButton">
  </form>
</table>

Open in new window

Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
are you sure it is    SET nav_now = nav_past       I thought it should be Nav_Past = nav_now
also youdont have to provide the where condition if you need to update all the records

Author

Commented:
Odd, I am getting a Variable DAILY_NAV is undefined. which is my db connection.

16 :             <cfquery datasource="#Daily_Nav#">
17 :                UPDATE  tbl_Daily_Nav
18 :            SET Nav_Past = nav_now

Author

Commented:
That was me, I had the # in there.  The query works in SQL but it won't run on the page, why won't the <cfif IsDefined("form.submitButton")>
                  
            <cfquery name="updDailyNav" datasource="Daily_Nav">
               UPDATE  tbl_Daily_Nav
           SET Nav_Past = nav_now
   
            </cfquery>

run when the submitbutton is clicked?
Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Still has no effect on the nav_past field.
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Can you put an output in there to see if we are even getting to that part of the code.

<cfif IsDefined("form.submitButton")>
          <!--- debug --->
          <cfoutput>Here we are!</cfoutput>              
                <cfquery name="updDailyNav" datasource="Daily_Nav">
               UPDATE  tbl_Daily_Nav
           SET Nav_Past = nav_now
            </cfquery>
</cfif>

If we are getting in this section of code, see if the username/password for the datasource has db write capabilities or if can only read.

Regards,
kevin

Author

Commented:
I caught something after I sent the last msg,  thanks for your help.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.