Re: Delete record in batch mode

If you dont include a unique key from each table that you want to edit
(including delete) then most database drivers have to read every record in
the table to try an determine which record **might** be the one that you
want updated or deleted. I say **might** becuase there may be more that one
record which has identical column data and then the database manager can
tell which particular (unique) record is the one that you *really* want to
update or delete. Most Access databases tend to be small and this phenomena
goes unnoticed but with large tables in MS Sql it will start to run slow or
fail completely.

e.g. lets say you have an invoice table and you select the following columns
from 6 rows ...

InvoiceNumber, InvoiceDate, TransCode, Amount

000001, 1/2/2007, CR, 1500.00
000001, 1/2/2007, CR, 1500.00
000001, 1/2/2007, CR, 1500.00
000001, 1/2/2007, CR, 1900.00
000001, 1/2/2007, CR, 2000.00
000001, 1/2/2007, CR, 2200.00

This is now in your ADO record set.

Then you change the "amount" on the third record to 1850.67

Then you call "updatebatch" ...

Which physical record in the actural database would you expect the the
database manager to update ???

He cant telll because ther were 3 records in the ADO Recordset and they
didnt' have any unique identifiers that he could you to determine exactly
which record was to be updated in the actuual database.

you **MUST** select a unique key from every row that you want to update or
delete and then, if it is a joined recordset you ** MUST** tell the database
manager, which table you want the updates to apply to .. he does not
automatically update all of the tables that were in the join, he updates
only one of them and if you dont let him know which one it is, he cant do
the job right.


.

Relevant Pages

  • Re: Edward Grevilles brother-in-law, Sir Edmund Tame
    ... but I dont have access to "Burke's landed Gentry" unless ... Robert ...jie no parents shown on Leo's site. ... If you took my statement to mean this database I have is more correct, ...
    (soc.genealogy.medieval)
  • Re: Any good Project Programming resources/books?
    ... I dont think that would be the best way to tackle this because it isn't ... Oracle 9i and Sql Server 2000. ... All the database connection information ... >> about is an internal ASP.NET app. ...
    (microsoft.public.project.vba)
  • Discussion: How to determine a Fact Table algorithmically
    ... I dont know the difference between OLAP and the datawarehouse newsgroups, ... database so that you can eventually build a cube around it. ... new one-click cube feature - It seems pretty magical that you can ... Can anyone take a crack at this algorithm? ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Why no links creat automatically, when use tools-->relationshi
    ... "Larry Linson" wrote: ... Allen Browne - Microsoft MVP. ... I dont know how to select relationship. ... I did Link to that database, since it has 600+ tables, and some ...
    (microsoft.public.access.reports)
  • Re: Delphi6. ADO <->SQLExpress - best practice?
    ... Unlike BDE and Paradox, dont use tADOTable .. ... can then be committed to the database "optimistically", ... helpful when you have multiple reords being created and dont want to commit ...
    (borland.public.delphi.database.ado)