The code below accesses an Access database called 'Friends.mdb' with a table 'tblFriends'. The table has 3 fields firstly an 'ID' field that is an autonumber, secondly a field called 'FirstName' which is a textfield and lastly another textfield called 'SurName'. Our code will select a random record from our table and loop through all the recordset fields printing out the values.
You will notice that the code below doesn't expressly retrieve these fields, rather it uses generic code that will retrieve the recordset fields and values from any database and table structure.
Call our file 'random_record.asp'.
<%@ Language="VBScript" %>
<% Option Explicit %>
<html>
<head>
<title>Selecting a random record</title>
</head>
<body>
<%
'declare your variables
dim connection, recordset, sConnString, sql
dim intRandomNumber, intTotalRecords, i
'declare SQL statement that will query your
database
sql = "SELECT * FROM tblFriends"
'create ADO connection and recordset
object
Set connection = Server.CreateObject("ADODB.Connection")
Set recordset = Server.CreateObject("ADODB.Recordset")
'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=" & Server.MapPath("Friends.mdb")
'Open the connection to the database
connection.Open(sConnString)
'Open the recordset object executing the
SQL
recordset.Open sql, connection, 3, 1
'count the number of records and hold this
is the variable intTotalRecords
intTotalRecords = recordset.RecordCount
Randomize()
intRandomNumber = Int(intTotalRecords * Rnd)
'move to the random number returned
recordset.Move intRandomNumber
'open the table
Response.write("<table border='1'><tr>")
'loop through the total number of fields
For i = 0 to recordset.Fields.Count - 1
'write out the field value
Response.write("<td>" & recordset(i) &
"</td>")
Next
'close the table
response.write("</tr></table>")
'close the recordset and connection objects and free up resources
recordset.Close
Set recordset=Nothing
connection.close
Set connection=Nothing
%>
</body>
</html>
| Previous: Are there any Records |
Get the best asp hosting provider from web-hosting-top.com and save up to 30%
Plug and play ASP membership script that integrates with PayPal to let you charge recurring membership fees.
