A Generic GetRows VBScript Class

The GetRows method of the ADO object is known to be the fastest way to fetch a recordset. By encapsulating the logic within a VBScript class, it's possible to query your database and return your records to a local array in only 3 lines of code.

Published: Apr 18, 2002
Tested with: ASP 3.0
Category: ASP
28,234 views

Introduction

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.

1	Class Database
2
3	Dim i_dbConnection
4	Dim i_objConn
5	Dim i_objRS
6
7	Private Sub Class_Initialize()
8	Const MAX_TRIES = 10
9	Dim intTries
10	On Error Resume Next
11	Do
12	Err.Clear
13	'edit the next line to point to your database
14	i_dbConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/faqs.mdb")
15	Set i_objConn = Server.CreateObject("ADODB.Connection")
16	i_objConn.Open i_dbConnection
17	Set i_objRS = Server.CreateObject("ADODB.Recordset")
18	intTries = intTries + 1
19	Loop While (Err.Number <> 0) And (intTries < MAX_TRIES)
20	End Sub
21
22	Public Function GetArray(strQuery)
23	'-- Cursor Type, Lock Type
24	' ForwardOnly 0 - ReadOnly 1
25	' KeySet 1 - Pessimistic 2
26	' Dynamic 2 - Optimistic 3
27	' Static 3 - BatchOptimistic 4
28	i_objRS.Open strQuery, i_objConn, 0, 1
29	If Err.Number <> 0 Then
30	Response.Write("There was an error processing your request.<br>Please try again.")
31	Exit Function
32	Else
33	If i_objRS.EOF and i_objRS.BOF Then
34	Response.Write("There are currently no records returned.")
35	Exit Function
36	Else
37	GetArray = i_objRS.GetRows()
38	End If
39	End If
40	End Function
41
42	Private Sub Class_Terminate()
43	Const adOpenState = 1 'indicates that the object is open
44	If Not i_objRS Is Nothing Then
45	If i_objRS.State = adOpenState Then
46	i_objRS.Close
47	End If
48	Set i_objRS = Nothing
49	End If
50	If Not i_objConn Is Nothing Then
51	If i_objConn.State = adOpenState Then
52	i_objConn.Close
53	End If
54	Set i_objConn = Nothing
55	End If
56	End Sub
57
58	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.

1	Dim numCols 'number of fields per row in results
2	Dim numColCounter 'used to loop through the columns
3	Dim numRows 'number of rows in results
4	Dim numRowCounter 'used to loop through the rows
5	Dim strthisfield 'current field in loop
6
7	numCols = UBound(arrAllData, 1)
8	numRows = UBound(arrAllData, 2)
9	Response.Write("<table border=""1"">" & vbcrlf)
10
11	'loop through rows
12	For numRowCounter = 0 To numRows
13	Response.Write("<tr>" & vbcrlf)
14	'for each column
15	For numColCounter = 0 to numCols
16	strthisfield = arrAllData(numColCounter, numRowCounter)
17	If IsNull(strthisfield) Then
18	strthisfield = "-null-"
19	End If
20	If Trim(strthisfield) = "" Then
21	strthisfield = " "
22	End If
23	Response.Write("<td valign=top>" & strthisfield & "</td>" & vbcrlf)
24	Next
25	Response.Write("</tr>" & vbcrlf)
26	Next
27
28	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.

1	'declare local variables for fields in array
2	Dim strQuestion
3	Dim strAnswer
4	Dim datCreated
5	Dim strPoster
6	Dim i
7
8	For i = 0 To UBound(arrAllData, 2)
9	strQuestion = arrAllData(0, i)
10	strAnswer = arrAllData(1, i)
11	datCreated = arrAllData(2, i)
12	strPoster = arrAllData(3, i)
13	'do something with these variables
14	'like a Response.Write
15	Next

Either way works fine. Use the one that works best for you.

Conclusion

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)