?
Solved

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

Posted on 2003-11-05
7
Medium Priority
?
868 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 500 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses

770 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