Date types and MS access

Alright, I've looked around, and I couldn't come up with a solution...

I got a few fields, ones a Date/Time field. It's gotta be Date/Time.

I gotta add a post to the table, and the query goes something like this

<CFQUERY name="addPost" datasource="postDB" dbtype="ODBC">
INSERT INTO posts
            (
            Poster,
            Subject,
            Post,
            Random,
            Date
            )
VALUES
            (
            '#form.postPoster#',
            '#form.postSubject#',
            '#form.postPost#',
            '#form.postRandom#',
            #form.postDate#
            )
</CFQUERY>

and it craps out on the date. If I remove it, it works fine, but there's no date, and there's gotta be a date.

In that form its like 09/06/2004  
I've tried...

now()
CreateODBCDateTime(form.postDate)
CreateODBCDateTime(now())

with 's and without 's

and every time it tells me

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

As a test, I threw in some data, here's what it got (with the ODBC date).


SQL  
INSERT INTO posts ( Poster, Subject, Post, Random, Date )
VALUES ( 'Lord Skitch', 'test', 'test!', 'test', {ts '2004-09-06 04:07:46'} )  

What in the crap am I doing wrong?
LVL 1
LordSkitchAsked:
Who is Participating?
 
Renante EnteraSenior PHP DeveloperCommented:
Hmmmm ... :-?

How about if you replace the column name "Date" to "[Date]"?

Therefore, your insert statement will be something like this :

   INSERT INTO posts(Poster,Subject,Post,Random,[Date]) <!--- Blah... blah... blah... --->

I think column name "Date" is the root cause since this is a reserved word.

Just try.


Best wishes!
eNTRANCE2002 :-)
0
 
MauseCommented:
hi there

What is in #form.postDate#?

If this is a valid date then try this:

<CFQUERY name="addPost" datasource="postDB" dbtype="ODBC">
INSERT INTO posts
          (
          Poster,
          Subject,
          Post,
          Random,
          Date
          )
VALUES
          (
          '#form.postPoster#',
          '#form.postSubject#',
          '#form.postPost#',
          '#form.postRandom#',
          <cfqueryparam value="#form.postDate#" cfsqltype="CF_SQL_DATE">
          )
</CFQUERY>

Hope this helps

Mause
0
 
Renante EnteraSenior PHP DeveloperCommented:
Hello LordSkitch!

You should have to put single quotes on the value for the inserted date.  I would recommend you to use CreateODBCDate() function for save usage on SQL operation.

So, you will have your code like this :

<CFQUERY name="addPost" datasource="postDB" dbtype="ODBC">
INSERT INTO posts
          (
          Poster,
          Subject,
          Post,
          Random,
          Date
          )
VALUES
          (
          '#form.postPoster#',
          '#form.postSubject#',
          '#form.postPost#',
          '#form.postRandom#',
          '#CreateODBCDate(form.postDate)#'
          )
</CFQUERY>

OR You can also use <cfqueryparam> tag :

<CFQUERY name="addPost" datasource="postDB" dbtype="ODBC">
INSERT INTO posts
          (
          Poster,
          Subject,
          Post,
          Random,
          Date
          )
VALUES
          (
          '#form.postPoster#',
          '#form.postSubject#',
          '#form.postPost#',
          '#form.postRandom#',
          <cfqueryparam cfsqltype="cf_sql_date" value="#CreateODBCDate(form.postDate)#">
          )
</CFQUERY>

This should resolved your problem.


Goodluck!
eNTRANCE2002 :-)
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
LordSkitchAuthor Commented:
form.postDate has now() in it at the moment..



<cfqueryparam value="#form.postDate#" cfsqltype="CF_SQL_DATE">
returned..
SQL    
INSERT INTO posts ( Poster, Subject, Post, Random, Date )
VALUES ( 'Lord Skitch', 'test1', 'test2', 'test3', (param 1) )  


'#CreateODBCDate(form.postDate)#'
returned...
SQL    
INSERT INTO posts ( Poster, Subject, Post, Random, Date )
VALUES ( 'Lord Skitch', 'test1', 'test2', 'test3', '{d '2004-09-06'}' )


<cfqueryparam cfsqltype="cf_sql_date" value="#CreateODBCDate(form.postDate)#">
returned...
SQL    
INSERT INTO posts ( Poster, Subject, Post, Random, Date )
VALUES ( 'Lord Skitch', 'test1', 'test2', 'test3', (param 1) )  


All 3 had syntax errors. Any other ideas?
0
 
MauseCommented:
What are the errors you get?
0
 
hartCommented:
if u r using cfqueryparam then don't use createodbc date
just use
<cfqueryparam cfsqltype="cf_sql_date" value="#form.postDate#">

try this

<CFQUERY name="addPost" datasource="postDB" dbtype="ODBC">
INSERT INTO posts
         (
         Poster,
         Subject,
         Post,
         Random,
         Date
         )
VALUES
         (
         <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#form.postPoster#">
         <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#form.postSubject#">
         <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#form.postPost#">
         <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#form.postRandom#">
         <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#form.postDate#">

         )
</CFQUERY>

Regards
Hart
0
 
LordSkitchAuthor Commented:
The errors look like this...

Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.  
 
The error occurred in YouDontGetToSee/addpost.cfm: line 24
 
22 :          <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#form.postPost#">
23 :          <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#form.postRandom#">
24 :          <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#form.postDate#">
25 :
26 :          )

 

--------------------------------------------------------------------------------
 

And what that returned was this..

SQL    
INSERT INTO posts ( Poster, Subject, Post, Random, Date )
VALUES ( (param 1) (param 2) (param 3) (param 4) (param 5) )  
 
0
 
MauseCommented:
You still need the , after the cfqueryparam

VALUES
         (
         <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#form.postPoster#">,
         <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#form.postSubject#">,
         <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#form.postPost#">,
         <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#form.postRandom#">,
         <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#form.postDate#">

         )

0
 
LordSkitchAuthor Commented:
Yeah I tried that too, didnt work.. see THIS is why I'm freakin out. lol
0
 
LordSkitchAuthor Commented:
LOL I just changed the name from Date to PostDate and came here to yell at you guys that wit works... access shouldn't allow key words in field names...

Sooo I guess you get the points!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.