Check for a duplicate key index programmatically

Problem/Question/Abstract:

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?

Answer:

Make a generic function like:

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

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

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