CodeCharge Studio
search Register Login  

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> ASP

 [RESOLVED] Automatically Update Timestamp Field

Print topic Send  topic

Author Message
dodaniel

Posts: 43
Posted: 07/18/2016, 9:57 AM

I am testing the trial version of CCS 5.1 and would like to know how to make it update the "Last Modified" date/time field in my SQL table if anything on the record changes.
View profile  Send private message
eratech


Posts: 512
Posted: 07/19/2016, 9:58 PM

@dodaniel

I use a couple of methods but the simplest is to have a Hidden field for the 'last modified' date and in the Before Update for the field or record to 'Retrieve Value for Control' and put in the ASP function 'Now' in as the value.

You can show a Label also connected to the last modified field - CCS will automatically only update the text box type, and display only for Labels.

That will update the date if the Record is updated, even if nothing changes. I also use a simple MD5 hash of a few important fields calculated when loading the record, and before doing the Update - if the MD5 changes then set the Last modified field, otherwise don't. You aren't interested in what the MD5 is, just that it has changed.

I had another method originally to do an update using a SQL Query, but it's much simpler to use the built-in functions - and if you change the project from ASP to PHP or similar, then it's easier to change a few functions than a bunch of code.

Cheers

Eric

_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia
View profile  Send private message
dodaniel

Posts: 43
Posted: 07/21/2016, 12:33 PM

Thanks eratech!

I contacted support and have been trying to follow the example in the CCS manual for Working With Databases, After Execute Update but I am lost on what I need to change in the code to go with my database. I have no experience using the code they show. This is the code from the manual that I am inserting into the "After Excecute Update" event:
-------------------------------------------------------------------------------------------------
Function Tasks_DataSource_AfterExecuteUpdate(Sender)
Dim SQL
Dim Connection
Dim ErrorMessage

Set Connection = New clsDBConnection1
Connection.Open
SQL = "INSERT INTO report (report_task_id,report_creator) "&_
"VALUES ("& Connection.ToSQL(CCGetFromGet("task_id",0),ccsInteger) &","& Connection.ToSQL(CCGetUserID(),ccsInteger) &")"
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
Connection.Close
Set Connection = Nothing
On Error Goto 0

End Function
--------------------------------------------------------------------------------------------------------------

I assume I need to change things in the SQL= area of the code but I don't know what I need the variables to match. Any help is appreciated!
View profile  Send private message
dodaniel

Posts: 43
Posted: 07/26/2016, 8:34 AM

I'm still trying to find a solution to this if anyone can help. I just need a timestamp field to automatically update any time a record is changed or modified.
View profile  Send private message
dodaniel

Posts: 43
Posted: 07/26/2016, 8:34 AM

I'm still trying to find a solution to this if anyone can help. I just need a timestamp field to automatically update any time a record is changed or modified.
View profile  Send private message
MaFi

Posts: 45
Posted: 07/28/2016, 3:37 AM

Use MySQL to update the timestampfield.
Insert a timestampfield in your MySQL table set the default to CURRENT_TIMESTAMP and mark it ON UPDATE CURRENT_TIMESTAMP.
--snipp--
ADD COLUMN `last_change` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
--snipp--
Now MySQL updates the timestamp field everytime you do a change on the record.
View profile  Send private message
dodaniel

Posts: 43
Posted: 08/02/2016, 1:54 PM

I'm using SQL Server 2016 and ON UPDATE CURRENT_TIMESTAMP does not work.

I already have a field in my table named UpdatedTS (datetime, null) and that is the one that I'm trying to get to update anytime something changes with the record.
View profile  Send private message
dodaniel

Posts: 43
Posted: 08/02/2016, 3:07 PM

*** RESOLVED ***

I was able to get it to work by running the following in Management Studio:

CREATE TRIGGER tgr_namefortrigger
ON TableName
AFTER UPDATE AS
UPDATE TableName
SET DateFieldName = GETDATE()
WHERE UniqueKeyFieldName IN (SELECT DISTINCT UniqueKeyFieldName FROM Inserted)
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Web Database

Join thousands of Web developers who build Web applications with minimal coding.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.