Delete filtered records in table

[bug_me_not]

Good day!
Can somebody help me please with such question:
I have a table with filtered items. I need to delete this items from a table. Trying to do like this:

Code: Select all

Tbl.Filter := 'Color=' + IntToStr(C); // by example 10
Tbl.Filtered := True;
Tbl.Edit;
Tbl.First;
while not Tbl.Eof do
begin
  Tbl.Delete;
  TblNext;
end;
Tbl.Post;

On Tbl.Delete I have a exception: "Refresh failed. Found 5 records"
How to delete the records? Thanks in advance!

[invent]

Hello,

when you want to delete one item you have edit this one:

Code: Select all

tbl.First;
tbl.Edit;
tbl.Delete;

Than there is no need to make tbl.Post, because the item is away.

A faster solution is to delete all items at once:

Code: Select all

MyUniQuery.Sql.Clear;
MyUniQuery.Sql.Add ('delete from ' + tbl.Tablename + ' where color = ' + QuotedStr (IntToStr (C));
MyUniQuery.Execute;

Kind regards,
Gerd Brinkmann
invent GmbH

[bug_me_not]

Yes, I can use query. But I suppose that there must be a way how to delete filtered records from table directly using only UniTable component. Btw, this will not work for filtered records:

Code: Select all

tbl.First;
tbl.Edit;
tbl.Delete;

[invent]

Hello,

with UniDAC it's better (and faster) to use FilterSQL. This works:

Code: Select all

UniTable1.TableName := ...;
UniTable1.FilterSQL := 'color = ' + QuotedStr (IntToStr (C));
UniTable1.Open;
repeat
  UniTable1.First;
  UniTable1.Edit;
  UniTable1.Delete;
until (UniTable1.RecordCount = 0);
UniTable1.Close;

Kind regards,
Gerd Brinkmann
invent GmbH

[bug_me_not]

Hello,

Hello,

with UniDAC it's better (and faster) to use FilterSQL. This works:

Code: Select all

UniTable1.TableName := ...;
UniTable1.FilterSQL := 'color = ' + QuotedStr (IntToStr (C));
UniTable1.Open;
repeat
  UniTable1.First;
  UniTable1.Edit;
  UniTable1.Delete;
until (UniTable1.RecordCount = 0);
UniTable1.Close;

Kind regards,
Gerd Brinkmann
invent GmbH

Nope, I just checked:

Refresh failed. Found 5 records

Can this be a MS SQL Server 2000 specific problem? I need to use it.
Thanks

[AndreyZ]

This error occured because 5 records were deleted during deletion operation. In such situations UniDAC warns that the number of deleted records is not equal 1 by raising the "Refresh failed. Found N records" exception. You can avoid this problem by setting the TUniTable.StrictUpdate property to False. For more information about this property, please read the UniDAC documentation.

[invent]

Hello AndreyZ,

please give me more information, because I cannot believe this.

Code: Select all

UniTable1.First;
UniTable1.Edit;
UniTable1.Delete; 

I thought that with this code I'm only working on one record, because TDataSet.First gives me only one record. Why can it be that this code deletes 5 records?

Kind regards,
Gerd Brinkmann
invent GmbH

[AndreyZ]

The point is that to delete record TUniTable executes a query to a database. If your table doesn't have the Primary Key, this query will use all the fields:

Code: Select all

DELETE FROM dept
WHERE DEPTNO = :DEPTNO AND DNAME = :DNAME AND LOC = :LOC

So, if there are more than one record with the same field values, UniDAC will raise the "Refresh failed. Found N records" exception (unless the StrictUpdate option is set to False). To avoid this problem, your table should have the Primary Key. In this case, UniTable will execute the query using only the fields of the Primary Key:

Code: Select all

DELETE FROM dept
WHERE DEPTNO = :DEPTNO

In this case you can be sure that such query will delete only one record.
You can set the TUniTable.Debug property to True to display the statement that is being executed. It will help you to understand the behaviour of the TUniTable component. Note that you should add the UniDacVcl unit to the USES clause of any unit in your project to make the Debug property work.