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

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.

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'''.

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'''.

Does it want double quotes possibly?

Nope, tried that, get the same result with either.

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.

Form data is always URL encoded. You could try using urlDecode() to force it back.