Author: Tomas Rutkauskas
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:
1 2 function TMyForm.CheckDuplicateKey(ATable: string; const Field: TField): Boolean;
3 var4 cSQL, KeyField, cValue: string;
5 begin6 KeyField := Field.FieldName;
7 cValue := Field.AsString;
8 cSQL := Format('select %s from %s where %s = %s', [KeyField, ATable, KeyField,
9 cValue]);
10 with LookupQuery do11 begin12 SQL.Clear;
13 SQL.Add(cSQL);
14 Open;
15 if RecordCount > 0 then16 Result := True
17 else18 Result := False;
19 Close;
20 end;
21 end;
22 23 //and use it in your key field's OnValidate handler like:24 25 procedure TMyForm.MainQueryMyIDValidate(Sender: TField);
26 begin27 if CheckDuplicateKey('MyTable', Sender) then28 raise Exception.Create('The table already has a record with this key.');
29 end;