Solved

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

Posted on 2009-07-07
9
201 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
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS 2013 - Finding Datasets/StoredProcedures 4 54
Making exceptions for ValidationAttribute 2 43
Display field if column exists 7 58
How come this XML node is not read? 3 53
Australian government abolished Visa 457 earlier this April and this article describes how this decision might affect Australian IT scene and IT experts.
When the s#!t hits the fan, you don’t have time to look up who’s on call, draft emails, call collaborators, or send text messages. An instant chat window is definitely the way to go, especially one like HipChat. HipChat is a true business app. An…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

751 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