2

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

Shambhala
  • 1,159
  • 3
  • 13
  • 31
  • What is the meaning of the error message? – No'am Newman Oct 18 '12 at 07:38
  • @No'amNewman - I no longer get this Error Message - I have changed my code which I will post as an edit. – Shambhala Oct 18 '12 at 16:36
  • You should add a primary key to your table (for example `ID`). then your first code should work. – kobik Oct 18 '12 at 17:36
  • @Shambhala after a few trials none of which succeeded I think your first code should work, but we're definitely missing a point. Would you paste your table structure so that we use a TQuery to send a DELETE statement. I think something is wrong with _GotoBookmark(Pointer(DBAdvListView1.Items[i]))_ part. – Ertunç Oct 18 '12 at 17:54
  • @Ertunç, no need for DELETE statements. since there is no PK, the record cannot be located in the DB. I would however verify that `GotoBookmark` does work, by commenting the `DataSet.Delete`, placing a delay or a messagebox and verifying that the selected records get located in the `TDBAdvListView`. – kobik Oct 18 '12 at 18:06
  • I see. Then visually the code is ok, but internally the missing primary key makes it impossible to locate data. A glimpse of table structure could have saved us the effort. – Ertunç Oct 18 '12 at 18:17
  • Thanks for your help @Kobik & +@Ertunç - I really do appreciate your efforts. I will post my Table structure in an Edit as it would look awful as a comment... – Shambhala Oct 18 '12 at 19:21
  • There's no need for the 'next' statement. – No'am Newman Oct 19 '12 at 04:20

3 Answers3

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.

No'am Newman
  • 6,395
  • 5
  • 38
  • 50
  • +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. – kobik Oct 19 '12 at 11:21
  • 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. – Shambhala Oct 21 '12 at 16:52
0

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;
Shambhala
  • 1,159
  • 3
  • 13
  • 31
0

second code will work if you just remove the Next; from the if section. Since u're already inside a 'for' loop, there's no need to use the Database.Next.

Mobius1
  • 1