filtering random number in database to nsure no re

# Thread: filtering random number in database to nsure no re

1. Junior Member
Join Date
Dec 1969
Posts
14

## filtering random number in database to nsure no re

I am trying to create an unique random number for each transaction in a database. However, I notice as the number of request increase, the unique number appears again. The numbers follow in the same order, ie one after the other. Max occurence is about 5 times in every 200 numbers. Could it be that they are executed at the same time but even so it should have been filtered out (see code below)<BR><BR>I use a simple random number generator and filter to check if the generated number exist and if not enter in the database. Anyone can give advice here. Database has more than 300000 numbers we are doing close to 250 transactions at any one time.<BR><BR>The code is as below;<BR><BR> Do<BR> n = Now<BR> i = Day(n) + Month(n) + Hour(n) + Minute(n) + Second(n) + CLng(Timer * 200)<BR> objRandom.seed = i<BR> keygen = objRandom.RandomNumber<BR> keygen = Left(keygen, 12)<BR> &#039;make it even number<BR> If (Right(keygen, 1) Mod 2) &#060;&#062; 0 Then keygen = keygen - 1<BR> rs.Filter = "sedid=" & keygen<BR> Loop Until (rs.BOF And rs.EOF)<BR> rs.Close<BR> <BR>Any advice ?

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## WHY???

Why would you *ever* use a random number?<BR><BR>*OF COURSE* random numbers can repeat! By definition, random numbers are *RANDOM*. And that includes the random chance that a number will repeat.<BR><BR>You should *NEVER* expect to use random numbers as unique identifiers.<BR><BR>(Well, MS uses 128-bit random numbers as GUIDs, but even they "cheat" a little to try to avoid repeats. And even with 128 bits it is possible to get a repeat...it&#039;s just that the chances are one in roughly 1,000,000,000,000,000,000,000,000,000,000,000,000, 000)<BR><BR>I dunno what database you are using, but don&#039;t you think you should consider the built-in mechanism for assigning unique ids to records? Such as the @@IDENTITY property in SQL Server?<BR><BR>The alternative is, I think, to use the time of day (accurate to the minute is probably good enough) concatenated with the user&#039;s Session.SessionID value. (Session.SessionID&#039;s are guaranteed unique until/unless you have to reset the server. Since you can&#039;t do that in less than a minute, I would presume, using a time of day accurate to the minute thus guarantees uniqueness.)<BR><BR>Just out of curiosity, what is your objRandom object? A home grown ActiveX component? If so, why do you seed it from VBScript and manipulate the result from VBS? Why not do all the work in the component? <BR><BR>And I can&#039;t believe you are using ADODB.RecordSet.Filter in a high volume operation (250 simultaneous transactions?). That is one of the most inefficient ways to *EVER* use a RecordSet. It has horrible performance.<BR><BR>There are several other mistakes in that code (such as the way you generate your seed...you are virtually *guaranteeing* you will get non-randomness with that code), but since I think the entire concept is done wrong, I won&#039;t bother going into them.<BR><BR>

3. Junior Member
Join Date
Dec 1969
Posts
14

## RE: WHY???

First of all I can&#039;t use this @@IDENTITY bec our output "random number" is to create a barcode which has certain unique properties. We also cannot use &#039;increment&#039; as the user may be able to guess the next unique number. Our design in fact has two activeX components, one is the random generator (home grown) which is called and the other is the creation of the barcode which is shown here. The main reason for this is that we can easily upgrade the random number generator later. Other than filter, I can&#039;t think of another way to check.

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Well...if you are going to home grow...

...a random number generator, then why not have *IT* ensure you never get two numbers the same?<BR><BR>The only other way to insure you don&#039;t is to look in the DB for a match and try again if you get one.<BR><BR>The problem with your seed, by the way, is pretty obvious:<BR><BR>You are doing <BR><BR>seed = Year(now) + Month(now) + Day(now) + ...<BR><BR>But consider: 2002-5-25 will give you the same sum for those first three numbers as (for example) 2001-6-25 and 2001-5-26 and 2002-6-24 and 2002-4-26 and... well, you get the idea.<BR><BR>By SUMMING those numbers you have severely limited the number of possible seeds you can get.<BR><BR>In truth, one of the worst things you can do to a random number generator is constantly re-seed it, anyway. You should seed it once (perhaps at Application startup, via Global.asa and "SUB Application_onStart"). And then have it be an application-wide component that hands out the next (guaranteed no duplicate) random number.<BR><BR>Having said all that...it might be simpler to use the session ID and time of day, as I suggested. You could then run that through a simple encoder (e.g., one that turns the numbers into letters and then scrambles the letters according to a pattern known only to you) and you&#039;d be guaranteed to have unique numbers.<BR><BR>But the other things I objected to were your way of cutting off 12 digits (using a string!) and your way of finding out if the number is even or odd (again, a string).<BR><BR>Enough. One way or the other, this badly needs a redesign, I think.<BR><BR>

5. Junior Member
Join Date
Dec 1969
Posts
14

## RE: Redesign Yes...

Bill, for sure I am going to redesign it, there is no question here. The curious thing is that it works ok in Access with no duplicates (I tested it up to 10,000 numbers) with 10 concurrent users but when it come to MSSQL, I have more than 100 duplicates with only 3000 numbers. It also gave this error and crash " error &#039;ASP 0115&#039; <BR>Unexpected error <BR><BR>A trappable error occurred in an external object. The script cannot continue running "<BR><BR>Irregardless, no matter how it is design, it still has to search the DB and that perhaps contribute to the error. Thanks for your comments.<BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•