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.

    thanks

    Jamie
     
    Jamie Fryatt, Dec 4, 2003
    #1
    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)
    Randomize
    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))
    Else
    rndId = 0
    End If
    oRs.Close
    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
    objRs.Close
    Set objRs = Nothing
    %>

    REgards,
    Bhaskardeep Khaund
     
    Bhaskardeep Khaund, Dec 4, 2003
    #2
    1. Advertisements

  3. Aaron Bertrand - MVP, Dec 4, 2003
    #3
    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.