Skip to main content
T
terrymod
Participant
December 31, 2008
Question

MYSQL error on CFINSERT / CFUPDATE

  • December 31, 2008
  • 3 replies
  • 1263 views
Converted from Access to MYSQL 5 and have 4 tables giving same error: when I try to use
I have tried to check the table configurations and they seem ok.
<CFINSERT> <CFUPDATE>
Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax ...

SQLSTATE 42000
SQL insert into project_specs

I have looked all over the web for help.

Any help greatly appreciated.

TerryMOD

This topic has been closed for replies.

3 replies

T
terrymodAuthor
Participant
December 31, 2008
I will try a sql insert and start with one or two fields at a time and check my errors.

Many thanks,

Terry
-
-__cfSearching__-
Inspiring
December 31, 2008
terrymod wrote:
> the only one I can think of is QTY / which is not in the reserved list in MYSQL

According to the MySQL documentation "Option" is a reserved word. Though personally I would still recommend using a regular insert/update over cfinsert/cfupdate

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html
A
Anonymous
December 31, 2008
I always do regular SQL inserts & updates, as almost always I end up doing a <CFIF> inside the SQL, or referencing Session variables, or something that goes beyond the CFINSERT & CFUPDATE. That should solve the problem, or at least show you more clearly where the problem is.
-
-__cfSearching__-
Inspiring
December 31, 2008
terrymod wrote:
> Converted from Access to MYSQL 5 and have 4 tables giving same error:

That is one of disadvantages of cfinsert/cfupdate. Error messages are often a bit more cryptic than with regular sql. I assume the insert/updates actually worked with MS Access?

Things I would check are:
1) Are any of the column or table names reserved keywords in MySQL?
http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

2) Turn on debugging and look at the actual sql that was generated. It might be easier to spot the problem.

3) Try converting the cfinsert to a regular insert.

T
terrymodAuthor
Participant
December 31, 2008
Many thanks for your reply.
1) Yes , all these worked great in access.
2) Table column names: I looked at reserved names in MYSQL

the only one I can think of is QTY / which is not in the reserved list in MYSQL

I have other tables with the same formats and they work. It is strange - I can use retrieve last record in some tables , but not in others with the same CF code.

I think my tables either have a column name / column type ( ie = integer) not set up to take the data in.

It seems like the sql insert works right up to the 9 th field based on the error.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices