Random Select from Database

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.

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

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

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: 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