A Generic GetRows VBScript Class

The GetRows method of the ADO object is known to be the fastest way to fetch a recordset. In this article, I will show you a VBScript class that you can use against any database. You call a public function with your SELECT query as the argument, and it returns your resultset in the form of an array.

VBScript Class

Below is the complete code for the VBScript class Database. Notice that in the Sub Class_Initialize I am using a trick where it tries up to 10 times to get a connection to the database. For a busy site with a not such powerful database (like MS Access), this can be a life saver.

To make this work for you, you need to edit the class code to point to your database. The i_dbConnection points to my access database. Change it appropriately.

Class Database

	Dim i_dbConnection
	Dim i_objConn
	Dim i_objRS

	Private Sub Class_Initialize()
		Const MAX_TRIES = 10
		Dim intTries
		On Error Resume Next
		Do
			Err.Clear
			'edit the next line to point to your database
			i_dbConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/faqs.mdb")
			Set i_objConn = Server.CreateObject("ADODB.Connection")
				i_objConn.Open i_dbConnection
			Set i_objRS = Server.CreateObject("ADODB.Recordset")
			intTries = intTries + 1
		Loop While (Err.Number <> 0) And (intTries < MAX_TRIES)
	End Sub

	Public Function GetArray(strQuery)
		'-- Cursor Type, Lock Type
		' ForwardOnly 0 - ReadOnly 1
		' KeySet 1 - Pessimistic 2
		' Dynamic 2 - Optimistic 3
		' Static 3 - BatchOptimistic 4
		i_objRS.Open strQuery, i_objConn, 0, 1
		If Err.Number <> 0 Then
			Response.Write("There was an error processing your request.<br>Please try again.")
			Exit Function
		Else
			If i_objRS.EOF and i_objRS.BOF Then
				Response.Write("There are currently no records returned.")
				Exit Function
			Else
				GetArray = i_objRS.GetRows()
			End If
		End If
	End Function

	Private Sub Class_Terminate()
		Const adOpenState = 1 'indicates that the object is open
		If Not i_objRS Is Nothing Then
			If i_objRS.State = adOpenState Then
				i_objRS.Close
			End If
			Set i_objRS = Nothing
		End If
		If Not i_objConn Is Nothing Then
			If i_objConn.State = adOpenState Then
				i_objConn.Close
			End If
			Set i_objConn = Nothing
		End If
	End Sub

End Class

The code above shows a typical scenario of using a SELECT statement. Let’s say you pass an ID to a page through the URL (“page.asp?ID=3″). You want to capture this ID, add it to your query which is then executed. All that can be seen above. You create an instance of the class, and call the public function GetArray which needs the query as an argument. A local variable arrAllData is used to store the returning results as an array. Then you can simply loop through this variable and write out the results.

Looping through your results

There are 2 ways to loop through your results. One way can be an automatic loop.

Dim numCols 'number of fields per row in results
Dim numColCounter 'used to loop through the columns
Dim numRows 'number of rows in results
Dim numRowCounter 'used to loop through the rows
Dim strthisfield 'current field in loop

numCols = UBound(arrAllData, 1)
numRows = UBound(arrAllData, 2)
Response.Write("<table border=""1"">" & vbcrlf)

'loop through rows
For numRowCounter = 0 To numRows
	Response.Write("<tr>" & vbcrlf)
	'for each column
	For numColCounter = 0 to numCols
		strthisfield = arrAllData(numColCounter, numRowCounter)
		If IsNull(strthisfield) Then
			strthisfield = "-null-"
		End If
		If Trim(strthisfield) = "" Then
			strthisfield = " "
		End If
		Response.Write("<td valign=top>" & strthisfield & "</td>" & vbcrlf)
	Next
	Response.Write("</tr>" & vbcrlf)
Next

Response.Write("</table>")

By looping as shown above you don’t have to worry about how many fields you are selecting. You can change your SELECT statement and everything will still function as expected. You can even do a SELECT * and it will still work.

A second way of looping through your local array is to assign individual values in the array to local variables. This way you have more control over your variables and how to use them.

'declare local variables for fields in array
Dim strQuestion
Dim strAnswer
Dim datCreated
Dim strPoster
Dim i

For i = 0 To UBound(arrAllData, 2)
	strQuestion = arrAllData(0, i)
	strAnswer = arrAllData(1, i)
	datCreated = arrAllData(2, i)
	strPoster = arrAllData(3, i)
	'do something with these variables
	'like a Response.Write
Next

Either way works fine. Use the one that works best for you. One could easily modify this class to meet their needs. For example, you could expand the number of arguments in the GetArray Function to match the standard arguments of the GetRows method:
GetRows (Rows, Start, Fields)