Link to home
Start Free TrialLog in
Avatar of coolispaul
coolispaulFlag for United States of America

asked on

storing a result set in an array with ASP

Hi ,

i want to be able to store my database resultset in an array so that i can output it further down the page. I know how to do this with PHP by how can i with ASP

What i have so far is:

SQL = "select DISTINCT FolderL1 from [ShellOSOE].[dbo].[ReleaseLog] where Lever = 'Your Team'"
'get documents

rs.open SQL, objConn

Dim myDynArray()
'will iterate to display the records got from the database
While Not rs.EOF
  response.write(rs("FolderL1") & "<br> ")
  myDynArray(count) = rs("FolderL1")
  count = count + 1
  rs.MoveNext
Wend  


Howvere this gives me a "Subscript out of range" error. Any ideas how i can do this?
Perhaps i should just close the db connection at the end of the page but i wanted to get the results from the DB , store them in an array then close the connection straight away.
Avatar of golfDoctor
golfDoctor

use the GetRows function of recordset.
http://www.w3schools.com/ado/met_rs_getrows.asp
ASKER CERTIFIED SOLUTION
Avatar of dev2dev
dev2dev
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
dev2dev - getRows is the appropriate way to do this to solve this problem "i want to be able to store my database resultset in an array ".  This is precisely what getRows does.   There is no need to put the result set in a string with getString.  Please do some research before commenting on what should and should not be used.  Thanks.
correct,
but the issue looks something else as i explained next :D
Try this:
Redim myDynArray(0)
'will iterate to display the records got from the database
While Not rs.EOF
  response.write(rs("FolderL1") & "<br> ")
  Redim Preserve myDynArray(UBound(myDynArray)+1)
  myDynArray(UBound(myDynArray)-1) = rs("FolderL1")
  rs.MoveNext
Wend

Open in new window

Why are you guys providing the incorrect/inefficient way to solve this?

Looping through the recordset to build an array is RIDICULOUS and a waste of resources.  If you want to help solve problems, at least try to solve them properly, and educate the asker on the appropriate way to code. The purpose of getRows is exactly this "build array from recordset", so looping through recordset can be avoided, and save valuable resources.

Let's not just answer to answer.  If you see the appropriate answer is already posted, then don't post.  Yes, there are more ways to solve a problem, but getRows is by far the best solution.
Avatar of coolispaul

ASKER

hi ,

just to let you know this worked:
While Not rs.EOF
 
  redim Preserve myDynArray(count)
  myDynArray(count) = rs("FolderL1")
  count = count + 1
  rs.MoveNext
Wend

but i think golfdoctor is probably right in that GetRows will do what i was trying to do anyway - put the result into an array

Cheers
EXACTLY the problem!  You guys provide bad answers, yet the asker doesn't know any better and just select the first one that fixed his error, never actually learning how to do it properly.  Ridiculous!
coolispaul - it's not always best to use the answer that simply works.  If you want to learn to code, do it right.
golfDoctor - you may not agree with the other answers provided but to be so rude about it is not an attribute of an 'expert'. I have used my solution many times and never encouter any problems with performance. The getRows function may be more efficient but results in having the use a multi-dimensional array which can be more complicated for 'the asker (which) doesn't know any better'.

I'm sure the experts and askers alike do not appreciate your comments here... I know I don't.
Experts are supposed to "educate", not simply provide fixes for inefficient code.  

What if you're dealing with 1000's or more records?  Are you really suggesting to loop through them all, and ReDim on top of that?

Think about it.  As I said, there are many solutions to a problem, but not this.
Agree, it is not a good idea to loop through, much efficient way to use is to store in array using getrows fucntion.

golfdoctor and me gave answers without completely understanding the problem

we both gave solution for what is being asked in the subject/title of the post/question

but I immediately realized the problem (subscript out of range) is not related to what he is said in his problem statement

we both answered as per his problem statement (for storing result set into arry), golf's answer being more efficient and i agreed in my next post

but then i identified and gave solution where hiz code is breaking

so the author must be happy with my 2nd and impressed with 3rd post