Recent

Author Topic: [SOLVED] DBGrid multi-select  (Read 1276 times)

egsuh

  • Hero Member
  • *****
  • Posts: 1394
[SOLVED] DBGrid multi-select
« on: May 26, 2024, 01:56:47 am »
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][+][-]
  1. procedure TSampleList.DBNavigator1Click(Sender: TObject;
  2.    Button: TDBNavButtonType);
  3. var
  4.    i: Integer;
  5. begin
  6.    if Button = nbDelete then begin
  7.       for i:=0 to dbgSampleList.SelectedRows.Count-1 do
  8.          with dbgSampleList.DataSource.DataSet do
  9.          begin
  10.             GotoBookmark(pointer(dbgSampleList.SelectedRows.Items[i]));
  11.             Delete;
  12.          end;
  13.    end;
  14. end;
  15.  
  16. procedure TSampleList.bdsSampleListBeforeDelete(DataSet: TDataSet);
  17. var
  18.    tclause: string;
  19. begin
  20.    TClause := Format('project_id=''%s'' and respondent_id=%d',
  21.              [pid, bdsSampleList.FieldByName('RID').AsInteger]);
  22.    if not DataAccess.DeleteARecord('SampleList', tclause)
  23.       then bdsSampleList.Cancel;
  24. end;
  25.  

This deletes as I want, but it causes memory leak, as attached in the image. What might have caused this?
« Last Edit: May 27, 2024, 10:20:19 am by egsuh »

korba812

  • Sr. Member
  • ****
  • Posts: 401
Re: DBGrid multi-select
« Reply #1 on: May 26, 2024, 01:00:24 pm »
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

  • Hero Member
  • *****
  • Posts: 1394
Re: DBGrid multi-select
« Reply #2 on: May 27, 2024, 03:34:24 am »
Quote
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.

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

  • Sr. Member
  • ****
  • Posts: 496
Re: DBGrid multi-select
« Reply #3 on: May 27, 2024, 07:33:36 am »
Have you tried dbgSampleList.SelectedRows.delete? does it causes mem leaks too?

egsuh

  • Hero Member
  • *****
  • Posts: 1394
Re: DBGrid multi-select
« Reply #4 on: May 27, 2024, 08:05:51 am »
Hi jesusr,

Quote
Have 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][+][-]
  1. procedure TSampleList.bdsSampleListBeforeDelete(DataSet: TDataSet);
  2. var
  3.    tclause: string;
  4. begin
  5.    TClause := Format('project_id=''%s'' and respondent_id=%d',
  6.              [pid, bdsSampleList.FieldByName('RID').AsInteger]);
  7.    if not DataAccess.DeleteARecord('SampleList', tclause)
  8.       then bdsSampleList.Cancel;
  9.       // else -------------------- need something here?
  10. end;

rvk

  • Hero Member
  • *****
  • Posts: 6276
Re: DBGrid multi-select
« Reply #5 on: May 27, 2024, 08:46:32 am »
Quote
Have 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][+][-]
  1.   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.
« Last Edit: May 27, 2024, 08:52:08 am by rvk »

egsuh

  • Hero Member
  • *****
  • Posts: 1394
Re: DBGrid multi-select
« Reply #6 on: May 27, 2024, 10:20:03 am »
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][+][-]
  1. procedure TSampleList.DBNavigator1Click(Sender: TObject;
  2.    Button: TDBNavButtonType);
  3. var
  4.    ti: Integer;
  5. begin
  6.    if Button = nbDelete then begin
  7.       for ti := 0 to dbgSampleList.SelectedRows.Count-1 do begin
  8.          bdsSampleList.BookMark := dbgSampleList.SelectedRows.Items[ti];
  9.          bdsSampleList.Delete;
  10.       end;
  11.    end;
  12. end;
  13.  


    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

  • Hero Member
  • *****
  • Posts: 1394
Re: [SOLVED] DBGrid multi-select
« Reply #7 on: May 27, 2024, 10:28:26 am »
It's interesting that following does not delete all records selected.

Code: Pascal  [Select][+][-]
  1. var
  2.    bm: TBookMark;
  3. begin
  4.    if Button = nbDelete then begin
  5.       for bm in dbgSampleList.SelectedRows begin
  6.          bdsSampleList.BookMark := bm;
  7.          bdsSampleList.Delete;
  8.       end;
  9.    end;
  10. end;

Hope that the TDBGrid.SelectedRows.Delete and all of other similar approaches work fine.

rvk

  • Hero Member
  • *****
  • Posts: 6276
Re: [SOLVED] DBGrid multi-select
« Reply #8 on: May 27, 2024, 10:29:38 am »
It's interesting that following does not delete all records selected.
Just curious... but what happens if you comment out everything in bdsSampleListBeforeDelete ?

egsuh

  • Hero Member
  • *****
  • Posts: 1394
Re: [SOLVED] DBGrid multi-select
« Reply #9 on: May 27, 2024, 10:41:50 am »
Quote
Just 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

  • Sr. Member
  • ****
  • Posts: 496
Re: [SOLVED] DBGrid multi-select
« Reply #10 on: May 27, 2024, 05:02:43 pm »
It's interesting that following does not delete all records selected.

Code: Pascal  [Select][+][-]
  1. var
  2.    bm: TBookMark;
  3. begin
  4.    if Button = nbDelete then begin
  5.       for bm in dbgSampleList.SelectedRows begin
  6.          bdsSampleList.BookMark := bm;
  7.          bdsSampleList.Delete;
  8.       end;
  9.    end;
  10. 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

 

TinyPortal © 2005-2018