Speedup Database Access using GetRows

Now comes the real part. Here we use two For ... Next loops to show all the records present in the ds array.

We first determine the number of rows retrieved by looking at the upper limit of the 2nd dimension of the ds variable. This is how we move row by row, showing the contents of the rows and columns.

Then within the For ... Next loop of each row we start a new For ... Next loop to show the content of all the columns.

Running the ASP page
You should place both the paging.mdb and getrows.asp files in the same directory. Assuming that you placed both of them under /getrows/ directory under your virtual directory, you should use http://127.0.0.1/getrows/getrows.asp URL to see your ASP page on your local computer.

You will notice that the results appear faster than using the usual way of accessing a database. Even if you don't notice that on your own computer, rest assured that the ASP page will run faster and database will be able to handle more requests when a lot more users access them at the same time.

So we saw that there are other faster ways of accessing a database and showing it's results than simply keeping the database connection patent while showing it's records. We learned a very useful Recordset.GetRows method which will retrieve all the records in a recordset into a two dimensional array. We can then using two For ... Next loops show all the contents of that Array and Erase it when we are done.

Remember this
Notice that you can create a Recordset any way you want, like you can open a table, run a query, a stored procedure etc to generate a recordset. Then simply use Recordset.GetRows to get all those records into a two dimensional Array and close the database connection.

I am saying this so that you may not start to think that the only way to create a recordset for creating a two dimensional Array is to open a table like we did in this article. You can create the Recordset anyway you like, like you do normally and just use the Recordset.GetRows method.

' Now Showing Records from our DataSet
Dim i, j
' Creating table to show records
Response.Write "<table align=""center"" border=""1"" width=""70%"""
Response.Write " cellspacing=""1"" cellpadding=""3"" bordercolor=""silver"">"
Response.Write vbcrlf
Response.Write "<tr><td colspan=""" & (UBound(ds, 1) + 1)
Response.Write """ align=""center"">Names</td></tr>"
Response.Write "<tr><td>ID</td><td>First " & _
	"Name</td><td>Last Name</td></tr>"

Here we just create a table and give useful names to it's columns.

	' Showing Each Row
	For i = 0 To UBound(ds, 2)
		Response.Write "<tr>" & vbcrlf
		' Showing Each Column

		For j = 0 To UBound(ds, 1)
			Response.Write "<td>"
			Response.Write ds(j, i)
			Response.Write "</td>"
		Next

		Response.Write "</tr>" & vbcrlf
	Next

Now comes the real part. Here we use two For ... Next loops to show all the records present in the ds array.

	' Showing Each Row
	For i = 0 To UBound(ds, 2)

We first determine the number of rows retrieved by looking at the upper limit of the 2nd dimension of the ds variable. This is how we move row by row, showing the contents of the rows and columns.

	' Showing Each Column
	For j = 0 To UBound(ds, 1)
		Response.Write "<td>"
		Response.Write ds(j, i)
		Response.Write "</td>"
	Next

Then within the For ... Next loop of each row we start a new For ... Next loop to show the content of all the columns.

	Erase ds

Keep learning!