Free Pascal
LAZARUS

Database Tutorial - part 3

If you play for a while with the GuitarClub project that we presented in Part 2 of our tutorial, you'll note some annoying behaviors when modifying records:

  • When adding or editing a record, the new field values are "posted" automatically when you move to another record.
  • You can set fields to unwanted values, such as leaving a field empty, or creating several dbfMembers records with the same ID. Thus the "integrity" of the database is compromized.

Prevent automatic posting

At the start of an insert or edit of a table, disable the DBGrid. Afterwards, enable it again.

Validation of field data

  • Don't post a record if a field is illegal, e.g. when it's empty; instead, show a message to the user.
  • When adding a dbfMembers record, let the program fill the field ID with a value that doesn't exist yet. For example, if the highest ID was '0010', the next ID must be '0011'.
    Attention: when in insert mode, you can not simply search the table for the highest ID, because moving to another record would automatically post the current record. We must open a second instance of the table, indexed on ID, and navigate to the last record.

The improved version

By default, a DBNavigator has buttons for Edit, Insert, Post and Cancel that automatically send commands to the attached dataset. That's fine for a quick test. But for a reliable database application, it's better to remove the built-in Post and Cancel buttons and send the appropriate commands ourselves, giving us greater control of what and when things happen.

For this project, create a folder FPlaz\GuitarClub2, download GuitarClub2.zip and unzip to this folder.

  1. Start Lazarus and open project GuitarClub2.
  2. Note that we added some components:

    • A second TDbf for the members, named dbfMembersID and indexed on ID.
    • A TDBNavigator for dbfGuitars.
    • The panels panMembers and panGuitars. One of these will be visible when we are updating a table, and it will be hidden later on.
    • On each panel, we have TDBEdit components that are linked to the fields that we want to update manually.
    • Each panel also contains a DBText component, for displaying the ID fields. The contents of these fields are handled by the program code.
  3. We have hidden the buttons POST and CANCEL of the DBNavigators and replaced them with our own buttons, in order to improve controlling the behavior of the GUI (Graphical User Interface).
  4. When the application starts, both of the additional panels are hidden (look at FormShow).
  5. There is an event handler for the BeforeEdit event of dbfMembers. That same event handler is tied also to the BeforeInsert event, so we named it appropriately:

    procedure TForm1.dbfMembersBeforeInsEdit(DataSet: TDataSet);
    begin
      panMembers.Show;
      ShowHideUI; //
    disable grids and dbnavigators
    end
    ;
  6. We have an event handler for the AfterInsert event of dbfMembers, that calculates and assigns the next member ID:

    procedure TForm1.dbfMembersAfterInsert(DataSet: TDataSet);
    var
      IDNr: integer;
      ID: string;
    begin
      dbfMembersID.Open;
      dbfMembersID.Last;
      IDNr := StrToInt(dbfMembersID.FieldByName('ID').AsString);
      ID := IntToStr(IDNr + 1);
      while Length(ID) < 4 do
        ID := '0' + ID;
      dbfMembers.FieldByName('ID').AsString := ID;
      dbfMembersID.Close;
    end;   
  7. There are handlers for btnMembersPost and btnMembersCancel:

    procedure TForm1.btnMembersPostClick(Sender: TObject);
    begin
      if Trim(edLastname.Text) = '' then
        ShowMessage('Empty LASTNAME not allowed. Please correct.')
      else begin
        dbfMembers.Post;
        panMembers.Hide;
        ShowHideUI; // enable grids and dbnavigators
      end;
    end;


    procedure TForm1.btnMembersCancelClick(Sender: TObject);
    begin
      dbfMembers.Cancel;
      panMembers.Hide;
      ShowHideUI; // enable grids and dbnavigators
    end;
  8. The AfterInsert event handler for dbfGuitars assigns the correct member ID to the Guitars record:

    procedure TForm1.dbfGuitarsAfterInsert(DataSet: TDataSet);
    begin
      dbfGuitars.FieldByName('MEMBERID').AsString :=
        dbfMembers.FieldByName('ID').AsString;
    end;
  9. The other events for the Guitars table are handled similarly as for the Members.
  10. The procedure ShowHideUI centralizes the handling of the GUI:

    procedure TForm1.ShowHideUI;
    begin
      gridMembers.Enabled := (not panMembers.Visible) and
       (not panGuitars.Visible);
      gridGuitars.Enabled := gridMembers.Enabled;
      DBNavMembers.Enabled := gridMembers.Enabled;
      DBNavGuitars.Enabled := gridMembers.Enabled;
      if panMembers.Visible then edLastname.SetFocus
      else if panGuitars.Visible then edBrand.SetFocus;
    end;