Solved

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

Posted on 2009-07-07
9
197 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
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.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

861 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

25 Experts available now in Live!

Get 1:1 Help Now