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
Solved

Show 5 multiple random records from access database in asp

Posted on 2007-11-18
7
2,831 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

Suggested Solutions

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…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

766 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