Generating Random Records from the Database

Introduction
In this article we will learn how to display random records from the database. From the articles so far, you will be able to display records from the database in a variety of ways and do a lot with them. We will add to our ASP-Database knowledge by enabling our ASP scripts to show random records.

Practical Application
This technique can be used in a variety of ASP-Database applications :

Ad Management Systems to display random ads from different advertisers.
Content Rotator to show random content on the site.

Can also be used in a lot other applications and scripts that I can't think of now. You learn this technique here and use this anyhwere you want.

Access Database
Start Microsoft Access and create a new database. Save it as random.mdb. Now in the 'design view', create a new table and save it as RandomRec. The table should look like following in the 'design view' :

RandomRec - Design View

Populate the table so that it looks something like this :

RandomRec Table

We are now ready to code the ASP page to show these statements randomly.

'rndrec.asp' ASP page
Create a new ASP page and copy the following code into, then save it as rndrec.asp :

<%
	Option Explicit
	Response.Buffer = True
%>
<html>
<head>
	<style>
	p { font-family:verdana; font-size:11px; }
	</style>
</head>
<body>
<br><p align="center">
<%
	' ADO Constant. Dont change this
	Const adCmdText = &H0001

		' Connection string and SQL statement
	Dim query, connStr
		query = "select statement from RandomRec"
		connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
				Server.MapPath("random.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 "<b>" & rs("statement") & "</b>"

		' Closing the database
		rs.Close
		Set rs = Nothing
%>
</p>
</body>
</html>

Explanation
You should be able to understand most of the code in rndrec.asp page if you have been following my ASP-Database articles. I will explain the part which involves generating a random number.

	rs.Open query, connStr, 3, , adCmdText

The first thing to remember when generating random records is to set Cursor Type to adOpenKeyset i.e. 3.

	' Generating random number from total number of records
	Dim intRnd
		Randomize Timer
		intRnd = (Int(RND * rs.RecordCount))

Next we declare a variable intRnd to hold the random record number. We use Randomize Timer statement to initialize the random-number generator. Then we use the RND Function to generate a random number between 1 and total number of records availabe ( which we get by using Recordset.RecordCount property ).

	' Now moving the cursor to random record number
		rs.Move intRnd

Once we have got a random number between 1 and total number of records, we can move the Recordset Cursor to that random record by using Recordset.Move Function.

	' Showing the random statement
	Response.Write "<b>" & rs("statement") & "</b>"

Next we show the random record. Once done we close the database connection and remove the Recordset Object that we created.

Running the ASP page
You should place both the random.mdb and rndrec.asp files in the same directory. Assuming that you placed both of them under /rndRec/ directory under your virtual directory, you should use http://127.0.0.1/rndRec/rndrec.asp URL to see your ASP page on your local computer.

You will notice that a random statement is displayed every time you hit the refresh button. Well done! you have successfully created a random statement displayer ASP application.

What have we learned ?
We saw how to show a random record from the database. This technique that you have learned is so very often used that I can't even think of any ASP-Database application that I make which doesn't use it.

All you need to do
Just remember to set the Recordset Cursor location to adOpenKeyset i.e. 3. Then randmize the timer and generate a random number as stated on the last page. After that move the Recordset Cursor to that random record number and show it.

Keep coming back and help make Stardeveloper.com become best online resource for server side languages. Thanks for your time.