I have a catalogue containing about 20 items, i list them by:

<%
set RSnav = server.createobject ("ADODB.recordset")
SQLnav = "SELECT * FROM eCatalog;"
RSnav.open SQLnav, Conn, 1, 3

While Not RSnav.EOF
response.write " " & RSnav("ID") & ".<br>"
RSnav.MoveNext
Wend

%>

this code lists all the items in the database, what i want is just to select one item randomly and display it.
so only item like number 5 or item no 9, so as to change the content with every reload.

Thanks in advance.

You could give them all a unique ID numbering 1 through 20, with no missing numbers, and then have ASP generate a random number between 1 and 20, then use a select statement like this: "select * from eCatalog where CatID = " & intRandomNumber

Otherwise, you can put the recordset into an array, and then do the same thing more or less only this time you would be accessing the array: myArray(intRandomNumber)

For more information on copying a recordset to an array, click the link below.
http://www.w3schools.com/ado/met_rs_getrows.asp - http://www.w3schools.com/ado/met_rs_getrows.asp

if its a MsSQL you can use the following SPROC
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=173 - http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=173

Alternative database (like Access)
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=65 - http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=65

A really elegant way to do this if you've got SQL Server 7 and above is:-

SELECT TOP nn * FROM my_table ORDER BY NewId()

(where nn is the number of random items you want and my_table is the table you are retrieving the data from)

This adds a GUID to each row and will return random records without the need for complex stored procs or asp routines.

I've also found an Access 97/2000 version:-

SELECT TOP 1 id, strText FROM tblMyTable ORDER BY Rnd(id)

This needs an autonumber field, id in the table to work as a seed for the Rnd() function to make the records completely random.

cosmic cat wrote: A really elegant way to do this if you've got SQL Server 7 and above is:- SELECT TOP nn * FROM my_table ORDER BY NewId() (where nn is the number of random items you want and my_table is the table you are retrieving the data from) This adds a GUID to each row and will return random records without the need for complex stored procs or asp routines.

whoa.. excellent little tidbit there!!  nice

cosmic cat wrote: I've also found an Access 97/2000 version:- SELECT TOP 1 id, strText FROM tblMyTable ORDER BY Rnd(id) This needs an autonumber field, id in the table to work as a seed for the Rnd() function to make the records completely random.

I tried this but didn't work:

I used this..

<%
Dim Rand, RandS, RandHere
Rand = "SELECT TOP 1 id FROM TableName ORDER BY Rnd(id)"
set RandS = server.createobject("adodb.recordset")
RandS.open Rand, Conn, 3, 1

response.write RandS(0)
%>

the database tested has 7 items... always id # 7 displayed,

everytime the only displayed record is the last one, I refreshed 20 times.. but the last record is the only displayed.

i modified the code to:  ORDER BY Rnd(id) DESC.. always id 6 displayed...

I'm trying the new posts now

 no success yet.. they are too complicated...

i am using access...

I've not tested the Access one either, sorry should have made that clear; it's just a snippet I picked up which looked like it should work. The SQL one works though, I use it a lot for randomly picking out records..

I'm taking a look in Access (2000) now to see what it does. Hmm, seems to always reverse the order of the records, so the top one would always be the last record.

I'll have a play later see if I can make it work!

it worked, thanks to MorningZ post.

<%
Dim Rand, RandS, RandHere
Rand = "SELECT id, Make, Model, Image FROM eCatalog "
set RandS = server.createobject("adodb.recordset")
RandS.open Rand, Conn, 3, 1

Dim rndMax
rndMax = CInt(RandS.RecordCount)
RandS.MoveFirst
Randomize Timer
Dim rndNumber
rndNumber = Int(RND * rndMax)
RandS.Move rndNumber

RandHere = RandHere & "<img border=""0"" onError=""src='GfX/clear.gif'""  src=""eShop/" & RandS(1) & ".gif""><BR>"
RandHere = RandHere &  "<img border=""0"" src=""eShop/" & RandS(3) & """>"
response.write RandHere
%>

cosmic cat wrote: I've not tested the Access one either, sorry should have made that clear; it's just a snippet I picked up which looked like it should work. The SQL one works though, I use it a lot for randomly picking out records.. I'm taking a look in Access (2000) now to see what it does. Hmm, seems to always reverse the order of the records, so the top one would always be the last record. I'll have a play later see if I can make it work!

thanks a lot.. it is working fine with the code i posted.

Cool

Shame there's not a simpler way in Access like SQL Server though, and when I was digging around in Access earlier, I remembered how much I've forgotten about Access (haven't used it for a couple of years now since we moved to MS SQL Server).

I remember testing this with Access a while back and we couldn't get it to reseed on the same connection, or was it a pool of connections....   There was some type of work around but I can't find where I saved it.

The SS one comes in quite handy though.

MS Access 2000 - I use this to post a random quotation on one of my pages from a database of quotations, maybe you can get an idea from it:

Edited - Sorry I noticed answer was already posted above!