Really simple, and I think rather elegant random record selector!

I wanted to select some random products from my database for display, a quick look at the web made it look like rocket science! So not to be stopped I came up with this really simple script for doing just that.

Thx for all your help folks and I hope this comes in handy for you.

Turn batch mode on

Randomize
 
'Create your recordset
 
Set conn=Server.CreateObject("ADODB.Connection")
conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" &server.MapPath("/access_db/WebData.mdb")&";" 
Set rs=Server.CreateObject("ADODB.RecordSet")
 
'Setup your cursor and locktype

'(If you need explanation of this then I suggest you don't experiment with this code!)
 
rs.CursorType = 3
rs.CursorLocation = 3
rs.LockType = 4

'Connect
 
rs.ActiveConnection = conn
rs.open ("Select * from rs")
 
"N" is the number of random records you want to select.
 
For x= 1 to [N]
rs.AbsolutePosition = int(rnd()*rs.RecordCount)
 
'Some code to do something with Record
 
'Delete the used record from the recordset(It isn't deleted from database just marked and inaccessible!)
 
rs.Delete
 
'Get the next random record from the remaining records in the recordset!

Next
 
'Cleanup
 
rs.Close
conn.close
Set conn=nothing
Set rs=nothing

Code (markup):

 

If you're using MS SQL you can just use NEWID() to get a random row:

Select * from rs ORDER BY NEWID()

 

MySQL its even more elegant than that


SELECT * from Table ORDER by rand() limit 1