Delphi - ADOTable does not delete records properly and creates ghost records

532 views Asked by At

I need help fixing a problem with my database. I have a .mdb file that contains records of cars. This is linked to my ADOTable and then a DBGrid in Delphi. When I delete records via a button on my form, it appears to not delete it properly, because when i scroll through the dbgrid, the active record is suppose to change/update (depends whether i am scrolling down or up) and display the value of each field of the active record in edits below the DBGrid.

After my code deleted the record, It does not appear in the DBGrid or in the .mdb-file when I view it in MS Access, So I assumed it was properly deleted. But, like I explained above, when the OnMouseWheel event excecutes, it displays what i guess is either a deleted record, or the previous record's data when the pointer of the active record of the DBGrid clearly shows it should display the next or previous records data.

It is interesting to note that the OnCellClick and DBGridNavigator Buttons does not have this effect on the DBGrid and the record information displayed.

Images:

Before OnMouseWheel-event Before OnMouseWheel-event

Each image after 1 excecution: enter image description here

enter image description here Image of the actual .mdb file: enter image description here


Code for procedures and functions used:

OnMouseWheel:

procedure TCars.DBGrid1MouseWheel(Sender: TObject; Shift: TShiftState; WheelDelta: Integer; 
MousePos: TPoint; var Handled: Boolean);
begin
  Show_Car_Details;
end;

OnCellClick:

procedure TCars.DBGrid1CellClick(Column: TColumn);
begin
  Show_Car_Details;
end;

Show_Car_Details:

procedure TCars.Show_Car_Details;
begin
  with CarOwners.tbl_Cars do
  begin
    edt_Car_ID.text := inttostr(fieldbyname('ID').value);
    edt_Car_Type.text := fieldbyname('Make').value;
    edt_Car_Price.text := FloatToStr(fieldbyname('Price').value);
    edt_Car_Distance.text := inttostr(fieldbyname('Distance').value);
    edt_Owner_ID.text := inttostr(fieldbyname('OwnerID').value);

    if fieldbyname('Insurance').value = true then
    begin
      cbx_Insurance.ItemIndex := 0;
    end
    else
    begin
      cbx_Insurance.ItemIndex := 1;
    end;
  end;
end;

The Delete procedure:

procedure TCars.bit_DeleteClick(Sender: TObject);
begin
  if messagedlg
    ('Are you sure you want to delete this record? It will permanently be removed.',
    mtConfirmation, [mbyes, mbno], 0) <> mryes then
    exit;

  CarOwners.tbl_Cars.Delete;
  DBGrid1.DataSource.DataSet.Refresh;
end;

Just incase, code for the Add and update procedures as well: Add:

procedure TCars.bit_AddClick(Sender: TObject);
var
  Make: string;
  OwnerID, Distance: Integer;
  Price: real;
  Insurance: Boolean;
begin
  Make := edt_Car_Type.text;
  OwnerID := strtoint(edt_Owner_ID.text);
  Distance := strtoint(edt_Car_Distance.text);
  Price := strtofloat(edt_Car_Price.text);
  if cbx_Insurance.ItemIndex = 0 then
  begin
    Insurance := true;
  end
  else
  begin
    Insurance := false;
  end;

  Add_Record(Make, OwnerID, Price, Distance, Insurance);
end;
//---------------------------------------------------------
procedure TCars.Add_Record(Make: string; OwnerID: Integer; Price: real;
  Distance: Integer; Insurance: Boolean);
begin

  // ----validation----
  //validation done here(removed for space, just basic if with exit.)

  // add new information
  with CarOwners do
  begin
    tbl_Cars.DisableControls;
    tbl_Cars.last;
    tbl_Cars.Insert;
    tbl_Cars['Make'] := Make;
    tbl_Cars['OwnerID'] := OwnerID;
    tbl_Cars['Price'] := Price;
    tbl_Cars['distance'] := Distance;
    tbl_Cars['Insurance'] := Insurance;
    tbl_Cars.post;
    tbl_Cars.EnableControls;
  end;
end;

Update procedure:

procedure TCars.bit_UpdateClick(Sender: TObject);
var
  Brand: string;
  Price: real;
  Insurance: Boolean;
  OwnerID, Distance: Integer;
begin
  Brand := edt_Car_Type.text;
  Price := strtofloat(edt_Car_Price.text);
  OwnerID := strtoint(edt_Owner_ID.text);
  Distance := strtoint(edt_Car_Distance.text);

  if cbx_Insurance.ItemIndex = 0 then
  begin
    Insurance := true;
  end
  else
  begin
    Insurance := false;
  end;

  Update_Record(Brand, OwnerID, Price, Distance, Insurance);
end;
//------------------------------------------------------------
procedure TCars.Update_Record(Make: string; OwnerID: Integer; Price: real;
  Distance: Integer; Insurance: Boolean);
begin
  //validation done here(removed for space, just basic if with exit.)

  // ----Update Information ----
  with CarOwners do
  begin
    tbl_Cars.DisableControls;
    tbl_Cars.edit;
    tbl_Cars['Make'] := Make;
    tbl_Cars['OwnerID'] := OwnerID;
    tbl_Cars['Price'] := Price;
    tbl_Cars['Distance'] := Distance;

    if Insurance then
    begin
      tbl_Cars['Insurance'] := true;
    end
    else
    begin
      tbl_Cars['Insurance'] := false;
    end;
    // ShowMessage('Posting...');
    tbl_Cars.post;
    // ShowMessage('Done');
    tbl_Cars.EnableControls;
  end;
end;

Any advice or assistance is welcome!!! Regards

1

There are 1 answers

1
Romans On BEST ANSWER

Thanks to @MartynA and @Olivier for the answer. The problem was using the wrong event handler to refresh and display the record's field values.

DO NOT USE: OnMouseWheel

USE:

procedure TCarOwners.ds_CarsDataChange(Sender: TObject; Field: TField);
begin
  if Field = nil then
  begin
    Cars.show_car_details;
  end;
end;

This updates the non data-aware controls correctly. Be sure to add the Form1 or in my case Cars_frm to the uses list directly under implementation.