Random Record - Perhaps Better

Results 1 to 4 of 4

Thread: Random Record - Perhaps Better

  1. #1
    the other steve Guest

    Default Random Record - Perhaps Better

    I haven&#039;t been satisfied with (read: ain&#039;t smart enough to understand) the random record scripts I found on this site and others so I wrote my own. I&#039;m sure this isn&#039;t new and it may even be an exact duplicate of stuff already here, but I&#039;m tired of searching google for scripts.<BR><BR>Basically, you this will work with any field, but truly random requires a field with unique values. A field with unique values is better since if you use a non-unique field you will only return the first instance of each unique value. The field does not need sequential values and can contain text or numbers or wheat berries or what have you.<BR><BR>This script uses the numerical subscripts of array addressing to select the unique value. That sounds complicated, but it really means that arrays are already sequentially ordered so if you get a one-column array from a table, you have intrinsically given a unique, sequential value to each row and can then randomize it.<BR><BR>If you find an error, please post it, but I think this works.<BR><BR>I just want everyone to know how F(*&!^# HOT it is here in my office!<BR><BR>Here it is:<BR><BR>&#060;%<BR>&#039; GENERAL DIMS<BR>DIM CONN, RS, QUERY<BR>DIM RANDOMARRAY, X, numrecords, numcolumns<BR><BR>&#039; Connection stuff<BR>SET CONN = Server.CreateObject("ADODB.Connection")<BR>set RS = Server.CreateObject("ADODB.Recordset")<BR>CONN.Ope n "dsn=yourdsn" &#039; Connect to database with specified DSN<BR><BR>&#039; Get the list of index values. In this case the index is propertyid, but it could be yourownkeyid from your table. You need any unique field. It could be numbers, text, what have you, and this will work. It doesn&#039;t need to be sequential, that&#039;s what the array does for you.<BR>query = "SELECT propertyid from property"<BR>RS.Open query, CONN<BR>RANDOMARRY=RS.getrows<BR><BR>&#039; This shows you what you got. I added 1 because arrays are zero-based and I can&#039;t think that way. You don&#039;t need these lines, really.<BR>numrecords = ubound(RANDOMARRY,2) + 1<BR>numcolumns = ubound(RANDOMARRY,1) + 1<BR>response.write "Numrecords: " & numrecords & "<BR>Numcolumns: " & numcolumns & "<BR>"<BR><BR>&#039; Here begins the randomization calculation. I&#039;m still a bit fuzzy on how the random thing works, but I understand from "VBScript in a Nutshell" that if you call rnd(-1) and then randomize, you&#039;ll actually get a random number (at least good enough for what I&#039;m doing, maybe not for high-end monte carlo analysis). You should call rnd(-1) and randomize before each random calculation. If you want more than one record, you need to add some code (I&#039;ll do this in the next few days and post it). Then you choose, in the form of variable x, a random row from the array of key values.<BR>rnd(-1)<BR>randomize<BR>x = int(rnd()*numrecords)<BR>response.write x & "<BR>" &#039; You don&#039;t need this line<BR>response.write "PropertyID from Array: " & RANDOMARRY(0,x) & "<BR>" &#039; You don&#039;t need this line<BR><BR>&#039; Close the old recordset and create a new one with a random key value. You can print any or all fields from the random record, which is the point after all.<BR>RS.close<BR>query="SELECT * FROM property WHERE propertyid=" & RANDOMARRY(0,x)<BR>RS.open query, CONN<BR>response.write "PropertyID: " & RS("propertyid")<BR><BR>&#039; Close everything<BR>RS.close<BR>set RS=nothing<BR>CONN.close<BR>set CONN=nothing

  2. #2
    the other steve Guest

    Default TYPO

    OK, there&#039;s a typo in the getrows line. randomarrAy.<BR><BR>Yea, remember to use option explicit too. so there.

  3. #3
    the other steve Guest

    Default more typos - sheesh

    ok, it&#039;s really hot and the sweat is dripping down my mangled, cankerous, carpel-tunneled digits, if you use option explicit you&#039;ll find all the misspelled randomarray&#039;s.<BR><BR>ok then.

  4. #4
    Join Date
    Dec 1969

    Default Ummm...how does this differ...

    ...from Method 2 at http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=65 ??<BR><BR>You *do* make it simpler, by only getting one random record, and that&#039;s certainly not a bad thing. And you explain it differently, which is *always* a good thing.<BR><BR>I might note that you *can* make both these methods work if you don&#039;t have a unique field so long as you have some *combination* of fields that uniquely identifies the record (e.g., last name, first name, zip code?). Just select all needed fields in the first query and pass them to the second.<BR><BR>Finally, if you are only selecting *one* random record, there really isn&#039;t anything wrong with the AbsolutePosition scheme so long as your DB supports AbsolutePosition:<BR><BR>&#060;%<BR>Randomize<BR>. ..<BR>Set RS = Server.CreateObject("ADODB.RecordSet")<BR>RS.MaxRe cords = 1 &#039; minimize data transfer!<BR>RS.CacheSize = 1<BR>RS.Open "tablename", yourConnection, adOpenStatic<BR>&#039; choose a random record from 1 to RecordCount<BR>RS.AbsolutePosition = 1 + Int( Rnd * RS.RecordCount )<BR>... now RS is ready to use with a random record ...<BR>%&#062;<BR><BR>SIDE NOTE: You should *NOT* use Rnd(-1). RND of a negative argument is actually used to *avoid* the Randomize process, as when you want to test code that uses RND but want repeatable results. Used as you did, with Randomize following it, it doesn&#039;t hurt, but unless you are trying to get non-random numbers, there&#039;s no reason to use it. See the VBS docs.<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