Autoincrementing in a DBG

[Helmut Scheiss]

This is kind of related to my last question about adding new rows into a DBG. When I load the master-detail grids with existing data from the database, the grid will always set the PK column with a value of the last row + 1. Example (all master records have a QAID value of 380):

Master Table:

Rec 1
QAID: 380
PKID: 100

Rec 2
QAID: 380
PKID: 101

Rec 3
QAID: 380
PKID: 102

If I add a new row to the grid, it will assign a PKID value of 103. Any detail records I add to the detail grid will be related via PKID, so they too have a value of 103. When I do a dataset update, the newly inserted master rows are reassigned a true identity value from the database. This causes the relationship to the detail records to break and they do not update. Example:

Master-
(Add new row)
QAID:380
PKID:104
Detail-
(Add new row)
PKID:104
Desc:Line 1
(Add new row)
PKID:104
Desc:Line 2

After the update, the master record with original value of PKID=104 might now be PKID=300. The detail lines "vanish".

The FK constraint is set to CASCADE in all instances. Another wierd thing is if I set the AutoincrementStep to -1, it would assign a PKID=102 to the inserted master. Using an AutoincrementSeed of even -1000, does nothing.

I''m on my last leg here. Thanks!

[Administrator]

So, it seems the problem is that the DataBase is resetting a value in the new master table, is this correct?

One possible work around would be to go ahead and save the new row master table before adding related entries in the child table. Then hopefully, the values would not change. You could try just calling datatable.AcceptChanges on the master table to see if that would do it. If not, then you would probably have to update the database to get the new row finalized with the proper primary key.

Instead of trying to save the new master rows to the database one at the time, another option might be to wait. Then when you need ot save things, save the master table first keeping track of whether new records are being chnaged, and then make any necessary modifiecation in the child table before saving it.

Another option might be querying the database for the next primary key and making sure the new row is using this key. I am not sure how you might do this.

[Helmut Scheiss]

Just to make sure you have enough information. There are two DataAdapters:

daMaster and daDetail

daMaster uses this SP to get its data:

PROCEDURE getMasterRecs(@qaID int)
AS
SELECT d.QADetailID,
d.QAID,
d.Description
FROM
tblMaster d LEFT OUTER JOIN
tblDetail dd ON d.QADetailID = dd.QADetailID
WHERE
QAID = @qaid

daDetail uses this SP to select its records:

PROCEDURE getDetailRecs(@qaID int)
AS
SELECT dd.DetailDetailID,
dd.QADetailID,
dd.Description
FROM
tblDetail dd,
tblMaster, d
WHERE
dd.QADetailID = d.QADetailID AND
d.QAID = @qaid

There is a FK constraint on Master.QADetailID and Detail.QADetailID.

When I setup my grids, the Master Grid uses the dataset as its datasource and getMasterRecs as its DataMember. The Detail Grid uses the same dataset as its datasource and its DataMember is set to the constraint.

I am now storing the original Master.QADetailID into a field, Master.OldQADetailID.
What I think I have to do is after Updating the Master table, loop through all of the Detail Datarows and change the Detail.QADetailID to the new Master.QADetailID.

Is this kludgey or is there some better way?

PS:Not sure how to loop through detail recs to set their values prior to doing an update.....