Error #42000 while deleting record from table

geekman1024 · Post by **geekman1024** » Mon 26 Jul 2010 07:42

Environment:
Win XP Professional SP3
Borland C++ Builder 6.0 enterprise
MySQL 5.0.24a (Appserv 2.5.7)
MyDAC 5.90.0.52 / 5.90.0.59

Problem:

Dropped a TMyConnection, TMyTable, TMyDataSource, TDBNavigator and a TCRDNGrid on a Form, Connection opened successfully, changed Options->Charset to GBK,
Opened a table, and linked the data to a CRDBGrid.

Table have 3 columns, 1 auto-increment index field, 2 columns accepting varchar(30) data, entered field #1, leaving field #2 blank. Later filling in field #2, facing 1st encounter of #42000, complaining syntax error in line 1 (which I don't know where it's from, since I never input any SQL code all along. Guessing it's auto generated code by the MyDAC components.).

Downloaded and installed MyDAC 5.90.0.59, error not appearing since I used phpMyAdmin to fill in Field #2, to avoid further delay in development.

After inputting a few more rows of data, decided to delete 1 of the rows using TDBNavigator, #42000 appeared again, saying:

"#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'index = 6' at line 3." (Table now contains 6 rows of data, row #6 is the data I'm trying to delete.)

The system have been behaving properly, until I started this new Application development, no changes done to my MySQL server, C++ Builder 6, MyDAC (except upgrading to 5.90.0.59), this error just popped up from no where and won't go away.

No query was entered during the whole process, so any SQL syntax error involved must be auto generated by MyDAC.

Tried to delete the data row using Table Editor (right click the TMyTable, choose Data Editor...), same error appeared.

Please help, as development has been suspended due to this error. Thanks in advance.

Dimon · Post by **Dimon** » Mon 26 Jul 2010 09:57

You can get a query that generates the error by setting the TMyQuery.Debug property to True to dispaly the statement that is being executed and all its parameters' values.
Also you can use the TMySQLMonitor component to monitor dynamic SQL execution in MyDAC based applications.
You can find more detailed information about these components in the MyDAC help.

geekman1024 · Post by **geekman1024** » Tue 27 Jul 2010 03:47

thanks for the information, problem solve. I changed the index field to some other name. I didn't realized the name 'index' is not usable as field name.

Thanks again.

Dimon · Post by **Dimon** » Tue 27 Jul 2010 09:15

You can use the 'index' name as field name, but in this case you should set the TMyQuery.Options.QuoteNames property to True to quote all field names in autogenerated SQL statements.
If there is anything else I can help you with, please contact me.