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