Catching Key Violations

I'm trying to write code that cathes key violations in a Paradox table's composite key (the key is made up of two fields).  What code should i use to 1) catch the violation before it happens (maybe onBeforePost or onPostError?) and 2) cancel the record.
LVL 3
d4jaj1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ronit051397Commented:
Usually you catch key violation on the OnBeforePost event.
In there write a code that checks key violation or use try, if there is a key violation, than cancel posting by using the 'Cancel' procedure or the 'Abort' procedure.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
d4jaj1Author Commented:
Okay, then why am I having problems with the below code?

procedure TfrmActivity.tblActivityBeforePost(DataSet: TDataSet);
begin
  try
    tblActivity.Post;
  except
     on E: EDBEngineError do begin
       if(E.ErrorCount > 0) And
        (E.Errors[0].ErrorCode = DBIERR_KEYVIOL) then
           ShowMessage('You''ve inserted the duplicate record');
    end;
end;
end;

I get an exception on the Key Violation stating "EStackOverflow  with message 'StackOverflow'"  What does this mean and what am I doing wrong in my error code?
0
ronit051397Commented:
Don't use Post on the event 'beforePost', becuese you are creating a kind of "Recursion". Use Post on other events.
0
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

d4jaj1Author Commented:
When I take of the line that contains 'Post', doesn't get to the EXECPT block and Delphi stops, probably because the code I have doesn't cath the key violation.  When I pasted the code below to the onPostError, it still wouldn't work.  Do you know the correct code to catch the key violation?

If  E: EDBEngineError then
  if(E.ErrorCount > 0) And (E.Errors[0].ErrorCode = DBIERR_KEYVIOL) then
    table1.cancel;
    ShowMessage('You''ve inserted the duplicate record');
0
ronit051397Commented:
procedure TForm1.Table1PostError(DataSet: TDataSet; E: EDatabaseError;
  var Action: TDataAction);
begin
  if E.message = 'Key violation.' then Action:=daAbort; //cancel posting
end;

commets:
1. This works for delphi3. I think in delphi2 you should write
'Key Violation' without the dot in the end
2. Set Integrated Debugging to False.

0
d4jaj1Author Commented:
Excellent, that works!  However, I have other 2 questions (that's why I raised the points.

1.  Why wouldn't this work with the Integrated Debugger ON?  If I cut it off, doesn't that mean Delphi woun't tell me if other errors occur?  Futhermore, the code I entered is in the onPostError - shouldn't that be a reason for Delphi not to stop on its own - because I wrote an exception handler?
2.   My key field is called Name.  A value 'ANIS' already exists.  If I enter 'anis' in lowercase, no keyviolation occurs.  Why and how can I stop this?  I'm using the BDE and Paradox tables.
0
ronit051397Commented:
Answer 1:
The reason you need to set off the integrated debugging is because the abort action is a kind of silence exception.
Here is an explanation from Delphi help:

Delphi applications handle most exceptions that your code doesn't specifically handle by displaying a message box that shows the message string from the exception object. You can also define "silent" exceptions that do not, by default, cause the application to show the error message.
Silent exceptions are useful when you don't intend to handle an exception, but you want to abort an operation. Aborting an operation is similar to using the Break or Exit procedures to break out of a block, but can break out of several nested levels of blocks.

Silent exceptions all descend from the standard exception type EAbort. The default exception handler for Delphi applications displays the error-message dialog box for all exceptions that reach it except those descended from EAbort.
There is a shortcut for raising silent exceptions. Instead of manually constructing the object, you can call the Abort procedure. Abort automatically raises an EAbort exception, which will break out of the current operation without displaying an error message.

Example

The following code shows a simple example of aborting an operation. On a form containing an empty list box and a button, attach the following code to the button's OnClick event:

procedure TForm1.Button1Click(Sender: TObject);
var  I: Integer;
begin
 for I := 1 to 10 do      { loop ten times }  begin
   ListBox1.Items.Add(IntToStr(I));      { add a numeral to the list }
   if I = 7 then Abort;      { abort after the seventh one }
 end;
end;

try to use the Cancel maethod instead of daAbort, maybe it will work.

Answer 2:
Yes. I am aware of this problem. In my oppinion, you should check this matter before posting a record and by useing this method:

if not Table1.Locate('Name', 'ANIS',[loCaseInsensitive]) then Table1.Post;

The Locate method will locate also 'anis', 'aNiS' etc.

0
d4jaj1Author Commented:
That's the best answer I've received from this site.  Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.