[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

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?
0
LordSkitch
Asked:
LordSkitch
  • 4
  • 3
  • 2
  • +1
1 Solution
 
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 EnteraCommented:
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
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
Renante EnteraCommented:
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
 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now