Delphi Pages Forums  

Go Back   Delphi Pages Forums > Delphi Forum > General

Lost Password?

Reply
 
Thread Tools Display Modes
  #1  
Old 07-08-2018, 09:29 PM
Marsheng Marsheng is offline
Senior Member
 
Join Date: Nov 2008
Posts: 299
Default Ensuring a unique number in table

When the user enters a race number, I need to ensure it is unique in that table.

I cant use a locate as that finds the first record and that maybe the current record so is valid.

There are discussion about setting the unique property in the SQL but I'm not sure how to implement that.
Reply With Quote
  #2  
Old 07-09-2018, 07:33 AM
Norrit Norrit is offline
Moderator
 
Join Date: Aug 2001
Location: Landgraaf
Posts: 7,292
Default

UNIQUE in SQL would do the trick. When you insert a record that validates the unique the insert will fail.
Only thing for this you need to alter your table and add the unique constraint.
Should be something like (TSQL):
alter table yourtable add constraint UC_yourcolumn unique(yourcolumn);

And if the race number is not important you could set it to autoincrement (if it's an int variant), this way you don't have to pass it and a new number is generated at each insert
Reply With Quote
  #3  
Old 07-09-2018, 10:04 AM
Marsheng Marsheng is offline
Senior Member
 
Join Date: Nov 2008
Posts: 299
Default

Thanks I found the Unique setting by adding an index to the table.

Now I'm not sure how to handle the error !

There are several OnXXXerror on the table events. All my tables are in a Data Module and this can't show messages, as far as I know.

I need something on the form that pops up a message that tells the user that they have entered a duplicate number.

There may be other ways of doing this, eg occurrence count.
Reply With Quote
  #4  
Old 07-09-2018, 10:31 AM
Norrit Norrit is offline
Moderator
 
Join Date: Aug 2001
Location: Landgraaf
Posts: 7,292
Default

What happens now if you have any errors in your datamodule? (connection lost, syntax error, ...)
Because this will be the same problem, your UI doesn't know...
What I'm basically saying you should think of a mechanism to notify the UI that something went wrong from you datamodule. This has to be done whatever solution for this problem you want to implement!

The Unique is nothing more then a database restriction, so it's a bit trivial to do for example your length validations client side and this only on saving.
You could build an IsUnique function which you call from your client. Nothing more then a Count=0 where number=newnumber. But this requires another database call and is not safe in a multi-user environment (it's a good indication though).

Not sure how the OnXXXerror table events work, never used a table (did it all directly through query). But that should be an easy test, just implement all with a ShowMessage and assign a number already in your db.

As for the how to show this back to your UI again is a bit more tricky. Perhaps some own message handling on which UI objects can subscribe. But this is an issue you already have, this specific problem asside...
Reply With Quote
  #5  
Old 07-09-2018, 09:13 PM
Marsheng Marsheng is offline
Senior Member
 
Join Date: Nov 2008
Posts: 299
Default

On connection lost is handled automatically with OnConnectionLost as part of MYConnection. Seems to work perfectly fine.

After a nights sleep I realized that unique may not work. I'm running several clubs and each have their own Race No. so I need to count with regard to the clubs ID.

I'll use occurrence count by club ID as my solution.

Store the race number going into the form, on form close check if the stored variable is nul and occurrence of the new number is 0 then write the value.

If the stored value is not 0 and is the same as the database value and occurrence is 1 then it must be the same record.

If the stored value is not 0 and is new entered value is different to the database value and occurrence is 0 then then write the value. Else number already used.
Reply With Quote
  #6  
Old 07-10-2018, 11:26 AM
Norrit Norrit is offline
Moderator
 
Join Date: Aug 2001
Location: Landgraaf
Posts: 7,292
Default

You can have a UNIQUE on multiple columns, so this still should work
Code:
ADD CONSTRAINT UC_ClubIdYourcolumn UNIQUE (ClubId, yourcolumn)
But you still could use your solution to verify before letting the db tell you it's unique...
Reply With Quote
  #7  
Old 07-10-2018, 09:58 PM
Marsheng Marsheng is offline
Senior Member
 
Join Date: Nov 2008
Posts: 299
Default

This is my solution.

The main table is tblMemMast and tblMemRaceNo is sorted on club.

Maybe not the most efficient way but I have all the control.

Code:
procedure TfMembers.FormShow(Sender: TObject);
begin
      eRaceNo.Text:=dm.tblMemMastFieldByName('RACENO').AsString;
end;

Code:
procedure TfMembers.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
var
   iCnt:Integer;
begin
   CanClose:=True;
   if Trim(eRaceNo.Text) = '' then begin  // In case any spaces entered. 
      dm.tblMemMast.edit;
      dm.tblMemMastRACENO.AsString:= ''
      end
     else begin
      if (Trim(eRaceNo.Text) <> Trim(dm.tblMemMastRACENO.AsString))  and (eRaceNo.Text <> '') then begin
         with dm.tblMemRaceNo do begin
            iCnt:=0;
            First;
            while not eof do begin
               if Trim(eRaceNo.Text) = Trim(FieldByName('RACENO').AsString) then
                  iCnt:=iCnt+1;
               next;
            end;
            if iCnt = 0 then begin
               dm.tblMemMast.Edit;
               dm.tblMemMastRACENO.AsString:= eRaceNo.Text;
              end
             else begin
               CanClose:= False;
               ShowMessage('Number already exists');
               eRaceNo.Text:='';
               eRaceNo.SetFocus;
               eRaceNo.Color:=$000080FF;
             end;
         end;
      end;
   end;
end;

Last edited by Marsheng; 07-10-2018 at 10:03 PM.
Reply With Quote
  #8  
Old 07-12-2018, 12:50 PM
rojam rojam is offline
Senior Member
 
Join Date: Jun 2015
Posts: 183
Default

Quote:
Originally Posted by Marsheng View Post
This is my solution.

The main table is tblMemMast and tblMemRaceNo is sorted on club.

Maybe not the most efficient way but I have all the control.

Code:
procedure TfMembers.FormShow(Sender: TObject);
begin
      eRaceNo.Text:=dm.tblMemMastFieldByName('RACENO').AsString;
end;
Code:
procedure TfMembers.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
var
   iCnt:Integer;
begin
   CanClose:=True;
   if Trim(eRaceNo.Text) = '' then begin  // In case any spaces entered. 
      dm.tblMemMast.edit;
      dm.tblMemMastRACENO.AsString:= ''
      end
     else begin
      if (Trim(eRaceNo.Text) <> Trim(dm.tblMemMastRACENO.AsString))  and (eRaceNo.Text <> '') then begin
         with dm.tblMemRaceNo do begin
            iCnt:=0;
            First;
            while not eof do begin
               if Trim(eRaceNo.Text) = Trim(FieldByName('RACENO').AsString) then
                  iCnt:=iCnt+1;
               next;
            end;
            if iCnt = 0 then begin
               dm.tblMemMast.Edit;
               dm.tblMemMastRACENO.AsString:= eRaceNo.Text;
              end
             else begin
               CanClose:= False;
               ShowMessage('Number already exists');
               eRaceNo.Text:='';
               eRaceNo.SetFocus;
               eRaceNo.Color:=$000080FF;
             end;
         end;
      end;
   end;
end;
The code you have above will fail miserably if you have a multi-user database (such as MySQL, Oracle, MSSQL, Postgres). Even if it's not currently designed as a multi-user interface, you would have to re-write all this code if the user's wanted to do this in the future.

Two users execute the code above at the same time, and they both will come up with the same "unique" number. scenario: user 1 on computer A and user 2 on computer B are on the same form so their view of the database is exactly the same, both count how many records meet the criteria, get the same value and attempt to use that same generated unique number, which is not so unique. Yes the database constraint will save you, but the point is to avoid the error all together, considering you don't handle that possibility at all in your code.

Also, why are you literally counting each record one at a time, simply run a Query with a Select Count(*) as Count from yourtable where raceno = :racenoParameter and set racenoParameter to the value you are looking for, Trim(eRaceNo.Text), before you open the query. Now you have the count in your query's Count.asInteger.

Also, how did you define your Unique Index on this field? Did it only include the one column in your database or did you also include raceno in your index? Since you say that the number has to be unique for a particular raceno, then your Unique Index has to be defined on both fields not just the one.

If you need a unique number within a raceno, I would let the database handle the generation of that number. How that works depends on the database you are using.

Last edited by rojam; 07-13-2018 at 01:04 AM.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On

Forum Jump


All times are GMT. The time now is 08:16 AM.


Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2018, vBulletin Solutions, Inc.