Solved

Show 5 multiple random records from access database in asp

Posted on 2007-11-18
7
2,827 Views
Last Modified: 2011-04-14
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
Comment
Question by:superlative
  • 2
  • 2
7 Comments
 

Author Comment

by:superlative
ID: 20308990
just modification to above code....
0
 
LVL 2

Expert Comment

by:yossi_intlock
ID: 20309066
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
 

Author Comment

by:superlative
ID: 20310496
hi yossi,

i have no idea how to do what you said,

could you give me a example with my above code?
0
 
LVL 2

Accepted Solution

by:
yossi_intlock earned 500 total points
ID: 20311349
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
 

Expert Comment

by:machII
ID: 24717607
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
API Location, separating out information 10 48
syntax error on ASP ? 6 46
Calculating value of records from a column - classic asp 7 79
SP to delete duplicates 15 70
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question