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

Posted on 2004-11-23
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
    LVL 19

    Accepted Solution

    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 32

    Expert Comment

    by:Big Monty
    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() "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

    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

    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

    Glad I could help!


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
    This demonstration started out as a follow up to some recently posted questions on the subject of logging in: and…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now