When you think ASP, think...
Recent Articles xml
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips
spgif spgif

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
spgif spgif
ASP ASP.NET ASP FAQs Feedback topnav-right

The 4 Guys Present: ASPFAQs.com

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

4GuysFromRolla.com : ASP FAQS : Arrays


How do I display data on a web page using arrays instead of Do...While...MoveNext...???...

[Print this FAQ]

Answer: This FAQ describes the process of displaying data on a Web page through arrays instead of the traditional Recordset method. The way we've all likely used to display arrays involves opening a Recordset object and then using a Do While Not objRS.EOF ... Loop, iterating through each row in the Recordset. While this approach is fine and good, there are advantages to using a special Recordset method called GetRows(), which reads the contents of the Recordset into a two-dimensional array; then, to display the Recordset data, you can simply iterate through this array.

While the syntax involved in using the array-based approach is a bit more confusing that simply looping through the Recordset via a Do While Not objRS.EOF ... Loop, the main motivation behind using GetRows() is performance. For a good read on the performance advantages be sure to read Why GetRows() Is Best to Fetch Data.

This builds on previous FAQ's in treating Recordsets as arrays, and vice versa. Read the FAQ below as well as the documentation on GetRows if you need to brush up.

FAQ: How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset?

Before we get started there are three important things to remember when working with GetRows; remembering these three things will save you many headaches and hours debugging, trust me! They are:

1. VBScript arrays start at 0 and go to n-1, where n is the count of the records (or count of columns) in the recordset. If you have 30 records, you will have rows numbered from 0 to 29; if you have 5 columns, you will have columns numbered from 0 to 4. In other words, just as if you had done Dim rows(4,29) to declare the array size yourself.
2. VBScript arrays are arranged by MyArray(ColumnElement, RowNumber) instead of MyArray(RowNumber, ColumnElement)
3. The order of field selection in your SQL statement determines the column subscript used to access that field. If you have 5 fields you will have 0 to 4 column elements.

Imagine that you have the following SELECT SQL statement:

Select fName, lName, Address, City, State, Zip
FROM SomeTable
ORDER BY lName, fName

and that you have already created and opened both Connection and Recordset objects. To read the contents of the Recordset into an array, use:

MyArray = rsMyRecordSet.GetRows()

VBScript sets up MyArray(ColumnCount, RowCount) with ColumnCount being the number of fields selected - 1 (0 based) and RowCount being the number of records returned - 1 (0 based)

(NOTE: If you want the array in any specific order, you can use the ORDER BY clause in your select. We can't recommend trying to sort the two-dimensional arrays that GetRows produces, as most any process you use to do so will be, at best, slow and tedious. The method described in this FAQ, for example, is completely inadequate and unusable with two-dimensional arrays.)

In the above select statement, the data will be in the order of the columns in the SELECT clause - that is, the 0th column will contain the value of the current row's fName value; the 1st column will contain the value of the current row's lName value; etc.

To access the elements of an array we need to use integer index values. To get the first column of the first row, we'd use: MyArray(0,0). This approach, however, is very unreadable. To make your code more readable (and hence more maintainable), consider creating constants whose values represent the position in the array for the column and whose name is similar to the column's name. For example, it would be prudent to add:

Const MyFirstNameOrdinal = 0
Const MyLastNameOrdinal = 1
Const MyAddressOrdinal = 2
Const MyCityOrdinal = 3
Const MyZipOrdinal = 4

In this instance you will have five columns in the array numbered 0 through 4.

Now that we know how our array is going to be laid out we can display the array's contents using a For...Next loop construct. In order to use this construct we must know what the loop bounds are. Clearly we want to start from the 1st row and 1st column (array indexes 0,0). But how high do we have to go? That depends on how many columns and rows our SQL statement returns. Since we do not know the number of rows returned when writing our script, we'll have to use the VBScript UBound function, which returns the total number of elements in an array. (An example of using UBound can be seen in this FAQ.)

Of course, UBound is a trifle trickier to use when dealing with multi-dimensional arrays (recall that GetRows() returns a two-dimensional array). When using UBound with multi-dimensional arrays, you must specify the dimension you wish to get the upperbound for. So, if we want to get the total number of columns or rows, we have to use different UBound statements, as shown below:

Ubound(MyArray,1) 'Returns the Number of Columns
Ubound(MyArray,2) 'Returns the Number of Rows

For our first example I will show how to display the data in some predetermined format, like:

lName, fName : address : city

The code to do this would be a simple loop through each of the rows in the array; then, in the loop body, Response.Write statements would output the proper array values:

For lnLoopCounter = 0 To Ubound(MyArray,2)
Response.Write MyArray(MyLastNameOrdinal, lnLoopCounter) _
      & ", " _
      & MyArray(MyFirstNameOrdinal, lnLoopCounter) _
      & " : " _
      & MyArray(MyAddressOrdinal, lnLoopCounter) _
      & " : " _
      & MyArray(MyCityOrdinal, lnLoopCounter) _
      & "<BR>" & vbNewLine

If you were wanting to display the fields in the exact order as they were presented in your SELECT clause you could add an inner loop for the columns. I usually just manually output the columns (as shown above) for simplicity, but either approach will work:

For lnRowCounter = 0 To Ubound(MyArray,2)
For lnColumnCounter = 0 To Ubound(MyArray,1)
Response.Write MyArray(lnColumnCounter, lnRowCounter)

Happy Programming !!!

... By KissLizzyCooper

FAQ posted by Scott Mitchell at 4/11/2002 1:14:41 PM to the Arrays category. This FAQ has been viewed 186,006 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

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