asked on

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?

Mause

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

Renante Entera

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 :-)

LordSkitch

ASKER

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?

Mause

What are the errors you get?

hart

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

LordSkitch

ASKER

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) )

Mause

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#">

)

LordSkitch

ASKER

Yeah I tried that too, didnt work.. see THIS is why I'm freakin out. lol

ASKER CERTIFIED SOLUTION

Renante Entera

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LordSkitch

ASKER

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!