Solved

Show 5 multiple random records from access database in asp

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
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…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now