[SOLVED] DBGrid multi-select
lazarus / 16-Databases

----------------------------------------------------------------------
egsuh · 2024-05-25 23:56

I've written following in another thread, but I'd like to create a new
thread and post again.
----------------------------------------------------------------------
------------------------------------------
I'm trying to extend this to delete multiple rows at once in following
way. BeforeDelete event is to delete the record from SQL server before
the record is deleted at the TDataSet.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TSampleList.DBNavigator1Click(Sender: TObject;   Button:
TDBNavButtonType);var   i: Integer;begin   if Button = nbDelete then
begin      for i:=0 to dbgSampleList.SelectedRows.Count-1 do       
 with dbgSampleList.DataSource.DataSet do         begin           
GotoBookmark(pointer(dbgSampleList.SelectedRows.Items[i]));          
Delete;         end;   end;end; procedure
TSampleList.bdsSampleListBeforeDelete(DataSet: TDataSet);var 
 tclause: string;begin   TClause := Format('project_id=''%s'' and
respondent_id=%d',             [pid,
bdsSampleList.FieldByName('RID').AsInteger]);   if not
DataAccess.DeleteARecord('SampleList', tclause)      then
bdsSampleList.Cancel;end; 
This deletes as I want, but it causes memory leak, as attached in the
image. What might have caused this?

----------------------------------------------------------------------
korba812 · 2024-05-26 11:00

If I remember correctly, in order to cancel deletion of a record in
the BeforeDelete event, you must use the Abort function or raise an
EAbort exception. Maybe this is reason? Try calling Abort function
instead of Cancel method.

----------------------------------------------------------------------
egsuh · 2024-05-27 01:34

QuoteIf I remember correctly, in order to cancel deletion of a record
in the BeforeDelete event, you must use the Abort function or raise an
EAbort exception. Maybe this is reason? Try calling Abort function
instead of Cancel method.
No... at first I tested with abort, which was not successful for some
reason. Just cancelling posting of the deletion is sufficient here.
Anyway this is not called, because I click yes.

----------------------------------------------------------------------
jesusr · 2024-05-27 05:33

Have you tried dbgSampleList.SelectedRows.delete? does it causes mem
leaks too?

----------------------------------------------------------------------
egsuh · 2024-05-27 06:05

Hi jesusr,

QuoteHave you tried dbgSampleList.SelectedRows.delete? does it causes
mem leaks too?
No, this does not cause mem leak. Thank you for your advice.
But, this does NOT delete all the records selected -- some of them
remain in the local dataset (numbers are inconsistent). SERVER RECORDS
ARE DELETED.

Any possible addition to the following codes?

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TSampleList.bdsSampleListBeforeDelete(DataSet: TDataSet);var 
 tclause: string;begin   TClause := Format('project_id=''%s'' and
respondent_id=%d',             [pid,
bdsSampleList.FieldByName('RID').AsInteger]);   if not
DataAccess.DeleteARecord('SampleList', tclause)      then
bdsSampleList.Cancel;      // else -------------------- need something
here? end;

----------------------------------------------------------------------
rvk · 2024-05-27 06:46

Quote from: egsuh on May 27, 2024, 08:05:51 amQuoteHave you tried
dbgSampleList.SelectedRows.delete? does it causes mem leaks too?No,
this does not cause mem leak. Thank you for your advice.
SelectedRows.Delete does a countdown instead of a for 0 to count - 1
(which you are doing)

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   for i :=
FList.Count-1 downto 0 do begin
Normally this shouldn't matter because doing a delete directly in an
event doesn't effect the buffered dataset.
BUT... you have some procedures which interact with the dataset during
deletion.
Namely the DataAccess.DeleteARecord (I'm not sure what going on in
that procedure).

But if the dataset is updated INSIDE the event, then the for 0 to
count -1 becomes very problematic.
You delete item 0 and everything is shifted one up.
The you delete item 1 (while item 0 is still not deleted).
You end up trying to delete non existing items !!

So... either also do a countdown like "for i := FList.Count-1 downto 0
do begin"
or do a while FList.Count > 0 delete item 0.

----------------------------------------------------------------------
egsuh · 2024-05-27 08:20

DataAccess.DeleteARecord is simply calls SQLquery that deletes server
record matching rid field. BeforeDelete intends to delete server
record before delete local (client) dataset's record.

I finally solved this problem. Following code works fine.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TSampleList.DBNavigator1Click(Sender: TObject;   Button:
TDBNavButtonType);var   ti: Integer;begin   if Button = nbDelete then
begin      for ti := 0 to dbgSampleList.SelectedRows.Count-1 do begin 
       bdsSampleList.BookMark :=
dbgSampleList.SelectedRows.Items[ti];         bdsSampleList.Delete;  
  end;   end;end; 

    for ti := dbgSampleList.Selectedrows.Count-1 downto 0 ...

does NOT delete all records selected. The server side records are
deleted correctly.


I've copied following codes somewhere from this forum.

      GotoBookmark(pointer(dbgSampleList.SelectedRows.Items[ i ]));
      Delete;

But It's not necessary to use pointer.  I should have written

       GotoBookmark(dbgSampleList.SelectedRows.Items[ i ]);  

as ..selectedrows.items[Index] is TBookMark type.

But the document says TDataSet.GotoBookMark is depricated in Lazarus /
FPC (still compiles and works), and recommend to use

      TDataSet.Bookmark := ABookMark; 

Anyway
      dbgSampleList.SelectedRows.Delete
should delete all the records, if to be implemented.

----------------------------------------------------------------------
egsuh · 2024-05-27 08:28

It's interesting that following does not delete all records selected.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var   bm:
TBookMark;begin   if Button = nbDelete then begin      for bm in
dbgSampleList.SelectedRows begin         bdsSampleList.BookMark := bm;
         bdsSampleList.Delete;      end;   end;end;
Hope that the TDBGrid.SelectedRows.Delete and all of other similar
approaches work fine.

----------------------------------------------------------------------
rvk · 2024-05-27 08:29

Quote from: egsuh on May 27, 2024, 10:28:26 amIt's interesting that
following does not delete all records selected. Just curious... but
what happens if you comment out everything in
bdsSampleListBeforeDelete ?

----------------------------------------------------------------------
egsuh · 2024-05-27 08:41

QuoteJust curious... but what happens if you comment out everything in
bdsSampleListBeforeDelete ?
The same. Even I removed BeforeDelete event, it does not delete all
records.
There is possibility that selection order may influence the result,
but I will not go further to test that.

----------------------------------------------------------------------
jesusr · 2024-05-27 15:02

Quote from: egsuh on May 27, 2024, 10:28:26 amIt's interesting that
following does not delete all records selected.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var   bm:
TBookMark;begin   if Button = nbDelete then begin      for bm in
dbgSampleList.SelectedRows begin         bdsSampleList.BookMark := bm;
         bdsSampleList.Delete;      end;   end;end;
Hope that the TDBGrid.SelectedRows.Delete and all of other similar
approaches work fine.

The problem with deleting records using this code is that it depends
on the specific dataset descendant bookmarks implementation which is
opaque. Meaning sometimes it will work and sometimes it will not, is
not DbGrid fault, it does what it can with the limited information it
have. What may help is adding some documentation so to avoid false
expectations.

If you want to be sure your code will work even if you switch datasets
type, don't use bookmarks directly, use them to collect the records
you want to delete by recording somewhere else the primary key or
whatever makes the selected records unique, then use something like
this (is not the only way of course):
Freeze the db controls[find a record which will not be deleted and
save it's location, do not use a bookmark (it might not survive record
deletions)][clear the bookmarks][start a transaction, if using
transactions]loop using dataset.locate and dataset.delete for deleting
the records[commit your changes, if using transactions]refresh the
dataset which should update any db controls[locate the undeleted
record]Unfreeze the db controls