Random Record Selection from Access 2000

[Jamie Fryatt]

Hi Guys, i have an access database with 5 records in and i need to show them
in a random order every time the page is refreshed or reloaded.

i've searched round and tryed no end of code but nothing seems to work.

i just need something really simple that selects a random feild from my DB.

Sql = "SELECT * FROM tblmain ORDER BY RND()" - ive tryed this and it just
shows the results in teh same order.

this is desperate help is need and will be gratefully recieved.

thanks

Jamie

[Bhaskardeep Khaund]

Hi,

You could do one thing. Get all the ids of the columns into an ASP array, and using the Randomize and RND() function, pick up any one of the array element and then select the database using that id. Like....

<%
'Randomizing the Id of the Term column
'--------------------------------------------------------
Function RandomNumber(intHighestNumber)
Randomize
lowerbound = 0
upperbound = intHighestNumber
randomIndex = Int( ( upperbound - lowerbound + 1 ) * Rnd + lowerbound )
RandomNumber = randomIndex
End Function

Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.Open "select iTermId from Term_MST", objConn
If not oRs.EOF Then
myArr = oRs.GetRows()
s = uBound(myArr,2)
rndId = myArr(0,RandomNumber(s))
Else
rndId = 0
End If
oRs.Close
Set oRs = Nothing
'-----------------------------------------------------------------

Set objRs = Server.CreateObject("ADODB.Recordset")
objrs.Open "select vTermName, vImageURL from Term_MST where iTermId= "&rndId&"", objConn
If not objrs.EOF Then
term = objRs("vTermName")
url = objRs("vImageURL")
End If
objRs.Close
Set objRs = Nothing
%>

REgards,
Bhaskardeep Khaund

[Aaron Bertrand - MVP]

http://www.aspfaq.com/2132

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"Jamie Fryatt" <ja**********@btconnect.com> wrote in message
news:bq**********@titan.btinternet.com...

Hi Guys, i have an access database with 5 records in and i need to show them in a random order every time the page is refreshed or reloaded.

i've searched round and tryed no end of code but nothing seems to work.

i just need something really simple that selects a random feild from my DB.
Sql = "SELECT * FROM tblmain ORDER BY RND()" - ive tryed this and it just
shows the results in teh same order.

this is desperate help is need and will be gratefully recieved.

thanks

Jamie