Solved

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

Posted on 2009-07-07
9
203 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

0
Comment
Question by:JohnMac328
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24795173
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
 

Author Comment

by:JohnMac328
ID: 24795211
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
 

Author Comment

by:JohnMac328
ID: 24795392
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 24807558
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
 

Author Comment

by:JohnMac328
ID: 24807641
Still has no effect on the nav_past field.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24807730
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
 

Author Closing Comment

by:JohnMac328
ID: 31600630
I caught something after I sent the last msg,  thanks for your help.
0

Featured Post

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question