Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Date types and MS access

Posted on 2004-09-06
10
Medium Priority
?
430 Views
Last Modified: 2013-12-24
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
Comment
Question by:LordSkitch
[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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 10

Expert Comment

by:Mause
ID: 11988268
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
 
LVL 14

Expert Comment

by:Renante Entera
ID: 11988279
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
 
LVL 1

Author Comment

by:LordSkitch
ID: 11988960
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
Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

 
LVL 10

Expert Comment

by:Mause
ID: 11989077
What are the errors you get?
0
 
LVL 11

Expert Comment

by:hart
ID: 11989892
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
 
LVL 1

Author Comment

by:LordSkitch
ID: 11991751
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
 
LVL 10

Expert Comment

by:Mause
ID: 11993986
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
 
LVL 1

Author Comment

by:LordSkitch
ID: 11994544
Yeah I tried that too, didnt work.. see THIS is why I'm freakin out. lol
0
 
LVL 14

Accepted Solution

by:
Renante Entera earned 2000 total points
ID: 11994590
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
 
LVL 1

Author Comment

by:LordSkitch
ID: 11994658
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

Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

Question has a verified solution.

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

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…
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 …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

650 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