Skip to main content
A
Anonymous
April 25, 2012
Answered

"Syntax error in INSERT INTO statement." comment posting error.

  • April 25, 2012
  • 4 replies
  • 6841 views

I had this working perfectly on MySQL. Then I was told I needed to do it with an mdb database in Access, now errors are popping up like crazy. I fixed all except this one, I get a 'Syntax error in INSERT INTO statement.' when I try to post a comment on a post in my mini-blog site.

The thing is, I use pretty much the exact same code for the comments that I do for the blog posts themselves. The posts work fine, the comments don't.

It says the error is on my addcomment_process page on like 119. It's the following (line 119 is underlined, the bolded line is also bolded in the error message):

<cfquery name="input_comments"

                    datasource="#DSN#"

                    username="#DSNUSER#"

                    password="#DSNPASS#">

 

INSERT INTO comments(

                         commenter,

                         comment,

                         datetime,

                         post_id

                    )

               VALUES(

                         '#form.commenter#',

                         '#form.comment#',

                         '#form.datetime#',

                         '#form.post_id#'

                   )

</cfquery>

It's getting all the data fine, just not inserting it, because it says:

"INSERT INTO comments( commenter, comment, datetime, post_id ) VALUES( 'Anonymous', 'test comment.', '25-Apr-12 10:55 PM', '2' )"

It worked perfectly when it was a MySQL database, now I keep getting errors with this one part of the site now that it's MDB. Anyone know what's going on?

    This topic has been closed for replies.
    Correct answer Adam Cameron.

    datetime is a reserved word in Access.

    --

    Adam

    4 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    April 25, 2012

    I agree with Owain. In addition to what he says, the datetime value should probably not have quotes either.

    Update: Ignore. Despite this contributing nothing, my browser took too long to post it!

    A
    Anonymous
    April 25, 2012

    Thank you so much, I changed datetime to post_time and it worked. I'm downgrading because, from what I understand, the server this site is going on doesn't have the ability to use MySQL databases and it needs to be an Access database or something. At least, that's what I was told.

    Thank you both.

    Owainnorth
    Owainnorth
    Inspiring
    April 25, 2012

    I'm downgrading because, from what I understand, the server this site is going on doesn't have the ability to use MySQL databases and it needs to be an Access database or something. At least, that's what I was told.

    Joking aside, Access is not server software and should never be anywhere near a server; I'd question whoever made this decision as it's a bad one. MySQL is free and infinitely superior.

    I'm glad you fixed your issue though, but please do use params every time you enter a variable into a query.

    A
    Adam Cameron.Correct answer
    Inspiring
    April 25, 2012

    datetime is a reserved word in Access.

    --

    Adam

    A
    Adam Cameron.
    Inspiring
    April 25, 2012

    Oh, and why the heck are you downgrading from MySQL to Access??!

    --

    Adam

    Owainnorth
    Owainnorth
    Inspiring
    April 25, 2012

    Oh, and why the heck are you downgrading from MySQL to Access??!

    This is also a very good question.

    Owainnorth
    Owainnorth
    Inspiring
    April 25, 2012

    Presumably the post_id column is numeric? In which case, you shouldn't have quotes around it. Also, use queryparams.

    A
    Anonymous
    April 25, 2012

    I removed the quotes, nothing changed. I'm fairly new to this, I thought queryparams were a MySQL thing? How would I use them in this situation with mdb?

    Owainnorth
    Owainnorth
    Inspiring
    April 25, 2012

    In which case it'll be that Access has a different date format to MySQL, and you're trying to push the string "'25-Apr-12 10:55 PM" into Access, which it's not understanding. To get around this, you need to treat your variable as a true date/time object, rather than a formatted string.

    CFQueryParams should be used on absolutely every database platform, they'll stop your website being vulnerable to SQL Injection attacks for one, which at the moment you're wide open to. Replace your query with something like this:

    <cfquery name="input_comments" datasource="#DSN#" username="#DSNUSER#" password="#DSNPASS#">

          INSERT INTO comments(

                             commenter,

                             comment,

                             datetime,

                             post_id

                        )

                   VALUES(

                             <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.commenter#" />,

                             <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.comment#" />,

                             now(),

                             <cfqueryparam cfsqltype="cf_sql_numeric" value="#form.post_id#" /> )

    </cfquery>

    The QueryParams will handle the data types for you, and should pass values around more safely and with fewer issues. Assuming you just want to store the current date and time you don't even need to get ColdFusion involved, as databases have their own functions for this. I believe Access' version is simply NOW(), so I've put that in your query.

    Give the above a go.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices