Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

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

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

0
JohnMac328
Asked:
JohnMac328
  • 4
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
JohnMac328Author 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
0
 
JohnMac328Author 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?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Kevin CrossChief Technology OfficerCommented:
Try it like this (add a value to submit button so that it is a defined variable on submit OR use one of the other form fields as conditional).
<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 name="updDailyNav" datasource="Daily_Nav">
               UPDATE  tbl_Daily_Nav
           SET Nav_Past = nav_now 
    
            </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" value="update"/>
  </form>
</table>

Open in new window

0
 
JohnMac328Author Commented:
Still has no effect on the nav_past field.
0
 
Kevin CrossChief Technology OfficerCommented:
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
0
 
JohnMac328Author Commented:
I caught something after I sent the last msg,  thanks for your help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now