Solved

Date types and MS access

Posted on 2004-09-06
10
424 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
Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

691 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