Generating Random Records from the Databaseby Faisal Khan.
 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. 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. | 
.NET
ASP (16) 
Contains 1 or more replies by the Author of this Article.
Contains 1 or more replies by 
