Primary Keys on multi user relational tables

[mmeier]

Hello - I'm new to vb.net

Here is what I have.

Backend is SQL Server 2000, developing vb in Studio 2005.

Just 2 tables, the Parent being tblChecks - primary key - Identity (auto Number), the Child being tblCheckSplits, Primary Key - Identity (Autonum) and a foreign constraint of the parent tblChecks primary key to tie the splits to the check.

In VB.Net, have windows form of the parent table, with a datagridview of the child table. All works well when paging forward, back, adding new, etc. The datagridview of the detail changes when the parent current record changes. (Due to the relationship setup in the data-sources)

My problem is when a record is added following a deletion. Error - INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_tblCheckSplits_tblChecks'.

I believe I see the problem. The highest key showing in enterprise mgr for the parent table is 56. The record that was key 57 has been deleted. Now we add a new parent record via the vb.net windows form. It gives it a primary key value of 57. Next, the child recs are created in the datagridview. VB.net gives the relating field the value 57, which is correct. Now when I endedit and update, the parent record in enterprise mgr gets value of 58 (skipping 57 because it was previously deleted. Then when I endedit and update the child table, I receive the above error. I'm assuming it’s because it's trying to add child recs with a foreign constraint key of 57 to the SQL table. None exist with that key in the sql parent table.

Now - I'm also assuming in a multi user setting, I will have these same issues randomly on adds.

My question - should I be trying to do this with Identity field for the primary key on the parent table?, or should it be a GUID, or something user created. (can't use check number because they will be assigned later)

I'm new to the table adaptor, binding source stuff. I'm use to adds/updates/delete going to the SQL tables immediately, not as a batch on a save button. So, my concepts may be way off.

Any suggestions / advice welcome.

Thanks for your time,

Mike

 

[Arg81]

on your dataSet, goto your tblChecks DataTable, and for the Identity column, set AutoIncrement to TRUE, AutoIncrementSeed to -1 and AutoIncrementStep to -1

You should really do this for every table that get it's ID generated from the SQL Server, so that as soon as a row is inserted, your app may say -1, but it then gets the proper ID set by the server. This also helps in a multi-user environment!

 

[cjard]

He's right... For ID fields, whatever the database says is what ADO copies..

For example, I have related data.. And i relate it with -ve numbers.

My parent ID is -1, child records are -1

I update the Parent, database assigns a new ID of 123456. Parent record is automatically set to 123456 when the database call to update the parent finishes. Hence, VB.NET updates all child records to 123456 to maintain local relationship. Then the next line of code submits the children. THey are written successfully..

All this happens auto for me. Perhaps you havent formed the relationship correctly on VB.NET side of things

 

[mmeier]

got it

Thanks! the -1 got me past the issue. Then I learned I need to do and EndEdit on the parent before creating a child record, even though the child record was picking up the correct -1 as it was being entered.

Thanks again guys!

Mike

 

[cjard]

Thanks! the -1 got me past the issue. Then I learned I need to do and EndEdit on the parent before creating a child record, even though the child record was picking up the correct -1 as it was being entered.

Thanks again guys!

Mike

Thats something else I should have mentioned... I found when I was coding, early on.. that if I did this:

ParentBindingSource.AddNew()
ChildBindingSource.AddNew()

Then edited the data (typed into the tect boxes)

Then as soon as I call ParentBindingSource.EndEdit(), the related child data would be destroyed :/

Nuisance.

I now ensure I cann EndEdit immediately after AddNew