Question BindingSource, controls and a grid

rpatterson

New member
Joined
Sep 15, 2014
Messages
3
Programming Experience
5-10
Hello, been racking my brain on this one for a while now. On my sqlserver 11 i have two tables, tblReconcile and tblReconcile checks. i have relationship defined between the parent and child. Parent has a primary key ReconcileID is a autonumber, IndentityIncrement & Seed set.

View attachment 4090

I have a strong types dataset created with the dataset wizard. The tblReconcile in the dataset tools window shows the child table tblReconcileCheck correctly.

View attachment 4091

i drag over a field from the dataset window, vb creates dataset, bindsource and tableadapter objects. For controls i setup the (databinding) property successfully. For the grid, i drag over the child table from the dataset window, vb creates another bindingsource, BindingSourceReconcileCheck. BindingSourceReconileCheck's datasource is set to ParentBindingSource its datameber set to FK_tblReconcileCheck_tblReconcile. The Child grid for ReconcileChecks is bound to BindingSourceReconileCheck..

Theres and update called:

Me.Validate()
Me.TblReconcileBindingSource.EndEdit()
TblReconcileCheckBindingSource.EndEdit()

TblReconcileTableAdapter.Update(Me.DataSet1)
TblReconcileCheckTableAdapter.Update(DataSet1)


Ok, so it works perfectly when modifying existing records, child follows parent successfully, except when i goto add a new record in the parent and then to the grif. The Grid adds the tempory keys IE -1,-2,-3 etc. Except when i call the above update i get the statement, breaking on "TblReconcileCheckTableAdapter.Update(DataSet1)"

The statement has been terminated. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblReconcileCheck_tblReconcileDATABASE". The conflict occurred in database "eboxII", table "dbo.tblReconcile", column 'ReconcileID'.


I know this error is coming from sqlsever when its trying to write these tempory keys back to db. Not sure what going wrong here. From what i've read it, the dataset variable is suppose to bring the key value back up.

I used GUID's for years to work around this, but i figured theres a correct way! ANy help is appreicated!
 
When you insert the parent records into the database, the database will generate the final ID values. Those ID values need to be retrieved back into your parent DataTable and then propagated to the child DataTable.

Open your DataSet in the designer, select the table adapter for the parent table and examine its InsertCommand. Its CommandText property contains the SQL code and it should include both an INSERT statement to save the new record and a SELECT statement to retrieve the auto-genereated ID, e.g.
VB.NET:
INSERT INTO MyTable (Column1, Column2) VALUES (@Column1, @Column2);
SELECT ID = SCOPE_IDENTITY()
It's the SELECT statement that does the first part of the job, i.e. retrieve the ID value back into the parent DataTable.

Next, open the properties for the foreign key relation. Make sure that it is set to cascade on updates. That does the second part of the job, i.e. propagate the values from the parent table to the child when the parent ID is updated.
 
When you insert the parent records into the database, the database will generate the final ID values. Those ID values need to be retrieved back into your parent DataTable and then propagated to the child DataTable.

Open your DataSet in the designer, select the table adapter for the parent table and examine its InsertCommand. Its CommandText property contains the SQL code and it should include both an INSERT statement to save the new record and a SELECT statement to retrieve the auto-genereated ID, e.g.
VB.NET:
INSERT INTO MyTable (Column1, Column2) VALUES (@Column1, @Column2);
SELECT ID = SCOPE_IDENTITY()
It's the SELECT statement that does the first part of the job, i.e. retrieve the ID value back into the parent DataTable.

Next, open the properties for the foreign key relation. Make sure that it is set to cascade on updates. That does the second part of the job, i.e. propagate the values from the parent table to the child when the parent ID is updated.

HI Thanks for your quick response... You helped me figure out i was using a ole connection instead of a nativesql client. So i moved up to VB2012 and update my connections. After that i did get to the point of confirming the SELECT statement for the insert contains both the INSERT command; SELECT SCOPE Command.

However, im assuming i change the foreign key relation you mentioned is in the dataset designer and not sqlserver (since those are already defined). So i did that http://fcfinearts.fullcoll.edu/capture3.jpg. Now when i enter both a new parent record and a new child record in the grid, the grid calls an error before i can even leave the newly added row. http://fcfinearts.fullcoll.edu/capture4.jpg.

I'd imagine i wouldn't have to call the parenttable adapter update first, but if i do, then i can add newchild rows. That does seem to work, but doesnt seem the right way to do it.

my update cmd is
VB.NET:
Me.Validate()
        Me.TblReconcileBindingSource.EndEdit()
        TblReconcileCheckBindingSource.EndEdit()
        TblReconcileTableAdapter.Update(Me.EboxIIDataSet)
        TblReconcileCheckTableAdapter.Update(Me.EboxIIDataSet)
 
If you have set the foreign key to cascade updates in SQL Server then the Data Source Wizard should do the same in your DataRelation, if I remember correctly, i.e. you shouldn't have to edit anything. If that's not the case then setting cascading updates is all you need for this particular issue. As for that error message, I can only tell you that if there is a parent record with that ID then you can add a child record with that foreign key, so you mustn't have a parent record with that ID in your DataSet.
 
If you have set the foreign key to cascade updates in SQL Server then the Data Source Wizard should do the same in your DataRelation, if I remember correctly, i.e. you shouldn't have to edit anything. If that's not the case then setting cascading updates is all you need for this particular issue. As for that error message, I can only tell you that if there is a parent record with that ID then you can add a child record with that foreign key, so you mustn't have a parent record with that ID in your DataSet.

Thanks for your help! I'll tinker with it some more!
 
Back
Top