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.
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)