Devart Forums

Hello
I have a master detail table setup
Master Table has the following fields
(id integer not null, <- part of Primary key
nr integer not null, <- part of Primary key
......

Detail Table has
(fk_id integer not null,
fk_nr integer not null,
nr integer not null,
...

The first two fields are used in a foreign key relationship to master table
and the nr in the detail table is to enforce uniqueness within this table.

The form has 2 TMSQuery components linked via the mastersource.
When i insert a new record in the master table and immediately commence inserting records in the detail table, each time i insert a record i run a function from the queries newrecord method which basically executes select max(nr) from detail table where fk_id=?? and fk_nr=?? and then rerturns retrieved value + 1.

The problem is that the function cannot see any records for the detail table at this stage and therefore always returns 1 which causes a violation of the primary key. Is there a setting that i should be setting to resolve this or is there a problem with my approach.

By the way i ensure that any updates to the master table have been applied (using applyupdates) before executing the statements in new record method


Regards

GuyB

The optimal solution is to set the 'nr' field of the detail table as primary key and autoincrement field.
If it does not go to you, then try to compose a small sample to demonstrate the problem and send it to dmitryg*devart*com.