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
Solved

Date types and MS access

Posted on 2004-09-06
10
422 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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

789 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