There is a white paper on IBO's Homepage
addressing that issue.

----- Original Message -----
From: "jeffplata" <jeffplata@...>
To: <IBObjects@yahoogroups.com>
Sent: Thursday, June 12, 2008 3:20 AM
Subject: [IBO] Auto-resolve conflicting auto-numbers


> Hello,
>
> I have this program that assigns unique number (JEVNO) to each record
> added in a table (JEVS). it's not an auto-increment field, its a
> varchar type assigned by the client program, defined unique in the
> table. When more than one users are using the program at the same
> time, it is always possible that they generate identical JEVNO
> numbers, so the program throws an EIB_ISCError ERRCODE 335544665. The
> user beaten to using the contested number can manually change the
> number, then save. It works, but rather inconvenient. I was trying to
> use tib_query.OnError event, but it seems I am not permitted to do a
> Post inside the onerror event. My OnError code is shown below. What I
> am trying to do is autoresolve the number conflict then save without
> the user having to change it manually. What is the better way to do this?
>
> TIA
> Jeff
>
> [code starts here]
> procedure Tdm_jevs.qrJevsError(Sender: TObject; const ERRCODE: Integer;
> ErrorMessage, ErrorCodes: TStringList; const SQLCODE: Integer;
> SQLMessage, SQL: TStringList; var RaiseException: Boolean);
> begin
> if e.ERRCODE = 335544665 then
> // violation of PRIMARY or UNIQUE KEY
> if MessageBox(0, 'Duplicate JEV Number found.'+#13+#10+
> 'Would you like the program to automatically resolve the problem?',
> 'Duplicate JEV Number',
> MB_ICONQUESTION or MB_YESNO or MB_DEFBUTTON1) = idYes then
> begin
> with TIB_DSQL.Create(nil) do
> try
> sql.Add('select max(JEVNO) JEVNO from jevs where jevperiod='+
> quotedstr(GetCurrentPeriod));
> try
> Execute;
> newnumber := FieldValues['JEVNO'];
> IncJevNo(newnumber);
> qrJevs.FieldByName('JEVNO').asstring := newnumber;
> qrjevs.Post;
> // the Post never gets executed
> // Shows message 'Record is not inserted.' instead
> except
> showmessage('Auto-resolve failed.'#13#10'Please change JEV
> number manually.');
> abort;
> end
> finally
> free;
> end;
> end;
> RaiseException := false;
> end;
> [code ends here]
>
>
> [table DDL, just in case]
> CREATE TABLE JEVS(
> ID Integer NOT NULL,
> JOURNALTYPE Varchar(4) NOT NULL,
> JEVNO Varchar(11) NOT NULL,
> JEVDATE Date DEFAULT 'now' NOT NULL,
> JEVPERIOD Varchar(6) NOT NULL,
> REFNO Varchar(20),
> REFNO2 Varchar(15),
> TOTALDR DM_MONEY DEFAULT 0.00,
> TOTALCR DM_MONEY DEFAULT 0.00,
> PARTICULARS Varchar(240),
> ENTRYDATE Date DEFAULT 'now',
> VOUCHERNO Varchar(11),
> CHECKNO Varchar(10),
> POSTED Char(1) DEFAULT '0' NOT NULL,
> CONSTRAINT PK_JEVS PRIMARY KEY (ID),
> CONSTRAINT UNQ_JEVS_JEVNO UNIQUE (JEVNO)
> );
>
>
> ------------------------------------
>
> ___________________________________________________________________________
> IB Objects - direct, complete, custom connectivity to Firebird or
> InterBase
> without the need for BDE, ODBC or any other layer.
> ___________________________________________________________________________
> http://www.ibobjects.com - your IBO community resource for Tech Info
> papers,
> keyword-searchable FAQ, community code contributions and more !
> Yahoo! Groups Links
>
>
>