Solved

Show 5 multiple random records from access database in asp

Posted on 2007-11-18
7
2,854 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

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 informatio…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

724 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