asp/sql random record display

[webmarkart]

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?

[raf]

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.

[Morgoth]

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...