Error when using .locate, .post and posting records in general

Error when using .locate, .post and posting records in general

Postby Thim » Fri 03 Mar 2006 16:40


Recently I have just started using MyDAC on D7 and everything seemed fine until the time came where I wanted to post a simple record.

First I check if the field already exists using .locate. If the field exists the value is changed otherwise a new record is appended.
When the table is empty there is no problem in have the first record accepted, but when I get to the second record, which is a new record, I get the following error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FieldName3, FieldName4) VALUES (Value1, Value2, Value3, Value4)' at line 2'.
(There are 4 fields in a record and as you may notice, then field name 1 and 2 is not listed).

The same error message occurs when I try using .post with the first record .

The MySQL server is version 5 and MyDAC is ver. 4.30.

Have anybody seen this before and do you have suggestion to a solution?

Kind regards,

Postby GEswin » Fri 03 Mar 2006 22:03

You should post some more info, like table structure, and sql you're running (Also if you're using a myTable or myQuery). But my first guess is that you're putting also a SQLInsert/SQLUpdate sentence.

Also add primary keys to your tables, this will avoid you lots of problems.

And to improve more, use a Update SQL, if affectedrows is 0 then insert, this will be kicking faster than performing a locate, specially with larger db's!.
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby Antaeus » Mon 06 Mar 2006 10:34

We couldn't reproduce the problem. Please check if you set erroneous statement to UpdateSql property. If not, send us (MyDAC*crlab*com) a complete small sample to demonstrate this problem, including script to create server objects.
Posts: 2098
Joined: Tue 14 Feb 2006 10:14

Return to MySQL Data Access Components