Saving Dataset- Order of operations

[Vayse]

Hi
If you save a Dataset, which order must you do the operations in? I think
its
1) Update
2) Delete
3) Add

Or does the order matter?
Vayse

 

[Vayse]

Its ok, I remembered it was in Sahils book.
Tis
1) Inserts
2) update
3) Delete

 

[Marina Levit [MVP]]

Why is this neccessarily the right way?

I would argue Deletes need to take place before Inserts.

For example, let's say the user deleted a row with primary key A. But then
inserted it again. So the new row has a primary key A, and a bunch of other
data that differs from the origiinal row which has been deleted.

If you process the Inserts first, it will fail due to a primary key
violation.
If you process the Delete first, you will delete the old row (what the user
did), and then you can Insert the new row - and everything will be fine.

 

[Guest]

If you're processing a dataset with related records, you'll want to process
deletes from the bottom up and then apply updates and inserts from the top
down.

For example, assume the following relations:
Customers
Contacts (of a customer)
Phones (of a contact)

Delete any phones, then any contacts, the any customers. Unless you cascade
deletes and then you only delete the Customer record.

Insert any Customers, any contacts, and then any phones.
Updates technically can happen either with the deletes or the insert unless
you alter primary keys in which case you either need to cascade udpates or
actually delete from the bottom up and insert from the top down.

 

[Cor Ligthert [MVP]]

Larry,

Nice short written, compliments

Cor

 

[Vayse]

Why is this neccessarily the right way?

Because Sahil said so! (p352 of Pro ADO 2)
Actually, you make a very good point about the primary keys. But would it
also not depend on the way the key was set up? If it was an autonumber key,
the insert would get the next number anyway.
But in the case of the user entering the primary key, then I would need to
do it the way you suggest. Better to do it that way in general I reckon.
Thanks!
Vayse