Articles: Want to get a random row from a table?

The method is simple, we get all the data and insert it in an array, then we don't have to worry about gaps in our numbering system (id), since the array will be numbered for us. If you are certain that there is no gaps, you can get away with just randomizing a number between 1 and your Max, and select that ID from the table

<%
Option Explicit
Dim objConn, objRS, strConn, strSQL, iCount, iRow, aData
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.RecordSet")
strConn = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("file.mdb")

The Option Explicit requires us to define all our variables, this usually make up for better scripting, it is however not required for the method to work. The following line declares the variables we are going to use, then come a part that should be familiar to anyone working with ASP against databases, we create a connection, and we create an explicit recordset, then we supply a connection string.

The next thing we do is to convert the data into an array, this is straight forward, using the GetRows() function.

objConn.Open strConn
strSQL = "SELECT * FROM table"
objRS.Open strSQL, objConn, 3,1
aData = objRS.GetRows()
objRS.Close
objConn.Close

The only thing you need to worry about changing is the SQL line, where it should only be necessary to change the table name. The next thing we are going to do is to get the number of records and then decide what row to get.

iCount = UBound(aData, 2)
Randomize
iRow = Int(Rnd * iCount)

We get the upper bound of the array, and generate a random number between 0 and that number (since the array start at 0). The Rnd will generate a number between 0 and 1, so we multiply it with the number of rows possible, and make that an int. If we want it to start at a location we then + x that, but we don't want that now, since the array start at 0.

Next we print the data we want, this is really simple.

Response.Write aData(0, iRow) & aData(1, iRow) & aData(2, iRow)

aData(x, y) where x is the column we want to get and y is the row. its numerical from 0 and upwards, depending on the number of columns we include.

now its just to finish up, setting the objects to null and closing the ASP snippet.

Set objRS = Nothing
Set objConn = Nothing
%>

Enjoy