h2g2 The Hitchhiker's Guide to the Galaxy: Earth Edition

I need a bit of help with an ASP coding problem.

I have a database set up with a table called "object" in it. To display a recordset from the "object" table I have:

<%
Dim con
Dim rs

Const adOpenKeyset = 1
Const adLockOptimistic = 3

Set con = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

con.Open "database"

sql = " SELECT * FROM object "

rs.open sql, con, adOpenKeyset, adLockOptimistic

response.write rs("Text")

rs.Close
con.Close
%>

So far this works... but I'd like a way to select a random recordset * FROM object in the SQL query. Can anyone suggest a way to do this?

B.

Query the database to find out how many records are in it, then generate a random number in that range, and query the database again to pull the record # that you generated.

There might be better ways, but I don't know ASP, and you don't say what database you are querying (oracle? mysql? access? mssql?). The above method should work though as long as ASP can generate a random number.

I had thought about doing that but the code needed would be very long and complex. I was wondering if there was a quicker and more efficient way of doing this just in the SQL query.

The database is currently MS-Access but will move over to MS-SQL soon.

B.

I prefare MySQL (NJB it's free either )

Surly there should just be a random number function, like rnd($seed) or somthing, and query the database from there.

somthing is missing I think.

-- DoctorMO --

I've tried:

sql = " SELECT rnd(*) FROM object "

But no luck there.

B.

OK... I'm going to go with d'Elaphants idea. I now have:


sql = " SELECT Count(*) FROM object "

rs.open sql, con, adOpenKeyset, adLockOptimistic

RowCount = rs(0)


How do I create a random number between 1 and the RowCount value to use later on in another query?

B.

Forgot to mention... the random generated number must be an integer

B.

Remember that I don't know ASP, but I found this doing a quick google search

http://www.asp101.com/samples/viewasp.asp?file=random%5Fnumber%2Easp


I'm also thinking I won't bother to learn ASP. JSP is much clearer.

Ah, I see...

-- DoctorMO --

No, PHP and MySQL!

OK... here's what I've got now:

<%
Dim rs

Const adOpenKeyset = 1
Const adLockOptimistic = 3

Set rs = Server.CreateObject("ADODB.Recordset")

sql = " SELECT * FROM object "

rs.open sql, con, adOpenKeyset, adLockOptimistic

rs.move = Int(rnd * CInt(rs.RecordCount))

response.write "Quote: " & rs("Text") & ""

rs.Close
Set rs=nothing
%>

The only thing is that on my computer (Windows 2000) in IIS5 the line Int(rnd * CInt(rs.RecordCount)) still isn't generating random numbers.

Is this because IIS on a local server work diferently to a full Windows 2000 Server?

B.

Got it working!!!!!


<%
' Initialise the parameters
Dim con
Dim rs

' Switch on the random number generator
Randomize()

' Create objects con = Connection rs = Recordset
Set con = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

' Write and run the SQL query
sql = " SELECT * FROM object "
rs.open sql, con, adOpenKeyset, adLockOptimistic

' move to a random record where the record number is between 0 and the total number of records
rs.move = Int(rnd * CInt(rs.RecordCount))

response.write "Quote: " & rs("Text") & ""

' Close the connections
rs.Close
con.Close
%>


Thanks for the help guys.

B.