Identity Column & Dataset relation

[Guest]

Using SQL server2000 VB.Net. I have a parent table that uses an identity
column as its primary key. I also have a child table that uses the identity
to create the relation between the parent & child rows. My form has a grid
with a dataset as its source where these 2 tables are joined with a
relationship. The user can add data to the dataset via the grid. When new
rows are added to the parent table of the data set this identity field is
incremented and i copy it to use for the child row to maintain the relation.
The problem is that the identity that the dataset has generated for the
parent table is not neccisarily the id that the SQL table will generate.
Thus the relation between the 2 talbes is broken. I am using the Update
method of the Data Adapter to add the new rows to SQL Server.
So has anybody found a way to solve this kind of problem or should i be
creating my own Id's that i can control better?
Gary

 

[William \(Bill\) Vaughn]

See my website for an article "Dealing with an Identity Crisis" at
http://www.betav.com/Files/Content/whitepapers.htm

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

 

[Guest]

Bill Thanks for giving me the opportunity to buy your book. I did look at
your website & followed the solution you have there. I did download the code
also to review it further. However your solution seems to address a one to
many relationship where the parent record is created & saved before any
children records are even created and i've doine this in the past. My current
project is quite different.
My project is a quoting system where the parent rows are the detail lines of
the quote with child rows that hold info like cost, vendor, markup or
discounts. Currently each detail line will have 1 child (in the future it may
have multiple children). The user will create Detail lines & their children
in a grid and I'll save each detail & child to their coorosponding table in
the Dataset. Once the user is finished creating the quote I will then save
the new quote to the SQL DB. Here is where the problem lies. If records were
to be deleted from the SQL table the next ID to be assigned will not
necessisarily be incremented by 1. The Data Table with the Parent rows will
simply take the highest ID & increment it by one and assign it to my Parent
record. I will then copy this & assign it to the child record. When i use the
Data Adapter update method to save data back to the SQL DB, possibly a
different ID will be assigned to the parent record, thus breaking the link
between my parent & child records. What I'm looking for is if anyone has been
able to solve this usage of an Identity column for parent Child
realtionships.
Thanks
Gary

 

[PGC]

Hi G,

Change the properties of the relationship in the dataset to "FK + Relation"
then you should be able to turn on a Cascade update/delete option. If you
proceed as Bill suggests, when the new row is returned to the datatable
after the insert and with the new Identity value, this should then
automatically update the child records' FK.

PGC

 

[William \(Bill\) Vaughn]

After several discussions with folks at MS and others who have faced similar
problems , I think that it's getting hard to justify use of the Identity
paradigm. They aren't supported very well in VS (not that it matters to
you). At this point I think a UniqueIdentifer approach might make more
sense. Coupled with the new SS2K5 newsequentialid() function (as well as
NewID()) to generate the codes, the need for server-side Identity-value
generation is eliminated. While these numbers are not human-friendly, they
are far easier to manage in a disconnected architecture. You'll also find
that when it comes time to replicate your data (if you ever do), Identity
columns will have to be eliminated anyway.

In my book I discuss the use of the "Anvil-salesman's" technique where the
disconnected application does not generate new parent rows at all. When it
comes time to add a new customer, the application requests a blank order
form (or a block of them) from the server. These rows then belong to that
client so collisions are not possible. These new rows are generated by
server-side code and returned as one or more pre-populated rows.
Subsequently, the child rows are simply created on the client--knowing that
the rows are tied to a known parent. This way the client need not retrieve
an identity (or GUID) from the server after a row is inserted. When it comes
time to post this information to the server, the PK/FK values are already in
place. The parent rows are updated and the new child rows are inserted--but
without server-side Identity generation. Their PK is a composite of the
Parent's key and the client-generated sequential ID.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

 

[Guest]

Thanks Bill & PGC,
It looks like I have a chapter to read & some work to do. PGC, how do i set
the FK to the tables in the dataset? I can set the realtion just fine.
It looks like i will try the Unique Identifier route.