We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Autoincrementing in a DBG

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!

2 Replies

AD Administrator Syncfusion Team May 20, 2005 01:53 PM UTC

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.

HS Helmut Scheiss May 20, 2005 09:14 PM UTC

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.....

Live Chat Icon For mobile
Up arrow icon