C++Builder - Database tutorial, part 5

Validating a database record includes two aspects:

• The integrity of the database has to be ensured.
• Only valid data should be accepted, such as no empty name and no negative numbers.

Database integrity

Usually, a database has a key field that must be unique for every record.
Moreover, you set restraints to this key field, for example to its length.

In our Articles database, the key field is ID:

• We won't allow more than one article with the same ID.
• In addition, we want the ID to be 4 characters.

We already dealt with double ID's when adding records.
But we also have to avoid a double ID when editing a record.

So, the storing of the ID has been changed as follows:

void __fastcall TForm1::btnOKClick(TObject *Sender)
{
  // statements...
  String IDError = "";
  // statements...
  IDError = ValidID();
    if (IDError != "") {
      if (DBMode == "A")
        UpdateUI(IDError, "A");
      else
        UpdateUI(IDError, "E");
    }
    else {  // ID is valid
      // more statements...
}

The function ValidID() returns an error message text if the ID is invalid, else it returns an empty string:

String TForm1::ValidID()
{
  int ix, L;
  String Result = "";
  String ID = edID->Text;
  L = ID.Length();
  if (L != 4)
    Result = " Invalid ID: must be 4 characters ";
  else {
    if (DBMode == "E") {
      if (Articles->Find(edID->Text, ix)) {
        if ((Grid->Row - 1) != ix)
          Result = " Invalid ID: already used for other article ";
      }
    }
  }
  return Result;
}

Data validation

It's obvious not to allow:

• an empty article name
• a negative price
• a negative stock

Here's where we check if the data are valid:

void __fastcall TForm1::btnOKClick(TObject *Sender)
{
  // statements...

    else {  // ID is valid
  InputError = ValidInputs();
      if (InputError != "") {
        if (DBMode == "A")
          UpdateUI(InputError, "A");
        else
          UpdateUI(InputError, "E");
      }
      else { // inputs are valid
        // more statements...
 }

The function ValidInputs() returns an error message text if any of the data are invalid, else it returns an empty string:

String TForm1::ValidInputs()
{
  String Result = "";
  String Name = edName->Text;
  float Price = 0;
  int Stock = 0;
  if (Name == "")
    Result = " Invalid NAME ";
  if (Result == "") {
    try { Price = StrToFloat(edPrice->Text); }
    catch (EConvertError &E) { Result = " Invalid PRICE "; }
  }
  if (Result == "") {
    if (Price < 0)
      Result = " Invalid PRICE ";
  }
  if (Result == "") {
    try { Stock = StrToInt(edStock->Text); }
    catch (EConvertError &E) { Result = " Invalid STOCK "; }
  }
  if (Result == "") {
    if (Stock < 0)
      Result = " Invalid STOCK ";
  }
  return Result;
}

Minor improvements

If nothing was changed after clicking the OK button in edit mode, we give this feedback to the user.
To this end, we saved the original field values in variables OldID, OldName, OldPrice and OldStock.
Later on, we compare the old values to the new values:

void __fastcall TForm1::btnOKClick(TObject *Sender)
{
  // statements...
        else {  // DBMode == "E"
  // statements...
          if (WasChanged(ID, Art->Name, Art->Price, Art->Stock))
            UpdateUI(" Article was modified ");
          else
            UpdateUI(" Article was not modified ");
  // more statements...
}

Our function WasChanged() looks like this:

bool TForm1::WasChanged(String ID, String Name, float Price, int Stock)
{
  bool Result = false;
  if ((ID != OldID) || (Name != OldName) ||
      (Price != OldPrice) || (Stock != OldStock))
    Result = true;
  return Result;
}