Correct UpdateBatch Execution Order?

Correct UpdateBatch Execution Order?

Post by Mark A. Esse » Thu, 27 Jan 2000 04:00:00



We are using UpdateBatch to update changes made to a disconnected recordset.
The problem is that it appears ADO(2.1) is performing Updates in the
recordset before doing Inserts.  I would expect UpdateBatch would perform in
the following order:

All Deletes
All Inserts
All Updates

It appears that Updates are happening before Inserts.   We are getting a
'Foreign Key' violation because of this.  If I do an insert, then save, and
then do a change(Update), and save again, everything works fine, but if I do
the insert and then the change(Update) and THEN save, it fails because of
the Foreign Key violation.

We are using the Microsoft OLE DB Provider for SQL Server, going against SQL
Server 7.0 SP1.  I can't find anything which talks about the order in which
the items are processed.   It almost looks like it might being looping
through the recordset, in Bookmark order, and performing the tasks in
Bookmark order.

Any suggestions, or pointers to where I might look for more information,
would be greatly appreciated.

Thanks,

Mark A. Essex

 
 
 

Correct UpdateBatch Execution Order?

Post by Marcus Gattinge » Tue, 01 Feb 2000 04:00:00


Hi Mark,

I had the same problem. I solved it by looping though all the pending
records (use Filter property with adPendingRecords) and test for the status
property having set to adRecDeleted or adRecNew. For those records found, I
call a special Stored Procedure, which takes care of all constraints. The
records left (those which have status adRecModified) were finally saved with
an UpdateBatch call. Seems no workaround for this to me. But if you get some
information about a better solution Im really interested to hear about it.

Best regards,
Marcus



> We are using UpdateBatch to update changes made to a disconnected
recordset.
> The problem is that it appears ADO(2.1) is performing Updates in the
> recordset before doing Inserts.  I would expect UpdateBatch would perform
in
> the following order:

> All Deletes
> All Inserts
> All Updates

> It appears that Updates are happening before Inserts.   We are getting a
> 'Foreign Key' violation because of this.  If I do an insert, then save,
and
> then do a change(Update), and save again, everything works fine, but if I
do
> the insert and then the change(Update) and THEN save, it fails because of
> the Foreign Key violation.

> We are using the Microsoft OLE DB Provider for SQL Server, going against
SQL
> Server 7.0 SP1.  I can't find anything which talks about the order in
which
> the items are processed.   It almost looks like it might being looping
> through the recordset, in Bookmark order, and performing the tasks in
> Bookmark order.

> Any suggestions, or pointers to where I might look for more information,
> would be greatly appreciated.

> Thanks,

> Mark A. Essex



 
 
 

1. UpdateBatch - Trigger Execution

HI everybody,

i'm working on an import-application for my SQL7 database with
WIN2000/VB6/ADO2.5 using the UpdateBatch-method of the ADO-recordset object.
the strange thing on that is the following: when i was testing the princip
of this import, a insert-trigger on the affected table fires only once per
UpdateBatch call, independent on the number of inserted records, but now the
trigger fires for each record inserted by the UpdateBatch statement ...

have anybody same problems or ideas on that topic?
thanks

2. Can you recommend some tools??

3. Cannot update database using DataCombo with ADO

4. Are execution plans correct for table variables?

5. AmerOmnis 2002 - Omnis User Conference

6. UpdateBatch re-orders records

7. Interface to Data Dictionary (Delphi 3)

8. Correct Order for JOINs

9. DTS fails to load tables in correct order

10. Scripting SPs in the correct order

11. Question about scripting SPs in the correct order

12. sysdepends and finding out the correct run order for stored procs