Trapping Key Violation error

[yomyom]

Dear all,
How do I trap a potential key-violation ?
I am using a grid as an entry form and am permitting the user to click the insert button and enter the text for a new record.
I then (obviously) want to check that the user is not trying to enter an account number that already exists.
I tried the beforeaction event of the DBNavigator thus:
begin
case button of
nbPost: checkNumber;
end;

procedure checkNumber;
begin
table1.setkey;
if (table1.findkey etc..
begin
showmessage('Duplicate account numbers not allowed');
dbnavigator1BtnCkick(nbCancel);
end;
end;
*********
Needless to say, when I deliberately enter an account number that exists, I find that the key violation error occurs before it executes any of my code.

So please, how do I trap the Key violation ? I really want to use a grid for my data entry because it allows editing and entry of new data in the same form thus saving me from having to code seperately.
Thanks inadvance for your help and sorry its long winded.
yomyom.

 

[svanels]

Define your referential constraints in the Database itself. If you are using Interbase or paradox, the executable version of your program will handle the key violations with a message box, and your program will run further. The same applies when you have different types of users with several rights (update, insert, delete, view etc.)
The Database server is intelligent enough to prevent the inclusion of garbage in a gracefull way, when you once have defined the rules. Steven van Els
SAvanEls@cq-link.sr

 

[LucieLastic]

hi

As Steven suggests, add your contraint to the Primary field of the relevant table and stick your Post command in a TRY EXCEPT.

something like this:-

function TObj.PostToDB : boolean;
begin
result := false;
TRY
MyQuery.Post;
result := true;
EXCEPT
on EAccessViolation do
Showmessage('This account exists');
else ShowMEssage('DB error, unable to post');
END;
end;

lou

 

[svanels]

Nice constuction Lou! Steven van Els
SAvanEls@cq-link.sr

 

[LucieLastic]

Thanks, Steven

I think I'd be tempted to make the function a bit more generic though:

:
if PostToDB(MyTable) then ...

function TObj.PostToDB(DataSet : TDBDataset) : boolean;
begin
result := false;
TRY
DataSet.Post;
result := true;
EXCEPT
on EAccessViolation do
Showmessage('This record exists');
else ShowMEssage('DB error, unable to post');
END;
end;

 

[svanels]

In general I use generators for my keyfields, wich are obtained from the Database Server (Interbase) itself. This takes away the problem of defining an unique identifier your self. For paradox there is a nice work around which can be found in the MastApp demo.

In this example, every customer (table Cust) has an unique ID field. The next customer ID is stored in a 1 record table called NextCust

Consider the before post event of the Customers table

procedure TMastData.CustBeforePost(DataSet: TDataSet);
begin
if Cust.State = dsInsert then
with NextCust do
begin
Open;
try
Edit;
CustCustNo.Value := NextCustNewCust.Value;
NextCustNewCust.Value := NextCustNewCust.Value + 1;
Post;
finally
Close;
end;
end;
end; Steven van Els
SAvanEls@cq-link.sr

 

[BTecho]

Hello.

Actually your code would've work with one small modification.

procedure checkNumber;
begin
if (table2.findkey etc..
begin
showmessage('Duplicate account numbers not allowed');
dbnavigator1BtnCkick(nbCancel);
end;
end;

instead of using FindKey for Table1, add another TTable to your form call it Table2, point it to the same Table file and use FindKey for Table2.

 

[LucieLastic]

I think you should use a Constraint rather than FindKey. It's the proper way as regards database design and you leave all the work to the Server.

lou