asp/sql random record display

I am trying to randomly select 3 records from an Access database and display them. I know what the problem is so far, but I'm unsure of how to fix it. I'm calling rs.RecordCount before I declare what rs is equal to.... here is the code so far:


<%
sql = ""
sql = sql & "SELECT LICENSE_NO,COMPANY,FNAME,LNAME,CITY,PHONE "
sql = sql & "FROM members "

MaxNumber = (rs.RecordCount) 'Highest Number from Record Count
Randomize 'To make sure its random
intrandomNum1 = Int(MaxNumber * Rnd) + 1
Randomize 'To make sure its random
intrandomNum2 = Int(MaxNumber * Rnd) + 1
Randomize 'To make sure its random
intrandomNum3 = Int(MaxNumber * Rnd) + 1

sql = sql & "WHERE LICENSE_NO = '" & intrandomNum1 & "' "
sql = sql & "OR LICENSE_NO = '" & intrandomNum2 & "' "
sql = sql & "OR LICENSE_NO = '" & intrandomNum3 & "' "
sql = sql & "ORDER BY LNAME asc "
Response.write sql
Set rs = oConn.Execute(sql)
%>




I need to change the Where and Or statements to
where record = (not LICENSE_NO = ) but I am also unsure of the syntax for this too...

Anyone know the proper syntax?

i think you need to use 2 querys:

sql="select max(LICENCE_NO) as uboundval from members"

The use the rs.Fields('uboundval').Value inside your randomisations, but add the generated integer to a collection
Like

dim coll
coll = "999999999999" 'just to get a start for the collection
MaxNumber = (rs.Fields("uboundval").Value 'Highest Number from the table
Randomize 'To make sure its random
do while i <= 3
coll = coll & ", " & (Int(MaxNumber * Rnd) + 1)
i = i + 1
loop


Then your second select:
sql="SELECT LICENSE_NO,COMPANY,FNAME,LNAME,CITY,PHONE FROM members WHERE LICENSE_NO In (" & coll & ")"

The main advantage is that the recordsets that are returned from the db, will be much smaller --> for the first select, only one variale-value pair and for the second one, just 3 records with 6 values.

If you have a big table, then selecting the whole table just to get a count will generate unnescecary db-trafic.

If you do want to only use 1 select, then you best dump the table into an array (using GetRows)+ close the recordset and connection AND then use "Int(maxnumber*Rnd)" as the arraykey for the records you need.

Dim IntRandom(3), I, J
Randomize


For I = 0 to 3

IntRandom(I) = Int(8 * Rnd() + 1)

For J = 0 to 3

If IntRandom(I) = IntRandom(J) and IntRandom(I) <> 0 and I <> J Then

I = I -1

Exit For

End If

Next

Next


For I = 0 to 3

Response.Write IntRandom(I) & "<br>"

Next


I don't know if this is helpful, but this randomize script will allow you to select 3 different random numbers.
And if you have 3 different random numbers you can select 3 different random records...