Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2003-11-05
7
Medium Priority
?
870 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 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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Screencast - Getting to Know the Pipeline
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month6 days, 13 hours left to enroll

782 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