Add random number but if found

Results 1 to 7 of 7

Thread: Add random number but if found

  1. #1
    Random number Guest

    Default Add random number but if found

    I&#039m creating user id with a random number on the end. I have a do while loop that searches the database. I need to be able to change the random number if it is found. How do I reinstaciate or whatever while in the do while loop?

  2. #2
    javawiz Guest

    Default RE: Add random number but if found

    dim num,available<BR>Randomize<BR>num=rnd<BR>available =false<BR><BR>while not available<BR>if check(num) then<BR>available=true<BR>else<BR>Randomize<BR>num =rnd<BR>end if<BR>wend<BR><BR>function check<BR>objRS.Open "SELECT ID FROM MYTABLE WHERE ID="&num,objConn<BR>if not objRS.EOF then<BR>check=false<BR>exit function<BR>else check=true<BR>end if<BR>wend<BR>end function<BR><BR>Response.write "Here is your ID"&num<BR><BR>It just keeps generating random numbers until it finds one that the database doesn&#039t have already.

  3. #3
    javawiz Guest

    Default RE: Add random number but if found

    Oops, instead of function check it&#039s supposed to be function check(num). Just a minor mistake. Good luck!

  4. #4
    Steve Cimino Guest

    Default A few problems

    First, we don&#039t know what type of number he is looking for. If you&#039re using rnd, you do realize that it returns a single (0.3923923). NEVER NEVER NEVER check to see if a single equals another single... it&#039s too precise. Instead, you should have a leeway.<BR><BR>A better solution instead of creating a recordset inside that loop(!), is depending on the amount of records he has. What I would do, is get all the numbers in the database, dump them into an array, and do something like this:<BR><BR>&#039I&#039ll assume at this point you&#039ve dumped all the unique <BR>&#039numbers from the database into array databaseArray<BR>Randomize<BR><BR>bUniqueFound = False<BR><BR>Do Until bUniqueFound<BR>iNewNumber = Int((Rnd * 1000) + 1) &#039This gives a number from 1 - 1000<BR>bUniqueFound = True<BR>For i = 0 to UBound(databaseArray)<BR> If databaseArray(i) = iNewNumber Then<BR>bUniqueFound = False<BR>Exit For<BR>End If<BR>Next<BR><BR>You don&#039t need to call Randomize again.<BR><BR>Using this method, you&#039re not wasting calls to the database. I&#039ll bet you that if you run this code against yours you&#039ll see a huge performance difference.<BR><BR>

  5. #5
    Randome Guest

    Default So...

    So , creating the loop to put all values in arrary and the do while loop will be more effective than the other?<BR><BR>Also, I put first name and randomize number together. So, I&#039d check for that and just add an extra number?

  6. #6
    balletchick Guest

    Default RE: Add random number but if found

    But all of this seems so silly and inefficient! If you don&#039t like the suggestion I gave you about using an IDENTITY or AUTONUMBER field (and I do not understand why you wouldn&#039t like it), then there is STILL no reason for you to have to loop through your database to see if the ID is already used!<BR><BR>Instead, be sure you have an index on that field. Then use code similar to this:<BR><BR>do<BR> lngPossibleID = int(rnd * 999999999)<BR> set rs = connection.execute("select count(*) from usertable where userID=" & lngPossibleID)<BR> lngCount = rs(0)<BR> rs.close<BR>loop until lngCount = 0<BR><BR>now... lngPossibleID contains one we can use. Now... there may be some concurrency problems here because you may have hundreds of simultaneous users... but then... your approach had the same potential problem.<BR><BR>Also, if you wanted to use this twisted logic :) instead of an identity/autonumber field, you would want to migrate the whole ID number routine (above) into a stored procedure.<BR><BR><BR><BR>At MOST you should never have more than one or two reads from your database before you find an open slot.<BR><BR>

  7. #7
    Steve Cimino Guest

    Default RE: So...

    There really isn&#039t a loop to put the values from the rs into an array. Check out the getrows ADO command. Very quick.<BR><BR>You can also use balletchick&#039s suggestion, but I have found numerous amounts of times where identity/autonumber is not sufficient for primary keys. It really depends on what you&#039re after.<BR><BR><BR><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