4GuysFromRolla.com : ASP FAQS : Arrays
Question:	How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset?
Answer:	Conceptually, a Recordset is nothing more than a glorified two-dimensional array. A Recordset consists of zero to many rows, each row having zero to many columns, just like a two-dimensional array. Specifically, each cell of a Recordset is a Field object, storing information like the name of the column, the value of the cell, the data type, the max length, the precision, whether or not the cell can contain a NULL value, etc. Using the GetRows() method of the Recordset object we can turn the contents of a Recordset into a two-dimensional array. Understand that this array contains less information that the Recordset. Only the values of each of the cells in the Recordset are stored in the array, information like the column's name, the data type, etc., are not stored in this array. You may be wondering why, exactly, one would want to use GetRows() as opposed to just looping through a Recordset. For a good read on the performance advantages of using GetRows(), be sure to read Why GetRows() is Best to Fetch Data. The complete technical syntax for GetRows() can be seen here. In its simplest form, all you need to do is open a Recordset object and then call GetRows(): 'Assumes there is an open Connection object, objConn 'Create a Recordset Dim objRS Set objRS = Server.CreateObject("ADODB.Recordset") objRS.Open "SELECT * FROM Table1", objConn 'Now, read the Recordset into a 2d array Dim aTable1Values aTable1Values = objRS.GetRows() That's all there is to it! At this point, aTable1Values contains a row for each row in the Recordset objRS and a column for each of the Recordset's columns. At this point, we can close our Recordset and Connection objects and still work with the data in the array. To display this array data, we need to use two nested For loops. The first loop needs to loop through each row. The total number of rows can be found by examining the upper bound of the second dimension of the array (UBound(ArrayName, 2)). Next, an inner loop needs to step through each column of the current row. This can be done via a For loop as well; to find the total number of columns, retrieve the upper-bound of the first dimension of the array (UBound(ArrayName, 1)). So, our code to loop through the two-dimensional array would look like: Dim iRowLoop, iColLoop For iRowLoop = 0 to UBound(aTable1Values, 2)   For iColLoop = 0 to UBound(aTable1Values, 1)     Response.Write(aTable1Values(iColLoop, iRowLoop) & "<br>")   Next 'iColLoop   Response.Write("<p>") Next 'iRowLoop A live demo of using GetRows() can be found here. Be sure to check it out! GetRows() also can accept up to three optional parameters. Let's take a close look at the third optional parameter, which allows us to only bring back certain columns of the Recordset into the array. (For a discussion on all three of the optional parameters, be sure to read the technical docs.) This third, optional parameter can specifies what columns to bring back. If you don't provide this parameter (as we did not provide it in our earlier example, all of the columns will be returned. If, however, you only want a subset of columns back, you can specify those column names you'd want back using an array. For example: ArrayName = objRS.GetRows(, , Array("ColumnName1", "ColumnName2", ...)) An example of using this third parameter can be seen in the live demo. For an examination of turning a two-dimensional array into a Recordset, be sure to read: Inserting the Contents of a Two-Dimensional Array into a Database. Happy Programming!
FAQ posted by Scott Mitchell at 6/14/2001 5:02:03 PM to the Arrays category. This FAQ has been viewed 238,089 times.