When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
Technology Jobs
ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs

The 4 Guys Present: ASPFAQs.com

Jump to a FAQ
Enter FAQ #:
..or see our 10 Most Viewed FAQs.

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?


[Print this FAQ]

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.

Do you have a FAQ you'd like to suggest? Suggestions? Comments? If so, send it in! Also, if you'd like to be a FAQ Admin (creating/editing FAQs), let me know! If you are looking for other FAQs, be sure to check out the 4Guys FAQ and Commonly Asked Messageboard Questions!

Most Viewed FAQs:

1.) How can I format numbers and date/times using ASP.NET? For example, I want to format a number as a currency. (761643 views)
2.) I am using Access and getting a 80004005 error (or a [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)' error) when trying to open a connection! How can I fix this problem? (207777 views)
3.) How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset? (202549 views)
4.) How can I quickly sort a VBScript array? (196039 views)
5.) How can I find out if a record already exists in a database? If it doesn't, I want to add it. (156019 views)
6.) How do I display data on a web page using arrays instead of Do...While...MoveNext...???... (152331 views)
7.) When I get a list of all files in a directory via the FileSystemObject, they aren't ordered in any reasonable way. How can I sort the files by name? Or by size? Or by date created? Or... (140381 views)
8.) For session variables to work, must the Web visitor have cookies enabled? (110162 views)
9.) Can I send emails without using CDONTS? (107083 views)
10.) How can I take the result of a SELECT...MULTIPLE or a group of same-named checkboxes and turn it into a query? That is, if the user selects 3 answers, how can I construct a query that looks for all 3? (106308 views)
Last computed at 9/17/2007 3:22:00 AM

Software Developer / Programmer - Distributed Systems (NYC)
Next Step Systems
US-NY-New York

Justtechjobs.com Post A Job | Post A Resume


ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article