一段返回随机记录的代码

March 25,2004

很久没用ACCESS了，只是看到经常碰到有人问如何返回随机记录的问题，所以就贴了上来，随便看看。


<%
Dim objConn
Dim objRst
Dim strSQL
Dim strConnection
Dim str
Dim str1
Dim cnt
Dim cnt1
Dim rndMax
Dim RndNumber

strConnection="driver={Microsoft Access Driver (*.mdb)};dbq=" & server.mappath("/testdb.mdb")
strSQL = "SELECT id FROM tblQuestions"

set objConn = Server.CreateObject("ADODB.Connection")
Set objRst = Server.CreateObject("ADODB.Recordset")
objConn.Open strConnection
set objRst.ActiveConnection = objConn
objRst.LockType = adLockOptimistic
objRst.CursorType = adOpenKeySet
objRst.Open strSQL

objRst.MoveLast
cnt = objRst.RecordCount
cnt1 = cnt
rndMax = cnt

If CInt(Request.Form("maxNumber")) < cnt Then
cnt1 = CInt(Request.Form("maxNumber"))
End If

str = ","
str1 = ","

Do Until cnt1 = 0
      Randomize
      RndNumber = Int(Rnd * rndMax)

      If (InStr(1, str1, "," & RndNumber & "," ) = 0) Then
      str1 = str1 & RndNumber & ","
      cnt1 = cnt1 - 1
      objRst.MoveFirst
      objRst.Move RndNumber
         str = str & objRst("id") & ","
End If

Loop

objRst.Close
Set objRst = Nothing

sql = "SELECT * FROM tblQuestions WHERE (((InStr(1,'" & str & "',(',' & [id] & ',')))<>0)) "
Set objRst = Server.CreateObject("ADODB.Recordset")
set objRst.ActiveConnection = objConn
objRst.LockType = adLockOptimistic
objRst.CursorType = adOpenKeySet
objRst.Open sql
%>
...DISPLAY THE RECORDS RETURNED...
<%
objRst.Close
Set objRst = Nothing

objConn.Close
Set objConn = Nothing
%>