Solved

I want to insert the current date and time into SQL when adding to table

Posted on 2003-11-05
7
864 Views
Last Modified: 2013-12-24
I am trying to capture the date and time when data is entered into my SQL table.  This is what I currently have and it is not working.

<cfquery datasource="btadb" name="addcall">
insert into support
(accountnbr, calldate, contactid, callbackdate, subject, author, status, topicid, releaseid) values
(#form.account#, <cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp">, #form.contactid#, '#form.callbackdate#', '#form.subject#',
 #form.author#, 'open', #form.topic#, #form.release#)
</cfquery>


I am getting the error on this part

<cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp">

What am I doing wrong?
0
Comment
Question by:swartout
7 Comments
 
LVL 11

Expert Comment

by:hart
ID: 9686553
if u want current date time to be there in the field..
why not assign a default value in the field itself.

give it as getdate() as tehe default value.
and then u don't have to worry about the now()..
also the datatype should be datetime

Regards
Hart
0
 

Author Comment

by:swartout
ID: 9686574
Can you please explain further, I do not understand what you mean.
0
 
LVL 11

Accepted Solution

by:
hart earned 125 total points
ID: 9686605
i meant in ur sql database

in the design of the table

for the field calldate
u have default value option given, just put getdate() in their and everything will work fine.

what it does is the value of that field will always be the current date time or now()..
so every new record will automatically have the current date time

and also remove that field [calldate] from the insert statement  [cfcode]

Regards
Hart


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 17

Expert Comment

by:anandkp
ID: 9686671
r u sure the error is on date ... & not on the other fields ?

can u paste teh error u got & also paste the output of ur query
i have a feeling it cld be the other data in ur query as well

try this query - just make sure u have the right datatypes in the following query for all the queryparams

<CFQUERY DATASOURCE="btadb" NAME="addcall">
      insert into support
      (accountnbr, calldate, contactid, callbackdate, subject, author, status, topicid, releaseid) values
      (
      <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#form.account#">,
      <CFQUERYPARAM VALUE="#now()#" CFSQLTYPE="cf_sql_timestamp">,
      <CFQUERYPARAM CFSQLTYPE="cf_sql_numeric" VALUE="#form.contactid#">,
      <CFQUERYPARAM CFSQLTYPE="cf_sql_date" VALUE="#form.callbackdate#">
      <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#form.subject#">
      <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#form.author#">'open',
      <CFQUERYPARAM CFSQLTYPE="cf_sql_numeric" MAXLENGTH="" NULL="No" VALUE="#form.topic#">,
      <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" MAXLENGTH="" NULL="No" VALUE="#form.release#">)
</CFQUERY>

K'Rgds
Anand
0
 
LVL 1

Expert Comment

by:kjuliff
ID: 9687279
Don't double quote the date on insert.
0
 

Author Comment

by:swartout
ID: 9687344
I tried what Hart suggested and it worked.  Thanks for all of the input.
0
 
LVL 11

Expert Comment

by:hart
ID: 9691902
:-)

Regards
Hart
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 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