#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Posts
    6
    Rep Power
    0

    Query error question


    I am trying to develop a website using Cold Fusion and am currently working on some administrative pages. I've built a page with a textarea in which I can type an SQL statement and a submit button to execute the SQL.

    This process works fine for any query that contains only numerical criteria. But, if I apply any kind of text-related criteria I get database errors.

    Thus,
    SELECT * FROM Team WHERE [ID]>23 ORDER BY [AgeGroup], [Name];
    works perfectly, but

    UPDATE Team SET [City]='MyTown' WHERE [ID]=29;
    results in :
    Message: ODBC Error Code = 37000 (Syntax error or access violation)
    Detail: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '''MyTown'''.

    Any ideas?

    TIA,
    Eric
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,307
    Rep Power
    972
    Try removing the brackets around the field names, they shouldn't be necessary.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Posts
    6
    Rep Power
    0
    Originally Posted by kiteless
    Try removing the brackets around the field names, they shouldn't be necessary.
    I've done that...I've also tried using both single- and double-quotes, all result in an error.
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,307
    Rep Power
    972
    Can you show the SQL and the error that is generated once you are running it without the brackets? Also, remove the semicolons...CF automatically terminates the statement with a semicolon.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Posts
    6
    Rep Power
    0
    Originally Posted by kiteless
    Can you show the SQL and the error that is generated once you are running it without the brackets? Also, remove the semicolons...CF automatically terminates the statement with a semicolon.

    This is the SQL I type into the textarea:
    UPDATE Team SET City = 'SomePlace' WHERE ID = 9

    This is the resulting error:

    DATABASE Error
    An error occured executing the following:

    UPDATE Team SET City = 'SomePlace' WHERE ID = 9


    Error details:

    Message: ODBC Error Code = 37000 (Syntax error or access violation)

    Error code: -3100
    SQLState: 37000
    Detail: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '''SomePlace'''.
  10. #6
  11. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,307
    Rep Power
    972
    And you can run this query:

    UPDATE Team SET City = 'SomePlace' WHERE ID = 9

    From inside Access and it works?
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Posts
    6
    Rep Power
    0
    Originally Posted by kiteless
    And you can run this query:

    UPDATE Team SET City = 'SomePlace' WHERE ID = 9

    From inside Access and it works?

    Yes, that is correct and why I am baffled.

    This also works just fine:
    UPDATE Team SET Wins = 5 WHERE ID = 9

    I only get these errors when I attempt to set values or criteria on string data, so a query like

    SELECT * FROM Team WHERE State = 'WA'

    yields the following:

    DATABASE Error
    An error occured executing the following:

    SELECT * FROM Team WHERE State = 'WA'


    Error details:

    Message: ODBC Error Code = 37000 (Syntax error or access violation)

    Error code: -3100
    SQLState: 37000
    Detail: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'State = ''WA'''.
  14. #8
  15. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,307
    Rep Power
    972
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Posts
    6
    Rep Power
    0
    Originally Posted by kiteless
    Does it want double quotes possibly?
    Nope, tried that, get the same result with either.
  18. #10
  19. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,307
    Rep Power
    972
    Try hard coding the SQL...so instead of trying to use a form variable, do this:

    <cfquery name="blah".....>
    UPDATE Team SET City = 'SomePlace' WHERE ID = 9
    </cfquery>

    Does that work?

    I'm at a loss at this point, because I've never had such a problem...obviously CF *does* allow you to do database UPDATES, INSERTS, or any other valid SQL operation, so there must be something wrong somewhere in your specific environment, I just can't tell what it is.
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Posts
    6
    Rep Power
    0
    Originally Posted by kiteless
    Try hard coding the SQL...so instead of trying to use a form variable, do this:

    <cfquery name="blah".....>
    UPDATE Team SET City = 'SomePlace' WHERE ID = 9
    </cfquery>

    Does that work?

    I'm at a loss at this point, because I've never had such a problem...obviously CF *does* allow you to do database UPDATES, INSERTS, or any other valid SQL operation, so there must be something wrong somewhere in your specific environment, I just can't tell what it is.
    Yes, that works...so that means, what? The textarea somehow modifies the ' mark, so that when it gets passed to the action template it is not processed as a ' ? If that's the case I can't immediately think of a work-around.
  22. #12
  23. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,307
    Rep Power
    972
    Form data is always URL encoded. You could try using urlDecode() to force it back.

IMN logo majestic logo threadwatch logo seochat tools logo