I love your tutorials but I noticed this only works if you insert new records in your database. How about if an existing record gets updated? how do I insert the date when an existin g record gets updated? Thanks for the great tutorial
By: Pablo Varando
USING YOUR DATABASE TO DO THE WORK
A lot of people have asked me "what can I do to make the database work for me, instead of ColdFusion?". The truth is that any veteran developer will tell you that making the database do the work, is the best practice in web development. I can think of a few different ways to make the database work for you. By this I mean telling the database to insert values, create tables and much more. In this tutorial I will show you the basics of how to make your database do the work for you! let's begin.
In this example we will be using an access database to have the current date and time be automatically inserted, when a new record comes in. Traditionally you had to have ColdFusion insert the date with the command #CREATEODBCDateTime#, but making the database do the work is much faster and way more reliable. Let's show you how it's done..
The first thing you will need is a date/time
field in your table. call this field:
PostDateTime
Set the value of the field to be Date/Time (for
more see picture below).
Notice that the field is called PostDateTime and the data type is set to
Date/Time. The next thing we need to do is tell this field to
automatically place the current date/time when a new record is inserted into the
database, to achieve this you simply make the following addition to this
field:
Notice that in the default value, I simply added the command Now(),
this let's the database know that when a record is inserted that it should
automatically add the current date/time. Let's see the end result within the
database.
Notice that even though there isn't a value in "Name",
the date time field is automatically there. If you close and reopen the
table, the date changes to the current date. So when a record is inserted
it keeps that date and you can therefore use it to display on your page with the
DateFormat and TimeFormat functions.
This is just a simple way of making the database do the work for you, instead of making ColdFusion (or whatever other language you use) do extra work to achieve the same result.
Congratulations, you now know how to make the database do the work, instead of your development language.
About This Tutorial
Author: Pablo Varando
Total Views: 55,992
Submission Date: August 08, 2002
Last Update Date: June 05, 2009
All Tutorials By This Autor: 47
Skill Level: Beginner
Platforms Tested: CF5Total Views: 55,992
Submission Date: August 08, 2002
Last Update Date: June 05, 2009
All Tutorials By This Autor: 47
Discuss This Tutorial
-
-
I understand setting date/time functions on the database (for those who don't know you can also use DATE() and TIME()), but what do I do for "subforms" aka using either URL info from master details page or the filtered ID from one form/query to populate another? This has been driving me mad for about a month of searches now (Old time Access guy learning new ColdFusion tricks). Thanks!
-
The Now() function does not work in SQL Server. Instead use GetDate(). This is the SQL Server equivalent.
-
hello mr i work on developing a hotel reservation system for a hotel through a website using CFML I want u to help me on this project
-
Kinda have same problem as James, Im using MS SQL 2000 which will not let you use the Now() function as the default value. Is there a work around?
-
What if I wish to insert ONLY the Date or Time? What would I put in the Default Value? I use SQL Server 2000 by the way. Thanks!
Advertisement
