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 postingAt 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.
- Start Lazarus and open project GuitarClub2.
- 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.
- 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).
- When the application starts, both of the additional panels are hidden (look at FormShow).
- 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;
- 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;
- 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;
- 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;
- The other events for the Guitars table are handled similarly as for the Members.
- 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;
|
|