RETURN 1 RANDOM RECORD TO WEBSITE! 1

[nwt002]

Hey all,

I am using Frontpage and have used its 'Database Interface Wizard' to create a page that displays

database results and also a page for me to edit my database from the web. The database that it

created for me is an Access database and will just contain a list of quotes. The two columns in

the DB are 'Key' which is autonumbered, and 'quote' which is just text. Whenever I find quotes

that I like I will be adding them to the database, so the number of records will always be

changing.


OJECTIVE:

I want to have the webpage display just 1 random quote from the database everytime the page is

refreshed. It is ok that it displays the same record more than once, but I would like every record

to have a chance to be displayed.


-----------------------------------
The only thing that I have found so far that kinda works is this:

SELECT TOP 1 Key, quote
FROM Results
ORDER BY Rnd(Key) * Second(Time()) * 1000 mod 1000

I added this to the custom query section of the 'Database Results Wizard' and this appears to

return random records, but the problem is every so often it will return all my records.

------------------------------------

I have tried to give you as much info as I could, so if there is anything else that would be

helpful to know, please let me know. I don't have much experience with databases, querying, or

scripting, so as much info as possible would be helpful. The more detailed the better!
Forgive me if this is not the right forum to post this question. I am not sure if this is more of a

website scripting question or a database question or both, so i will try posting this in some other

forums as well. Thanks for your time!

 

[BKQc]

Have you ran the request many times in a row? How many records do you have? For those who didn't see the first part of the thread, here is the request used:

SELECT TOP 1 key, quote 
FROM Results, (Select Min(key) as MinValue FROM Results) TMin 
WHERE Key>=(((Select Max(key) FROM Results) - TMin.MinValue) * Rnd + TMin.MinValue)

 

[nwt002]

Yes, I keep pressing the refresh button on my browser, but it keeps returning the quote which has the 'Key' = 14. I have a total of 18 records, I deleted the first record, so i don't have a 'Key' = 1. The min. key = 2 and the max key = 19.

 

[BKQc]

Have you tried to run the query directly within Access? Also, maybe you should use ctrl+F5 instead of simply F5. The main difference is that you're sure to clear the cache. Tell me what's the behavior within Access and then we'll know if the problem is Access/Query or ASP/HTML/Browser

 

[nwt002]

I just tried running the query in access a couple of times and it returned different records. So it appears to work in Access. I tried using ctrl+F5 on the website but it keeps returning the same record with 'Key'=14. I tried clearing the cache myself, but i still get the same result.

 

[PHV]

You may try something like this:
SELECT TOP 1 key, quote
FROM Results, (Select Min(key) as MinValue FROM Results) TMin
WHERE Key>=(((Select Max(key) FROM Results) - TMin.MinValue) * Rnd[highlight]([any numericfield from Results])[/highlight] + TMin.MinValue)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nwt002]

Thanks for the post PHV.

I tried adding 'Key', which is the only numberic field in the database, after Rnd. It displays a different record now(record with 'Key'=8), but it is the same record each time I refresh the webpage. I even tried putting different numbers after Rnd and it displays different records, but it keeps displaying the same record even though I clear the cache and refresh the webpage.

 

[PHV]

And this ?
* Rnd(Results.Key*Time()) +

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nwt002]

Hey PHV,

I tried adding this and also what you posted in the other forum, but it still displays the same record each time i refresh.

 

[BKQc]

Maybe now that we have a running query we could transfer this to an ASP/HTML forum? Have you tried running the query many times within the same ASP page? If you try this, don't forget to close your recordset and connection each time. Also, there maybe is a "pooling" or "caching" issue within ASP so you could try something like

SELECT TOP 1 key, quote 
FROM Results, (Select Min(key) as MinValue FROM Results) TMin 
WHERE Key>=(((Select Max(key) FROM Results) - TMin.MinValue) * Rnd + TMin.MinValue) AND 
(key=" & Rnd()*100000 & " OR TRUE)

Don't forget to use Randomize() in your ASP code before calling the VBScript Rnd() function. The effect of this will be to make the query different each time so if there's a caching mechanism, it won't be able to work.