How to check for a duplicate key index programmatically (Views: 705)
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; |