Solved

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

Posted on 2009-07-07
9
195 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
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 59

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 59

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I've been asked to discuss some of the UX activities that I'm using with my team. Here I will share some details about how we approach UX projects.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. 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.: (CODE)

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now