random

[benbramz]

hi

how would you go about selecting a random record from a database?

basicly i want a page, and when you hit it a random record from the database is displayed. so if i was to refresh the page, you would see another (random) record

cheers

[Webforumz Staff]

Access or SQL?

[benbramz]

access

[Webforumz Staff]

http://www.webforumz.com/viewtopic.p...=access+random

[benbramz]

ha! lol never saw that post 5 from the top

however, im stuck. basicly im editing a script i downloaded. its the page that shows all the entries of the database. However, im not sure where or what to edit to make the RND function work.

Code: Select all

  <%
  dim Connect, SHOWALL, Query 
Set Connect = Server.CreateObject("ADODB.Connection")
Connect.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.MapPath("db.mdb"))
Set SHOWALL = Server.CreateObject("ADODB.Recordset")
Query = "SELECT * FROM records"
SHOWALL.Open Query, Connect, adOpenDynamic, adLockOptimistic
SHOWALL.movefirst
do while not SHOWALL.eof
%>

how do i edit this? and whats the basic connection to the db, cos i know some of that is extra, just dont know what.
(is it this?)

Code: Select all

  <%
  dim Connect, SHOWALL, Query 
Set Connect = Server.CreateObject("ADODB.Connection")
Connect.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.MapPath("db.mdb"))
Query = "SELECT * FROM whatever"
%>

with regards

[Webforumz Staff]

Ack, it's not the way I usually write this stuff but this is the basic connection part

Code: Select all

Set Connect = Server.CreateObject("ADODB.Connection") 
Connect.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.MapPath("db.mdb"))

And to get one random one you want to change

Code: Select all

Query = "SELECT * FROM records"

to

Code: Select all

Randomize()
intRandomNumber = Int (1000*Rnd)+1


Query = "SELECT TOP 1 *,Rnd(" & -1 * (intRandomNumber) & "*TableID) AS rid  FROM records ORDER BY rid"

replacing TableID with the name of the id field for the table.

I haven't tested it but unless I made a typo that should work.