Solved

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

Posted on 2003-11-05
7
866 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 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

707 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