How to delete selected database records from a TDBAdvListView?

Question

I am testing out Absolute Database by ComponentAce

I have on my Form a TABSTable, TABSDatabase and a TDataSource and the data is being displayed in a TDBAdvListView, MultiSelect and RowSelect are True. I have only one Table.

When either one or more of the Items in the TDBAdvListView are selected I want to have the Database Delete the selected Records.

I have tried this way in the code below:

procedure TMain.DeleteEntry2Click(Sender: TObject);
var
  i: Integer;
begin
  with DBAdvListView1.DataSource.DataSet do
  begin
    for i := DBAdvListView1.Items.Count - 1 downto 0 do begin
    if DBAdvListView1.Items[i].Selected then
    begin
      DBAdvListView1.DataSource.DataSet.GotoBookmark(Pointer(DBAdvListView1.Items[i]));
      DBAdvListView1.DataSource.DataSet.Delete;
      end;
    end;
  end;
end;

This always results in an Error Message:

Cannot retrieve record - Native error: 10026

I have very little experience with database programming, what am I doing wrong?

Edit:

I have added a new field into the database named ID as an integer starting from 0 in the hopes that I can reference them with the Locate method and tried with the code below. This produces no error but will only delete the top record in the ListView and if I select more than one it will delete different records than selected.

My new code:

procedure TMain.DeleteEntry2Click(Sender: TObject);
var
  i: Integer;
begin
  with DBAdvListView1.DataSource.DataSet do
  begin
    DBAdvListView1.BeginUpdate;
    First;
    for i := DBAdvListView1.Items.Count - 1 downto 0 do begin
    if DBAdvListView1.Items[i].Selected then
    begin
      if dbTable.Locate('ID',DBAdvListView1.Items[i].Selected,[]) then
      dbTable.Delete;
      Next;
      end;
    end;
  dbTable.Close;
  dbTable.Open;
  DBAdvListView1.EndUpdate;
  end;
end;

The dbTable has to be Closed and Opened to see changes for some strange reason - I have tried Refresh to no avail...

Edit:

// To include Table Structure as requested...

• ID integer 0
• Title string 200
• Author string 100
• Date string 20
• Location string 60
• Category string 100
• ISBN-13 string 20
• ISBN-10 string 20

In the Absolute Database Utils directory there is a DatabaseManager.exe which I used to create the actual table with and in here I have also now set a Primary Key of the type:

Type - Primary Name - ID

The fields for the Index:

ColumnName - ID CaseInsensitive - False ASC - True MaxIndexSize - 20

Solution 1

Answer to own question... I remove the selected records with the code below:

procedure TMain.Button5Click(Sender: TObject);
var
  i: Integer;
begin
  with DBAdvListView1 do
    for i := 0 to Items.Count - 1 do
      if Items[i].Selected then
      begin
        Memo1.Lines.Add(Items[i].Caption + ' - Selected!'); //Test for Correct ID's!
      if dbTable.Locate('ID', Items[i].Caption, []) then
      DBAdvListView1.Datasource.DataSet.Delete;
    end;
  dbTable.Close;
  dbTable.Open;
end;

Solution 2

If you know the primary keys of all the records to be deleted, then you can use one SQL query statement in order to delete all the selected records in one go -

delete from table
where id in (1, 7, 15, 23, 45);

You would have to build this query manually, i.e. create the string which holds the id numbers.

Comments

[kobik]

+1 This is the approach I would have used also. you need to Refresh the TDataSet after this operation. BUT, I don't see any problem with the initial code (before OP made an edit) if the table contains a PK. I know nothing about TDBAdvListView, but if Pointer(DBAdvListView1.Items[i]) is a valid bookmark (the same as TDBGrid) I see no reason why the code should fail.

[Shambhala]

I have now found a way to do it using the Locate method with the Items[i].Caption and this keeps track of all the selected items in the list. I am going to post it as an answer to my own question. Thanks for all help.