Solved

how to generate number for multiple users

Posted on 2006-07-06
5
211 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 17

Expert Comment

by:TheRealLoki
ID: 17055743
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
ID: 17055804
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
ID: 17056008
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
ID: 17056701
>>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
ID: 17070580
Thanks.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

737 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