Random Record from Access DB (Using Query)

[nevets2001uk]

Originally posted in the ASP.NET forum I have the following problem...

If am working from an Access Database and would like to pull a recrod at random from my database to display in a random photograph section of my new website. I had this working in SQL but had to move back to access for hosting at this time.

Here is what I've got based on a few google searches. The problem I have is that although requerying the table does cycle through different photographs it doesn't do it randomly and I always get the same sequence of photos.

SELECT TOP 1 PHOTOGRAPH_ID, RND([PHOTOGRAPH_ID]) AS RANDOMID, GALLERY_ID, TITLE, REFERENCE FROM PHOTOGRAPHS ORDER BY Rnd([PHOTOGRAPH_ID]) DESC

Any thoughts how to do this? The query is inside a table adapter which might make a difference?


Steve G (MCSE / MCSA:Messaging)

 

[Remou]

Do you have Randomize somewhere in your code?

This may be of interest:

http://aspnet.4guysfromrolla.com/articles/091907-1.aspx

 

[SeeThru]

It is the bit:

RND([PHOTOGRAPH_ID])

RND is not truly random - supplying the same seed will always return the same number, and a photograph ID is fixed, you get the same top record.

Try using something else to randomise it, that changes all the time. how about

RND(now()+[PHOTOGRAPH_ID])

As now() (the current date/time) is always changing, the random order alwas changes. Note Now() on it's own will not work as then all records would get the same random number.

 

[Remou]

In ASP you need Randomize:

http://www.dotnetindex.com/articles...sing_RND_Function_And_Randomize_Statement.asp