superlative
asked on
Show 5 multiple random records from access database in asp
Hi,
I have a ms access database.
I am coding in asp.net
I currently have the attached code which displays one random record each time the page is refreshed.
How can i alter the below code to change it to show 5 random records?
I have a ms access database.
I am coding in asp.net
I currently have the attached code which displays one random record each time the page is refreshed.
How can i alter the below code to change it to show 5 random records?
<%
Option Explicit
Response.Buffer = True
%>
<html>
<head>
<style>
p { font-family:verdana; font-size:11px; }
</style>
</head>
<body>
<br><p align="center">
</p>
<p align="center">
<%
' ADO Constant. Dont change this
Const adCmdText = &H0001
' Connection string and SQL statement
Dim query, connStr
query = "select ItemName, ItemURL from Links where Activated = True"
connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("database.mdb")
' Opening database
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open query, connStr, 3, , adCmdText
' Generating random number from total number of records
Dim intRnd
Randomize Timer
intRnd = (Int(RND * rs.RecordCount))
' Now moving the cursor to random record number
rs.Move intRnd
' Showing the random statement
Response.Write "<ul><li>"
Response.Write "<a href=" & rs("ItemURL") & ">" & rs("ItemName") & "</a>"
Response.Write "</ul></li>"
' Closing the database
rs.Close
Set rs = Nothing
%>
</p>
</body>
</html>
you can do the random in the select statement by adding a guid column every time you query for the results. just change your query to:
select ItemName, ItemURL, RND = NEWID() from Links where Activated = True order by RND
that will do the random job for any amount of results
you can be sure that every select will organize the results in a different random way.
after that youll just have to select the top 5 or 6 or 10 results from the recordset.
select ItemName, ItemURL, RND = NEWID() from Links where Activated = True order by RND
that will do the random job for any amount of results
you can be sure that every select will organize the results in a different random way.
after that youll just have to select the top 5 or 6 or 10 results from the recordset.
ASKER
hi yossi,
i have no idea how to do what you said,
could you give me a example with my above code?
i have no idea how to do what you said,
could you give me a example with my above code?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Alternate code for you all.
The first SQL seeds the Access random, then the second one retrieves it.
The first SQL seeds the Access random, then the second one retrieves it.
<%
'The ASP Randomize
randomize
'This code needs to be executed...
query = "SELECT Rnd(" & Int((10-1+1)*Rnd+1) * -1 & ") FROM {TABLE}"
'Then this code to get the result set.
query = "SELECT top 1 * FROM {TABLE} ORDER BY Rnd([ID])"
%>
ASKER