Delphi Community database tutorials, part 5


Validating field values

In a database application, the integrity of the database is very important. It's necessary to validate certain fields before storing them in the database.
In our case, we make sure that the field "ID" is not empty and that it has a unique value in each record.

To this end, we wrote the function IsValid. This function returns TRUE if the new ID is valid, or FALSE if it's invalid.

To start with, we check if the ID is not empty.

Next, we check if the ID is not already used in another record:

* We open a copy of the database.
* We check if there is no other record with the same ID value.

Let's go!

  1. In the interface section, under Private declarations, add the following lines:
    OldID: string;
    function IsValid: Boolean;
  2. In the implementation section, modify procedure cdsUfoBeforeEdit as follows:
    procedure TForm1.cdsUfoBeforeEdit(DataSet: TDataSet);
    begin
      // Remember the original ID
      OldID := cdsUfo.FieldByName('ID').AsString;
      EditGUI(True);
    end;
  3. Drop an additional TClientDataBase component on the form and name it cdsUfo2.
  4. In the implementation section, add the following function:
    function TForm1.IsValid: Boolean;
    var
      ID: string;
    begin
      ID := edID.Text;
      Result := Length(Trim(ID)) > 0;
      if not Result then
        ShowMessage('Field ID is empty. Please, enter something.')
      else if (cdsUfo.State = dsInsert) or
              ((cdsUfo.State = dsEdit) and (ID <> OldID)) then begin
        cdsUfo2.CloneCursor(cdsUfo, False, False); // copy cdsUfo
        cdsUfo2.Open;
        cdsUfo2.IndexFieldNames := 'ID';
        Result := not cdsUfo2.FindKey([ID]);
        cdsUfo2.Close;
        if not Result then
          ShowMessage('Double ID. Please, enter another ID.');
      end;	
    end;
    
  5. Finally, let's modify the procedure btnSaveClick:
    procedure TForm1.btnSaveClick(Sender: TObject);
    begin
      if IsValid then
        cdsUfo.Post;
    end;

Finding keywords

It would be nice if we could easily find a record with a certain keyword in its description or location.

  1. Drop a TEdit to the right of the DBNavigator and name it edSearch.
  2. Drop a TSpeedButton to the right of the TEdit and name it btnSearch.
  3. Add an OnClick event handler for btnSearch:
    procedure TForm1.btnSearchClick(Sender: TObject);
    var
      IsFound: Boolean;
      ID, Desc, Locat: string;
    begin
      ID := cdsUfo.FieldByName('ID').AsString; // remember were we are
      cdsUfo.DisableControls;
      cdsUfo.First;
      IsFound := False;
      while (not cdsUfo.EOF) and (not IsFound) do begin
        Desc := UpperCase(cdsUfo.FieldByName('DESC').AsString);
        Locat := UpperCase(cdsUfo.FieldByName('LOCATION').AsString);
        IsFound :=
          (Pos(UpperCase(edSearch.Text), Desc) > 0) or
          (Pos(UpperCase(edSearch.Text), Locat) > 0);
        if not IsFound then
          cdsUfo.Next;
      end;
      if IsFound then
        DBGrid1.SetFocus
      else
        cdsUFO.FindKey([ID]); // return to old record
      cdsUfo.EnableControls;
    end;
  4. Here's a nice touch: when you press ENTER after entering something in edSearch, button btnSearch will be clicked automatically. Add an OnKeyPress event handler to edSearch and complete it as follows:
    procedure TForm1.edSearchKeyPress(Sender: TObject; var Key: Char);
    begin
      if Key = #13 then
        btnSearch.Click;
    end;
    

Go to a webpage

In our database, in the field LINK2, we added some links to websites. This was only completed for a few records.

  1. Drop a TSpeedButton to the right of the btnSearch and name it btnWeb.
  2. Drop a TLabel to the right of btnWeb and name it lblURL.
  3. Add an OnClick event handler for btnWeb and complete it as follows:
    procedure TForm1.btnWebClick(Sender: TObject);
    var
      URL: string;
    begin
      lblURL.Caption := '';
      URL := Trim(cdsUfo.FieldByName('LINK2').AsString);
      if Length(URL) > 0 then begin
        lblURL.Caption := URL;
        ShellExecute(handle, nil, PChar(URL), nil, nil, SW_SHOWNORMAL);
      end;
    end;
  4. Add "ShellApi" to the end of the uses clause near the top of Unit1. Now it should read something like:

    uses
       (Names of several Delphi units...), ShellApi;

Customize the appearance

Under Project / Options / Appearance, we selected "Iceberg Classico" before compiling the program. This changes the appearance of the final application (shape and colour of certain elements).

Part 4 Database Tutorials - Part 4


Crash Course Delphi  Database tutorials Delphi Starter  FAQ  Tips  Source Code  Downloads  Links