Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Show 5 multiple random records from access database in asp

Posted on 2007-11-18
7
Medium Priority
?
2,875 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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

636 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