• Registered: 2017-06-30
  • Posts: 3

Topic: Adding Current DateTime to database when updating an entry

I am trying to add the current DateTime to database field when updating an entry. I originally tried to do it with a date time picker but it wouldn't automatically pull the current datetime. So I switched to trying my hand a scripting. I wrote this one here with the help of many other articles but after I close the form it updates all entries in my database not just the one I am working on. I know I am missing something but I am not super knowledgeable about Delphi. I know it must be missing a WHERE statement or some var function, but I cannot figure it out. Any help will be greatly appreciated.

My code:
procedure frmPatient_OnClose (Sender: string; Action: string);
begin
SQLExecute('UPDATE patient SET date_updated = "' + FormatDateTime('yyyy-mm-dd hh:mm:ss.zzz', Now) + '" ');
end;

begin
end.

  • Registered: 2014-06-25
  • Posts: 863

Re: Adding Current DateTime to database when updating an entry

Depending on your application, it could be an update or an insert in your "date_updated" field. Normally, this field should be unique and an update date for each update should be recorded if you want to stick with best practices and traceability

Inserting the date:


procedure frmPatient_OnClose (Sender: string; Action: string);
begin
sCurrentDate := '"' +  FormatDateTime('yyyy-mm-dd hh:mm:ss', now) + '"';
SQLExecute ('INSERT INTO patient (date_updated) VALUES ('+sCurrentDate+')');
end;

if you really want to replace the value, I would suggest the feature "REPLACE"

Updating the date:

procedure frmPatient_OnClose (Sender: string; Action: string);
begin
sCurrentDate := '"' +  FormatDateTime('yyyy-mm-dd hh:mm:ss', now) + '"';
SQLExecute ('REPLACE INTO patient (date_updated) VALUES ('+sCurrentDate+')');
end;

3 Reply by senorlocksmith (edited by senorlocksmith 2017-07-01 01:49:06)

  • Registered: 2017-06-30
  • Posts: 3

Re: Adding Current DateTime to database when updating an entry

I tried replacing the script however it comes back with an undeclared identifier sCurrentDate. How do I need to declare that? I have decided to upload my program to see if anyone could figure this out. Maybe I am just doing it wrong.

Any advice is greatly welcomed.

Post's attachments

Attachment icon Bill.zip 345.84 kb, 588 downloads since 2017-07-01 

4 Reply by derek (edited by derek 2017-07-01 10:59:48)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,674

Re: Adding Current DateTime to database when updating an entry

Hi Senorlocksmith,
Your script is fine - I think I would just add  a condition and a where clause.  There are probably other ways to achieve it too but this seems the most straightforward way to me.
Please have a look at the attachment and see if this is the sort of thing that you are trying to do.
Hope this helps,
Derek.

Post's attachments

Attachment icon Bill.zip 349.91 kb, 646 downloads since 2017-07-01 

5 Reply by tcoton (edited by tcoton 2017-07-01 13:42:29)

  • Registered: 2014-06-25
  • Posts: 863

Re: Adding Current DateTime to database when updating an entry

My bad... sorry, I forgot one part in the script... to declare the variable. I usually do that on top of the script as global variables:

Insert:

procedure frmPatient_OnClose (Sender: string; Action: string);
var
sCurrentDate: string;
begin
sCurrentDate := '"' +  FormatDateTime('yyyy-mm-dd hh:mm:ss', now) + '"';
SQLExecute ('INSERT INTO patient (date_updated) VALUES ('+sCurrentDate+')');
end;

Replace:

procedure frmPatient_OnClose (Sender: string; Action: string);
begin
var
sCurrentDate: string;
sCurrentDate := '"' +  FormatDateTime('yyyy-mm-dd hh:mm:ss', now) + '"';
SQLExecute ('REPLACE INTO patient (date_updated) VALUES ('+sCurrentDate+')');
end;
  • Registered: 2014-06-25
  • Posts: 863

Re: Adding Current DateTime to database when updating an entry

@senorlocksmith

I actually tried your version and there is no issue whatsoever with your application as is. Which version of MVDB are you using?

  • Registered: 2015-11-13
  • Posts: 652

Re: Adding Current DateTime to database when updating an entry

Thought I would add my two cents worth. Attached is another way and does not require SQL. This is of course considering I understand what you are trying to do. In the attached project, the form fields are assigned to the table fields and they are set to the current date and time before saving. I noticed that there were two dates; one for the date entered and one for date updated. The attached takes care of both.

Post's attachments

Attachment icon Bill Another Way.zip 594.57 kb, 717 downloads since 2017-07-02 

  • Registered: 2017-06-30
  • Posts: 3

Re: Adding Current DateTime to database when updating an entry

Derek and ehwagner

Both of your versions worked wonderfully. Thank you so much. Its interesting to see where I went wrong with the scripting and how the few changes can make a big difference.

Thanks again so much!