Trap key violation

Question

hi i'm trying to do some exception handling and intercept a repeated field value (key violation) error. From my searching for a solution ive seen many suggestions to trap all errors using

try
(enter code)
except on E: EDatabaseError do
showmessage (Error message);
end;

but I'd like to respond specifically to a key violation, it uses an access table using ADO.

What specifically identifies the exception that you wish to trap? — David Heffernan, Apr 15 '12 at 20:29
when i add a new client to my system with a Client ID (the primary key) which already exists and the following message is returned, - raised exception class EOleException with message 'The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship' — Jeowkes, Apr 15 '12 at 20:34
then if i run again the same message is returned but with EDatabaseError instead of EOleException — Jeowkes, Apr 15 '12 at 20:36
yes but in the error message i want to be able to state that The client ID entered already exists, and using general exception could trap various other errors which have no relation to client ID already existing surely — Jeowkes, Apr 15 '12 at 20:40
Take a look e.g. on [`this article`](http://delphi.about.com/od/database/l/aa103001a.htm). — TLama, Apr 15 '12 at 22:16

score 7 · Accepted Answer · edited Apr 15 '12 at 21:19

7

This will work, if the only error you're wanting to handle is the one with the 'duplicate value' message:

try
  // Your code
except
  on E: EOleException do
  begin
    // The better way is to find out what E.ErrorCode is
    // for this specific exception, and handle it instead
    // of checking the string - you didn't provide the
    // ErrorCode, though.
    // If E.ErrorCode = <whatever> then
    //
    if Pos('duplicate value', E.Message) > 0 then
      // You've got a duplicate with the message above
      // Do whatever handles it
    else
      raise;
  end;
  // If you want to handle other exception types (eg., EDataBaseError),
  // you can do so here:
  //  on E: EDataBaseError do
  //    HandleDBError;
end;

edited Apr 15 '12 at 21:19

David Heffernan

601,492
42
1,072
1,490

answered Apr 15 '12 at 20:43

Ken White

123,280
14
225
444

Is it possible that localization could make this fail on non-English systems? Just a thought! Also, your code swallows all EOleException exceptions. – David Heffernan Apr 15 '12 at 20:53
Just updated my answer to provide a better solution that doesn't have this problem. See the comments above the `Pos` line. :) – Ken White Apr 15 '12 at 20:57
@user use debugger or logging – David Heffernan Apr 15 '12 at 21:01
2

@user1277240, just cause the exception to happen, and in the exception code do a `ShowMessage(Format('ErrorCode: %d', ]E.ErrorCode]));` to get the ErrorCode; it will always be the same, where the message can change if the language changes (as David pointed out). – Ken White Apr 15 '12 at 21:20
2

@David, thanks for catching the `else raise` I missed in my last revision. I thought I'd added it, just noticed I didn't, and when I went in to edit it was there. Thought I was going crazy until I went back and refreshed the question page and saw your edit. :) – Ken White Apr 15 '12 at 21:23

score 6 · Answer 2 · answered Apr 15 '12 at 21:28

The EDatabaseError is just a generic excption class without additional information about the error, to get extended information about a error in ADO, you must use the TADOConnection.Errors property to get the specifc error code when a Key violation exception is raised, for this check the Number and NativeError properties.

You can found more documentation about this topic here

David Heffernan · Answer 3 · 2012-04-15T21:18:27.207

3

From your description, this doesn't sound like something that you should let get as far as an exception. Especially if all you have to go on is EOleException. Exceptions should be for things you don't have a good way to handle and that's not the case here.

I suggest that you check that the new ID is not already in use before you attempt to add the new record. Or, as @TLama suggests, take advantage of any error handling facilities of your DB framework that let you hook this before it becomes an exception.

edited Apr 15 '12 at 21:18

answered Apr 15 '12 at 20:48

David Heffernan

601,492
42
1,072
1,490

@ken transactions deal with 2 and you have a point about 1. Maybe the perf is too much of an issue. I'm thinking from the programming side rather than the db perspective. – David Heffernan Apr 15 '12 at 20:58
But this can most probably be *catched* in some of the DB data serving control event. – TLama Apr 15 '12 at 21:01
@TLama, it doesn't matter where it gets caught. It still has to get caught. – Ken White Apr 15 '12 at 21:02
@Ken, I'm talking about proper ADO control error handling, not about *press button and wait if I get exception* ;-) E.g. `TClientDataset` errors might be handled [`this`](http://stackoverflow.com/a/10091304/960757) way for instance. – TLama Apr 15 '12 at 21:08
@TLama, but that only applies to a CDS. I'm not aware of any equivalent for ADO (but I'll look). :) – Ken White Apr 15 '12 at 21:09
@Ken, don't know what OP uses, in his previous question it was `TADOQuery` and there's prefect event for this, the [`OnPostError`](http://docwiki.embarcadero.com/Libraries/XE2/en/Data.DB.TDataSet.OnPostError) and there you can affect [`what will happen`](http://docwiki.embarcadero.com/Libraries/XE2/en/Data.DB.TDataAction). I'm just beating on the way of error handling, because it seems to be strange to get the errors from different DB layers, it should be handled somewhere, IMHO. – TLama Apr 15 '12 at 21:13
1

@TLama, sounds like a good place to me (if the poster is using `TADOQuery`). – Ken White Apr 15 '12 at 21:16

Trap key violation

3 Answers3