How to delete certain rows from Access via Delphi?

Question

while not TBLOrder.Eof do
begin
  TBLOrder.Locate('OrderID', Total, []);
  TBLOrder.Delete;
end;

This just deletes every single row in my Access Database, which is really annoying.

I'm trying to get the program to delete the selected row (which is Total).

From what I understand, It should locate the selected row, which is equal to Total. e.g. If Total = 3 it should find the row where OrderID = 3 and then delete that row.

Any help is appreciated.

"which is really annoying" - +1 for understatement of the month! — Leonardo Herrera, Mar 22 '12 at 18:28

score 3 · Accepted Answer · answered Mar 22 '12 at 18:11

Try this instead (Max's routine requires you to loop through the entire dataset, which is fine unless it's got many rows in it):

while (TblOrder.Locate('OrderID', Total, [])) do
  TblOrder.Delete;

TDataSet.Locate returns a Boolean; if it's True, the found record is made the active record and you can then delete it. If it returns False (meaning the record is not found), the call to Delete is never made.

BTW, the problem with your original code is that you test for Eof, but never check to see if the Locate finds the record; you just delete whatever record you're on, and then test for Eof again. If you're not at Eof, you call Locate, ignore whether or not it found the record, and delete whatever row you're on. This then repeats over and over again until there are no more records, at which point Eof returns true and you break the loop.

@MaxWilliams - thanks. :) As I said, yours works fine as long as there aren't many rows, and you did mention that in your code comment in your answer. — Ken White, Mar 22 '12 at 19:00

score 2 · Answer 2 · answered Mar 22 '12 at 18:04

If there is just one row that contains an ORDERID equal to 3, you don't need the WHILE loop.

If you expect more than one row with an ORDERID equal to 3, do this:

TBLOrder.first; // you could also do the locate here if it's a big table
while not TBLOrder.Eof do
begin
 if TBLOrder.FieldByName('OrderID').AsInteger = 3 then
   TBLOrder.delete
 else
   TBLOrder.next;
 end;

Otherwise, you could also use SQL.

How to delete certain rows from Access via Delphi?

2 Answers2