?
Solved

how to generate number for multiple users

Posted on 2006-07-06
5
Medium Priority
?
215 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 2000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Course of the Month13 days, 6 hours left to enroll

777 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