Random Record Selection from Access 2000

Discussion in 'ASP General' started by Jamie Fryatt, Dec 4, 2003.

  1. Jamie Fryatt

    Jamie Fryatt Guest

    Hi Guys, i have an access database with 5 records in and i need to show them
    in a random order every time the page is refreshed or reloaded.

    i've searched round and tryed no end of code but nothing seems to work.

    i just need something really simple that selects a random feild from my DB.

    Sql = "SELECT * FROM tblmain ORDER BY RND()" - ive tryed this and it just
    shows the results in teh same order.

    this is desperate help is need and will be gratefully recieved.


    Jamie Fryatt, Dec 4, 2003
    1. Advertisements

  2. Hi,

    You could do one thing. Get all the ids of the columns into an ASP array, and using the Randomize and RND() function, pick up any one of the array element and then select the database using that id. Like....

    'Randomizing the Id of the Term column
    Function RandomNumber(intHighestNumber)
    lowerbound = 0
    upperbound = intHighestNumber
    randomIndex = Int( ( upperbound - lowerbound + 1 ) * Rnd + lowerbound )
    RandomNumber = randomIndex
    End Function

    Set oRs = Server.CreateObject("ADODB.Recordset")
    oRs.Open "select iTermId from Term_MST", objConn
    If not oRs.EOF Then
    myArr = oRs.GetRows()
    s = uBound(myArr,2)
    rndId = myArr(0,RandomNumber(s))
    rndId = 0
    End If
    Set oRs = Nothing

    Set objRs = Server.CreateObject("ADODB.Recordset")
    objrs.Open "select vTermName, vImageURL from Term_MST where iTermId= "&rndId&"", objConn
    If not objrs.EOF Then
    term = objRs("vTermName")
    url = objRs("vImageURL")
    End If
    Set objRs = Nothing

    Bhaskardeep Khaund
    Bhaskardeep Khaund, Dec 4, 2003
    1. Advertisements

  3. Aaron Bertrand - MVP, Dec 4, 2003
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.