[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2884
  • Last Modified:

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?
<%
 
	
 
	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>

Open in new window

0
superlative
Asked:
superlative
  • 2
  • 2
1 Solution
 
superlativeAuthor Commented:
just modification to above code....
0
 
yossi_intlockCommented:
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.
0
 
superlativeAuthor Commented:
hi yossi,

i have no idea how to do what you said,

could you give me a example with my above code?
0
 
yossi_intlockCommented:
i added your code with the required changes :)
<%
	'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 TOP 5 ItemName, ItemURL, RND = NEWID() from Links where Activated = True ORDER BY RND"
		connStr = "Provider=Microsoft.Jet.OLEDB
		
		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
				
		arrRows = rs.getrows()
 
		' Showing the random statement
		
		for i=0 to ubound(arrRows,2)
		
			Response.Write "<ul><li>"
			
			Response.Write "<a href=" & arrRows(1,i) & ">" & arrRows(0,i) & "</a>"
	 
			Response.Write "</ul></li>"
		
		next
 
 
		' Closing the database
		rs.Close
		Set rs = Nothing
%>
</p>
</body>
</html>
 

Open in new window

0
 
machIICommented:
Alternate code for you all.

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])"
%>

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now