Insert Error

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Posts: 3
Joined: Wed 26 Nov 2008 04:56

Insert Error

Post by GuyB » Wed 14 Jan 2009 05:15

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



Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 14 Jan 2009 09:46

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.

Post Reply