At 08:31 AM 1/04/2005 +0000, you wrote:

>Hi,Groups!
>
>I use Ibobject and firebird 1.03.972 on win2k server.
>I have a application write by delphi,
>when a record before post,I need a lotno,so I write :
>procedure QDtlBeforePost(DataSet: TDataSet);
>begin
>if (qdtllotno.AsString = '') then
> qdtlLOTNO.Value :=
> afgetNewNo_str(qMainINDT.Value, 4);
>end;
>
>function afgetNewNo_str(pddate: tdate):
> string;
>var
> lignid: integer;
>begin
> QGnid.sql.Clear; //QGnid is a Tiboquery
> QGnid.sql.add('select gen_id(gn_lotno,1) from
> rdb$database');
> QGnid.ExecSQL;
> lignid := QGnid.Fields[0].AsInteger;
> Result := FormatDateTime('yymm', PDDATE)
> + inttostr((lignid + 10000000) mod 10000);
> //so if the Gnid get the value 25418
> // then the lotno will become "05035418"
>end;
>
>I though this way the lotno won't be duplicate.

Given that your calculation causes the generated number, at its current
order, to shed the first digit, you would start getting duplicates if you
spun more than 9999 numbers of this generator in a single month. If you
lost generator numbers for some reason during that month, the maximum
number of potential lot numbers might be considerably less than the
supposed 9999.

>and this application run just fine about 6 month.

Let's see about those 6 months:

October, 31 days, safe. (Not a full month's use?)
November, 30 days, safe. (Short month)
December, 31 days, safe. (Christmas holidays?)
January, 31 days, safe. (New Year holidays?)
February, 28 days, safe. (Short month)
March, 31 days, not safe. (Full month, no holidays).

>but last week, my client report they found 2 diffent records with same
>lotno.
>The time between 2 record insert is about 2hours,
>and I am sure they won't insert 10000 record within 2hours.

I don't see where "2 hours" fits in. You will begin producing duplicates
if you use up more than 9999 generated numbers in one calendar month.

Since you are using IBO, you have no need for your complicated method of
getting the generator value: use the Gen_ID() function:

function afgetNewNo_str(pddate: tdate):
string;
begin
Result := IntToStr(IB_Connection1.Gen_ID(gn_lotno,1)); // returns '25418'
Result := FormatDateTime('yymm', PDDATE)
+ copy(Result, (Length(Result) - 3), 4);
// produces '05035418'
end;

But I think you need to add two characters to the length of LotNo and
include the day part of the date in your string.

./hb