How to insert records in master/detail relationship
- by croceldon
I have two tables:
OutputPackages (master)
|PackageID|
OutputItems (detail)
|ItemID|PackageID|
OutputItems has an index called 'idxPackage' set on the PackageID column. ItemID is set to auto increment.
Here's the code I'm using to insert masters/details into these tables:
//fill packages table
for i := 1 to 10 do
begin
Package := TfPackage(dlgSummary.fcPackageForms.Forms[i]);
if Package.PackageLoaded then
begin
with tblOutputPackages do
begin
Insert;
FieldByName('PackageID').AsInteger := Package.ourNum;
FieldByName('Description').AsString := Package.Title;
FieldByName('Total').AsCurrency := Package.Total;
Post;
end;
//fill items table
for ii := 1 to 10 do
begin
Item := TfPackagedItemEdit(Package.fc.Forms[ii]);
if Item.Activated then
begin
with tblOutputItems do
begin
Append;
FieldByName('PackageID').AsInteger := Package.ourNum;
FieldByName('Description').AsString := Item.Description;
FieldByName('Comment').AsString := Item.Comment;
FieldByName('Price').AsCurrency := Item.Price;
Post; //this causes the primary key exception
end;
end;
end;
end;
This works fine as long as I don't mess with the MasterSource/MasterFields properties in the IDE. But once I set it, and run this code I get an error that says I've got a duplicate primary key 'ItemID'.
I'm not sure what's going on - this is my first foray into master/detail, so something may be setup wrong. I'm using ComponentAce's Absolute Database for this project.
How can I get this to insert properly?
Update
Ok, I removed the primary key restraint in my db, and I see that for some reason, the autoincrement feature of the OutputItems table isn't working like I expected. Here's how the OutputItems table looks after running the above code:
ItemID|PackageID|
1 |1 |
1 |1 |
2 |2 |
2 |2 |
I still don't see why all the ItemID values aren't unique.... Any ideas?
