[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2009-07-07
9
Medium Priority
?
210 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
  • 4
  • 2
7 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 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 61

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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Strategic internal linking is often considered an SEO power technique, especially for content marketing. Do you need to hire an SEO agency to optimize you internal linking? No, this article will help you understand the basics of internal linking and…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

591 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