一段返回随机记录的代码

网络整理 - 09-12

很久没用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
%>