ADS 11.10 ADT tables in a DD. Delphi. We have a problem with two users getting the same invoice number. It only happens if two users request an invoice number at the exact same time. We have a table with one record that keeps the last invoice number. Here is what we do when there is a request for an invoice number.

AdsTable1.edit;
MyInteger := AdsTable1.FieldByName('TNumber').AsInteger;
inc(MyInteger);
AdsTable1.FieldByName('TNumber').AsInteger := MyInteger;
AdsTable1.post;

I thought that no other user was able to open that record in between Edit and Post but it is happening. Anyway to be sure that two users don't get the same number?

The temporary solution we have now, is to check if the number exist in the invoice table before we post it, but that again will only work if no other user are doing the same at the exactly same time.

Thanks, Kim

asked 20 Aug '15, 02:53

KimHJ's gravatar image

KimHJ
516343950
accept rate: 16%


The old value might still exist in the client side cache. I'm not sure if a refresh after the edit keeps the edit status (I fear it releases it!).
An approach I usually use: a rowversion field for double checking and SQL.

alter table test add column rv rowversion;
now use it for unique numbers:
declare @num integer;
declare @rv integer;
declare @numrows integer;
declare @c cursor as select tnumber, rv from test;
@numrows=0;
while @numrows=0 do
  open @c;
  fetch @c;
  @rv=@c.rv;
  @num=@c.tnumber+1;
  close @c;
  update test set tnumber=@num where rv=@rv;
  @numrows=::stmt.UpdateCount;
end;
select @num from system.iota;

link

answered 20 Aug '15, 11:58

Joachim%20Duerr's gravatar image

Joachim Duerr
8.7k1031133
accept rate: 18%

Do you allow duplicate invoice numbers? A simple unique index on the invoice number field would prevent such a scenario.

From your code it seems that you are using a separate table to store the last invoice number.

I would rather use a "SELECT MAX() + 1" approach combined with the unique index.

If you run the number generating step in the BeforePost event it should be very rare that two users get the same number that way. When it happens the database will prevent it (via the index) and you can catch the exception and show a "Please try again" kind of error.

link

answered 20 Aug '15, 15:24

Jens%20M%C3%BChlenhoff's gravatar image

Jens Mühlenhoff
1.3k51037
accept rate: 18%

Yes we allow because the number gets to big so our unique index is Invoicenum;Datein. The user don't see the invoice number before the invoice is created and printed. so we could do a BeforePost as we do now as a work around.

(20 Aug '15, 22:07) KimHJ
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×271
×172
×55

Asked: 20 Aug '15, 02:53

Seen: 1,780 times

Last updated: 20 Aug '15, 22:07

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.