Solved

how to generate number for multiple users

Posted on 2006-07-06
5
205 Views
Last Modified: 2010-04-05
I seems to have a problem to generate number for multiple users. For a single user, I would search my tableA in SQL, if no such number, I will issue a number+1 based on last record. The problem arise when I have multiple user logged into the system and I need to generate numbers. The numbers generated are unique, they cannot have duplicates. My scenario is as :

Single user log in ==> Issue a number that is not in Table A


Multiple user log in==> issue a number for each user not found in Table A.

E.g                                   User A   User B  User C
Generate number:                1           2          3

Should User A exit program, the number will be reuse and given to next user. However, if all 3 users decide to save data, the number will be given to them. If User D log in, Generated number 4 will be issue.

My problem is I am not able to generate the number for multiple users. The database I am using is SQL. I have created another dummy table to hold the numbers generated, however I keep getting duplicates number issued.


I have attached my code for reference:


>>>>>>>>>>>>>>>>>>>>


procedure TMerchantEntryFrm.GenerateControlNumber;
Var i, iTemp1 : integer;
begin
 if sMode = 'Insert' then
  begin
i := 0;
iTemp1 :=0;

     with Query1 do
     begin
       Close;  SQL.Clear;
       sSQL := 'Select * From Merchant Where MerchantCode = ''' + DBMerchantCode.Text + '''' +
               ' And CompanyCode = ''' + DBCompanyCode.Text + '''' +
               ' Order By MerchantCode, CompanyCode, ControlNo ASC';
       SQL.Add(sSQL);
       Open;
     end;

     with Query1 do
     begin
          While not eof do
          begin
          if Format('%.3d', [i]) = Query1.FieldByName('ControlNo').AsString then
             begin
             i :=i +1;
             Query1.Next;
          end
          else
          begin
             iTemp1 := i;

             With QueryCheck do
             begin
             Close; SQL.Clear;
             sSQL := 'Select * from ControlNoCheck where Mercode=:Mercode '+
                     'And ComCode=:ComCode '+
                     'And FieldCheck=:FieldCheck ';
             SQL.Add(sSQL);
             Parameters.ParamByName('Mercode').value :=DBMerchantCode.text;
             Parameters.ParamByName('Comcode').value :=DBCompanyCode.text;
             Parameters.ParamByName('FieldCheck').value := Format('%.3d', [iTemp1]);
             open;
            end;

                If QueryCheck.Eof then
                begin
                break ;
                end
                else
                begin

                i := i + 1;

                end;
           end;

          end;  // while



       Query1.First;

       if Not query1.Eof then
       begin

         //MERCHANT STATUS = 'N', THEREFORE INC CONTROL NO BY 1

         if itemp1 <> 0 then
         begin
         iTemp := iTemp1;
         end
         else
         begin
         Query1.Last;
         iTemp := Query1.FieldByName('ControlNo').AsInteger + 1;
         end;
         while true do
         begin


             With QueryCheck do
             begin
             Close; SQL.Clear;
             sSQL := 'Select * from ControlNoCheck where Mercode=:Mercode '+
                     'And ComCode=:ComCode '+
                     'And FieldCheck=:FieldCheck ';
             SQL.Add(sSQL);
             Parameters.ParamByName('Mercode').value :=DBMerchantCode.text;
             Parameters.ParamByName('Comcode').value :=DBCompanyCode.text;
             Parameters.ParamByName('FieldCheck').value := Format('%.3d', [iTemp]);
             open;
            end;

            if QueryCheck.eof then

            break

            else
             itemp := itemp + 1;
         end;

        DBControlNumber.text := Format('%.3d', [iTemp]);

       Control(DBMerchantCode.text, DBCompanyCode.text,DBControlNumber.text);

       end
       else
       begin
         if Trim(DBMerchantCode.Text) = '' then
           begin
           DBControlNumber.Text := '' ;
            Control(DBMerchantCode.text, DBCompanyCode.text,DBControlNumber.text);
            end
         else
         begin
           with Query1 do
           begin
             Close;  SQL.Clear;
             sSQL := 'Select * From Merchant Where MerchantCode = ''' + DBMerchantCode.Text + '''' +
                     ' Order By MerchantCode';
             SQL.Add(sSQL);
             Open;
             DBControlNumber.Text := '000';
           end;
             Control(DBMerchantCode.text, DBCompanyCode.text,DBControlNumber.text);

         end;
       end;
     end;
  end;
end;

<<<<<<<<<<<<<<<<<<<<<


Thanks.
0
Comment
Question by:cwtang
5 Comments
 
LVL 17

Expert Comment

by:TheRealLoki
Comment Utility
You don't say what kind of SQL databse it is.
Interbase and Firebird for example have "Generators" whose job is for this purpose.
Every time you ask for a new number, it increments the generator.
If you are using the BDE for SQL you could have a 1 record table eg.
Table: UniqueNumber
Field: NextNumber: integer

and your program could use Table1.LockTable() while it retrieves the next unique number, then it updates the table with the number + 1


0
 
LVL 9

Expert Comment

by:sun4sunday
Comment Utility
For the multiuser applicaitons  Unique  Number should generate at the end of Posting only.
IF the unique/primary key generated in the begining and if the user cancel the transaction,
by the time if another user generated a unique number after the the above user there will be gap on the unique numbers. For eg : Receipts. There should not be any gaps oi\n it.

Consider creating at the end of the Posting and can aviod this

sun4sunday
0
 

Author Comment

by:cwtang
Comment Utility
Hi,
Thanks for the reply, I am using ADO to connect to the SQL database. As for the text boxes, they are just normal tedit.
I am at a loss on how to ensure that the number issue is not duplicated. My begining of th program check for table for any entries using a var i, if no entires are found, it will issued a number based on value of i. However, it will also check the controlnocheck(temp) table for any entries, if no entires number will be issued, however if there are entries, it will reloop to issue another number. The problem lies here, after I issue a number, there is no checking with merchant table to ensure that it is a unique. I am not sure(concept and code) on how to ensure that number being generated is unique and not in database, neither it is being issued to another user.

As for gaps, it is able to reuse the numbers that are not in use. However, my code has no checking with merchant table at the end of code as it has been done at the begining of the code.

Any help is appreciated.
0
 
LVL 10

Accepted Solution

by:
atul_parmar earned 500 total points
Comment Utility
>>Should User A exit program, the number will be reuse and given to next user. However, if all 3 users decide to save data, the number will be given to them. If User D log in, Generated number 4 will be issue.

For such a scenario you should not generate a number when user decides to insert. it should be generated when user actually inserts (saves) the record. the same should be returned to the user for referance.

>> My problem is I am not able to generate the number for multiple users. The database I am using is SQL. I have created another dummy table to hold the numbers generated, however I keep getting duplicates number issued

In your database you must set the field to be unique. and if possible make it auto increment. Or if you want to generate yourself then the SQL should look like the following

'Select ISNULL(MAX(ControlNo),0)+1 AS NEWCONTROLNO From Merchant Where MerchantCode = ''' + DBMerchantCode.Text + '''' +
               ' And CompanyCode = ''' + DBCompanyCode.Text + '''' +
               ' Order By MerchantCode, CompanyCode, ControlNo ASC'
then there is no need to go thrgou each record and check for duplicate
0
 

Author Comment

by:cwtang
Comment Utility
Thanks.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now