Check for a duplicate key index programmatically (Views: 100)


I have a DBISAM 2.04 table with several indexes. It actually lists project details. One field is the ProjectNo (a text field some 20 char wide). I want to make sure that the same PropjectNo is not entered twice. I could make the index unique, and that would no doubt work. But the error message returned in not very user friendly - I would rather trap it myself. I assume that in the OnBeforEInsert event I would have some code that checks to see if this index key already exists. If so, then I warn the user (perhaps even allowing the record to be saved if the user insists). And then aborting the save if a duplicate. How do I find an existing key, i.e. something like KeyExists(['99023']) ? Would I have to do a Locate or something?


Make a generic function like:

function TMyForm.CheckDuplicateKey(ATable: string; const Field: TField): Boolean;
  cSQL, KeyField, cValue: string;
  KeyField := Field.FieldName;
  cValue := Field.AsString;
  cSQL := Format('select %s from %s where %s = %s', [KeyField, ATable, KeyField,
  with LookupQuery do
    if RecordCount > 0 then
      Result := True
      Result := False;

and use it in your key field's OnValidate handler like:

procedure TMyForm.MainQueryMyIDValidate(Sender: TField);
  if CheckDuplicateKey('MyTable', Sender) then
    raise Exception.Create('The table already has a record with this key.');

<< Back to main page