getrows question - how to split getrows array into individual arrays?

Posted on 2004-11-23
Medium Priority
Last Modified: 2012-08-13
Sorry if this is a dumb question but I can't figure out how to get the results of a database query into separate arrays.  I'm using getrows to create a 2 D array consisting of divisionID and divisionName.  But I can't figure out how to make an array of only the divisionID records and another of only the divisionName records.  I use getrows all the time to retrieve records from a db but never have had to re-assigned those records to separate arrays.   I need the names and ids in separate arrays for use with a javascript function.

Thanks for the help
Question by:saabStory
  • 3
LVL 19

Accepted Solution

peh803 earned 1000 total points
ID: 12657439
Okay, so you can do something like this:

Dim my2dResultsArray
Dim myIDArray
Dim myNameArray
Dim i
my2dResultsArray = rs.getRows()
'' set the upper bound of the two, one-dimensional target arrays to the upper bound of the results array
redim myIDArray(ubound(my2dResultsArray ,2))
redim myNameArray(ubound(my2dResultsArray ,2))
''assuming that the ID's are the 0th element, and the names are the 1st element, loop through the array and make two new arrays:
For i = 0 to ubound(my2dResultsArray,2)
  myIDArray(i) = my2dResultsArray(0,i)
  myNameArray(i) = my2dResultsArray(1,i)

'Now, take a look at the array contents:
for i=0 to ubound(myIDArray)
  response.write "myIDArray("& i &"): " &myIDArray(i) & "<BR>"

for i=0 to ubound(myNameArray)
  response.write "myNameArray("& i &"): " &myNameArray(i) & "<BR>"
LVL 34

Expert Comment

by:Big Monty
ID: 12657469
if divisionID and divisionNAME are in the same recordset, then using getrows will put them in the same array. theres no way to split them using getrows. you could create a loop to put the data into seperate arrays:

dim arr1()
dim arr2()

rs.open "select divisionID, divisionName from someTable", objConn

counter = 0
do while not rs.EOF
   arr1(counter) = rs("divisionID")
   arr2(counter) = rs("divisionName")
   counter = counter + 1

set rs = nothing

LVL 19

Expert Comment

ID: 12657493
Basically, my previous post takes the 2d array and separates the values into two single dimension arrays.

So, if you have this:


     0 (ID Element)    1 (NameElement)
0  "2"                      "Division A"
1  "4"                      "Division D"
2  "7"                      "Division Q"
3  "11"                    "Division R"
4  "16"                    "Division ZZ"
5  "20"                    "Division E"
6  "32"                    "Division F"

So, the 2d results array has the following characteristics:
-- ID is the 0th element
-- division name is the 1st element
-- ubound(my2dResultsArray,2) = 6

Now, the code re-dimensions two single dimension arrays to the upper bound of the second dimension of the results array (one for the names and one for the IDs) and then populates the two single dimension arrays as follows:

0  "2"
1  "4"
2  "7"
3  "11"
4  "16"
5  "20"
6  "32"

0  "Division A"
1  "Division D"
2  "Division Q"
3  "Division R"
4  "Division ZZ"
5  "Division E"
6  "Division F"

Hope this helps!!


Author Comment

ID: 12657514
Thanks peh803 - that works.  In fact, it looks just like what I was doing but I was getting a type mismatch when I was trying to create the singleton arrays.  I'll be comparing to see where I went off track.  Thanks for the help.
LVL 19

Expert Comment

ID: 12657532
Glad I could help!


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question