Table.Delete , Table.Edit / Table.Post

[mparak]

Dear Friends.
I recently found that when doing a tZTable.Edit if the table had no unique fields the wrong records were updated.
If appears that the table.edit, is meant to edit the current record issues the SQL updat table where fields = values command and therein lies th eproblem.
If the table has two identical records the Table.Edit seems to get confused between them. I solved this by adding a keyed unique, auto increment field and if worked like a charm .
Any comments would be welcome and interesting.

Regards

Mparak
Durban
South Africa.

[gto]

Hello mparak!

Well, I think this behaviour is normal with tables that have no Keys, duplicated records and the property WhereMode is set to wmWhereKeyOnly.

You may try to set this option to wmWhereAll, but, creating a Key and leaving WhereMode set to wmWhereKeyOnly looks more "clear" to me. I already had problems with tables with no keys/index, when they grow big.

[]'s!

[btrewern]

First things first, all tables should have a primary key. How else should an application refer to a record in an SQL database??

In the case of a table without a primary key, for an update, ZeosLib by default produces a WHERE clause which contains all the fields of the recordset i.e.:

Code: Select all

UPDATE table1 SET field1 = 'new_date1', field2 = 'new_data2 ...
WHERE field1 = 'old_data1' and field2 = 'old_data2' and ... fieldn = 'old_datan';

As you can see this would affect all records with the same data as the current record. This also shows there may be a problem updating tables like this with floating point fields as no records may be updated.

Moral of the story - always have a primary key in your tables.

Regards,

Ben