Page 1 of 1
Delete filtered records in table
Posted: Sat 20 Aug 2011 09:24
by 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!
Posted: Sat 20 Aug 2011 09:53
by invent
Hello,
when you want to delete one item you have edit this one:
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
Posted: Sat 20 Aug 2011 09:58
by 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:
Posted: Sat 20 Aug 2011 11:53
by 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
Posted: Sat 20 Aug 2011 16:29
by bug_me_not
Hello,
invent wrote: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
Posted: Mon 22 Aug 2011 12:01
by 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.
Posted: Mon 22 Aug 2011 14:52
by 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
Posted: Tue 23 Aug 2011 12:01
by 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.