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)