Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Posts
    2

    Unanswered: Select Random records from table

    Hello
    i have product table with 20 record and i need to select randomly 3 records in every request.
    in MS Access when i make query with this code :
    "SELECT top 3 Prod_ID FROM table ORDER BY RND(Prod_ID)" it's working, every time i call this query it turns back 3 new records
    and diferent from the last time.
    but when i call this from my web page from internet with the same SQL select, it turns back always the same records

    here is the web page where i call this, the 3 products down have to be chosed randomly from the product table and change every time the page is called :

    http://www.almadina-bazar.de/produc...ID=10&cat=3

    here is my code in the ASP page:
    <%
    DSNLess_String ="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("database.mdb")
    conn.Open DSNLess_String
    Set rsRnd = Server.CreateObject("ADODB.Recordset")
    SQLRnd = "SELECT top 3 Prod_ID FROM table ORDER BY RND(Prod_ID)"
    rsRnd.open SQLRnd, DSNLess_String, 3, 3
    rsRnd.movefirst
    for s = 1 to rsRnd.recordcount
    response.write(rsRnd("Prod_ID") & "<br>")
    rsRnd.movenext
    next
    rsRnd.close
    set rsRnd = nothing
    %>
    thank you for your help

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm not sure ADO supports that the way you may be thinking. I can't say with certainty.

    Have you tried storing your sql string as a query within the access database, then call SELECT * FROM yourQuery?

    I suggest this because ADO sometimes botches environment-specific constructs, give it a shot.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    AFAIK JET doesn't support the rather nifty random function that MySQL has. You may need to recreate that functionality programatically

    if you have only 20 records then its feasible to read the 20 product rows. generate your 3 random numbers and return the results.


    a way of doing it would be to store 20 product ID's in a collection,
    generate a random number in the range 0..NoItemsinCollection),
    extract that product from the collection,
    generate a random number in the range 0..NoItemsinCollection),
    extract that product from the collection,
    generate a random number in the range 0..NoItemsinCollection),
    extract that product from the collection.

    There may be a better algolrhytym out there, but this one should work...
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2006
    Posts
    2

    this doesn't solve the problem

    Hello
    i gave the number 20 just for example , i want that you know, i the table are more product and the IDs are not ordered , i mean there are products they were canceld which means thier IDs don't exist ( ID : 1,2,5,9,12 ....)
    and to save all the all the records in one recordset is not good for the performence.
    what i didn't under stand , is why the Query in MS Access it turns every time new records and when i call it from the Asp code it turns the same records. maybe there is solution in this way.
    thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •