Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update date

Status
Not open for further replies.

GUJUm0deL

Programmer
Jan 16, 2001
3,676
US
Hiya ppl...
This is what I wanna do, and i'm looking to see if there is an easy way...
PROBLEM: I have a form that can be updated by an admin...I already have the admin name counted for, but I also need to track the date the form was updated...how can I do this, as simply as possible??
I have a field declared in the dB called update_date... I have not failed; I have merely found 100,000 different ways of not succeding...
 
as simply as possible? i would definitely say that you should use the update_date field for this purpose

just update it to whatever the current date function is in your database, in the same update sql statement as you're executing now

in other words, don't do it with cf's date, do it with the database's date

rudy
 
<cfquery datasource=&quot;&quot;>
update mydata
set
last_updated = '#dateFormat(now())#',
..............
.................
</cfquery>
 
OK, I got a way to do the update_date, but for some reason it doesn't write to the dB...
This is what I have done:
On the form.cfm page
<!--- in the <head> --->
<SCRIPT>
thisDay = new Date();
var wilMonths;
var wilDate;

function todaysDATE() {
setTimeout(&quot;todaysDATE()&quot;,1000);
wilMonths = new Array(&quot;01&quot;,&quot;02&quot;,&quot;03&quot;,&quot;04&quot;,&quot;05&quot;,&quot;06&quot;,&quot;07&quot;,&quot;08&quot;,&quot;09&quot;,&quot;10&quot;,&quot;11&quot;,&quot;12&quot;);
wilDate = new Array(&quot;0&quot;,&quot;1&quot;,&quot;2&quot;,&quot;3&quot;,&quot;4&quot;,&quot;5&quot;,&quot;6&quot;,&quot;7&quot;,&quot;8&quot;,&quot;9&quot;,&quot;10&quot;,&quot;11&quot;,&quot;12&quot;,&quot;13&quot;,&quot;14&quot;,&quot;15&quot;,&quot;16&quot;,&quot;17&quot;,&quot;18&quot;,&quot;19&quot;,&quot;20&quot;,&quot;21&quot;,&quot;22&quot;,&quot;23&quot;,&quot;24&quot;,&quot;25&quot;,&quot;26&quot;,&quot;27&quot;,&quot;28&quot;,&quot;29&quot;,&quot;30&quot;,&quot;31&quot;,&quot;1&quot;);

document.po.update_date.value = wilMonths[thisDay.getMonth()]+&quot;/&quot; +wilDate[thisDay.getDate()]+&quot;/&quot;+thisDay.getFullYear()
}
</SCRIPT>

<!--- in the <body> --->
<body onLoad=&quot;return todaysDATE();&quot;>


<!--- right after the submit button --->
<input type=&quot;hidden&quot; name=&quot;update_date&quot;>
this is because I don't want to display the date on the screen



On the action.cfm page
<!--- as the CFUPDATE --->
<cfupdate datasource=&quot;dB_name&quot; tablename=&quot;table1&quot; formfields=&quot;update_date&quot;>

<!--- as the CFUPDATE --->
And i'm passing the 'update_date' value as: <input type=&quot;hidden&quot; name=&quot;update_date&quot;>




Now, on the action.cfm page I have the 'update_date' date shown on the screen as:
Code:
<tr> 
  <td width=&quot;173&quot;><b><font size=&quot;2&quot;>Update Date:</font></b></td>
  <td width=&quot;1&quot;></td>
  <td width=&quot;128&quot; valign=&quot;middle&quot; align=&quot;right&quot;></td>
  <td width=&quot;4&quot; valign=&quot;middle&quot;></td>
  <td width=&quot;264&quot;><b><font size=&quot;2&quot; color=&quot;003399&quot;>#update_date#</font></b></td>
</tr>


even though it shows on th screen the update_date date, it will not write to the dB...why??
What am I missing?? I have not failed; I have merely found 100,000 different ways of not succeding...
 
okay, see, i can't help you, because i don't do CFUPDATE

what you have found it necessary to do, and painfully if i may say so, is jump through hoops to declare a hidden field with a date in it

furthermore, you're using javascript, so right away there's the problem of what happens for users who have javascript turned off

my suggestion involved using an sql statement telling the database to use the database's current date function, instead of passing the date in as a form field value

it's much neater, simpler, and more obvious, except that i think you would have to give up CFUPDATE and use CFQUERY instead

but writing your own UPDATE sql statement inside a CFQUERY is, to me anyway, better, because you can see exactly which fields are getting updated

rudy
 
r937, so can I use this query, exactly as written (using object's suggestion):


<cfquery name=&quot;update&quot; datasource=&quot;dB_name&quot; tablename=&quot;table1>
update update_date
set update_date = '#dateFormat(now())#',
</cfquery>


And call that query like this:

<tr>
<td width=&quot;173&quot;><b><font size=&quot;2&quot;>Update Date:</font></b></td>
<td width=&quot;1&quot;></td>
<td width=&quot;128&quot; valign=&quot;middle&quot; align=&quot;right&quot;></td>
<td width=&quot;4&quot; valign=&quot;middle&quot;></td>
<td width=&quot;264&quot;><b><font size=&quot;2&quot; color=&quot;003399&quot;><cfoutput name=&quot;update&quot; #update_date#</cfoutput></font></b></td>
</tr>
I have not failed; I have merely found 100,000 different ways of not succeding...
 
yeah, that's almost the right syntax for the CFQUERY UPDATE, but where are the other fields being updated?

seems kind of pointless to go in and update a row and set its update_date value, when nothing else in the row has changed

the CFQUERY tag does not take a table name parameter, you have to specify that in the UPDATE statement

rudy
 
hmmm, is this any better??

<cfquery name=&quot;update&quot; datasource=&quot;dB_name&quot;>
update table1
set update_date = '#dateFormat(now())#',
name = #name#,
address = #address#,
state = #state#,
zip = #zip#
</cfquery>
I have not failed; I have merely found 100,000 different ways of not succeding...
 

beautiful, except for three things :)

for the update_date column, don't use a coldfusion function, use a database function (this is what i was trying to say in my first reply)

Code:
   set update_date = sysdate()

you will have to substitute your datebase's current date function -- date() for access, getdate() for sql/server, etc.

secondly, make sure you surround character values with quotes, e.g.

Code:
   address = '#address#'

finally, if you run the UPDATE statement as is, it will update every row in the table with those values

you need to pick which row is being updated using a WHERE clause, e.g.

Code:
   where tablepk = #adminid#

rudy
 
r937, guess what?? I found out what the problem is with the code...and its sooo simple!!
I found out that the problem was with my field name, I used update_date, in CF the _date is used to mark fields that will be used in server-side validation, just like _required, _integer, etc...
CFUPDATE and CFINSERT ignore fields that end in these validation suffixes. So all I did was rename the field name and it worked...but you're idea is also filed away for future use...
[smile]

PS -- I found this out from the macromedia coldfusion site... lol I have not failed; I have merely found 100,000 different ways of not succeding...
 

cool, thanks for the followup, that's good to know
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top