Recordset to Array

Report · Sep 15, 2007

Hi,

Is it possible to create an array from a recordset, I googled and found some
solution but could not get any to work

Dim rsReviewerInfo
Dim rsReviewerInfo_numRows

Set rsReviewerInfo = Server.CreateObject("ADODB.Recordset")
rsReviewerInfo.ActiveConnection = MM_dbtest_STRING
rsReviewerInfo.Source = "SELECT NameInitial, FullName From Contact_tbl"
rsReviewerInfo.CursorType = 0
rsReviewerInfo.CursorLocation = 2
rsReviewerInfo.LockType = 1
rsReviewerInfo.Open()

rsReviewerInfo_numRows = 0

Any help is appreciated

Thanks!

Report · Sep 15, 2007

MM User wrote:
> Hi,
>
> Is it possible to create an array from a recordset, I googled and found
> some solution but could not get any to work
>
> Dim rsReviewerInfo
> Dim rsReviewerInfo_numRows
>
> Set rsReviewerInfo = Server.CreateObject("ADODB.Recordset")
> rsReviewerInfo.ActiveConnection = MM_dbtest_STRING
> rsReviewerInfo.Source = "SELECT NameInitial, FullName From Contact_tbl"
> rsReviewerInfo.CursorType = 0
> rsReviewerInfo.CursorLocation = 2
> rsReviewerInfo.LockType = 1
> rsReviewerInfo.Open()
>
> rsReviewerInfo_numRows = 0

The GetRows method of the recordset object returns a two dimensional array, the first dimension is for the rows, and the second dimension are the columns of the recordset (number based *not* name based).

So for you recordset above:
Dim MyArray
MyArray = rsReviewerInfo.GetRows() ' returns all records and all columns

Response.Write(MyArray(1, 1)) ' writes out the contents of the second row second column

For more info:
http://w3schools.com/ado/met_rs_getrows.asp

--
Danilo Celic
| Extending Knowledge Daily : http://CommunityMX.com/
| Adobe Community Expert

Report · Sep 15, 2007

Thanks Danilo,

That was what I was looking for - is it possible to assign a variable to the
fields i.e.

ReviewerID = 1
i=0

Do While Not UBound(MyArray)
"Reviewer"&ReviewerID = (MyArray(0,0) & "" & MyArray(1,0))
i = i +1
ReviewerID = ReviewerID +1
Loop

Regards,

Alex

"danilocelic AdobeCommunityExpert" <danilo@shimmerphase.com> wrote in
message news:fch0a7$1mb$1@forums.macromedia.com...
> MM User wrote:
>> Hi,
>>
>> Is it possible to create an array from a recordset, I googled and found
>> some solution but could not get any to work
>>
>> Dim rsReviewerInfo
>> Dim rsReviewerInfo_numRows
>>
>> Set rsReviewerInfo = Server.CreateObject("ADODB.Recordset")
>> rsReviewerInfo.ActiveConnection = MM_dbtest_STRING
>> rsReviewerInfo.Source = "SELECT NameInitial, FullName From Contact_tbl"
>> rsReviewerInfo.CursorType = 0
>> rsReviewerInfo.CursorLocation = 2
>> rsReviewerInfo.LockType = 1
>> rsReviewerInfo.Open()
>>
>> rsReviewerInfo_numRows = 0
>
>
> The GetRows method of the recordset object returns a two dimensional
> array, the first dimension is for the rows, and the second dimension are
> the columns of the recordset (number based *not* name based).
>
> So for you recordset above:
> Dim MyArray
> MyArray = rsReviewerInfo.GetRows() ' returns all records and all columns
>
> Response.Write(MyArray(1, 1)) ' writes out the contents of the second row
> second column
>
> For more info:
> http://w3schools.com/ado/met_rs_getrows.asp
>
>
> --
> Danilo Celic
> | Extending Knowledge Daily : http://CommunityMX.com/
> | Adobe Community Expert

Report · Sep 15, 2007

MM User wrote:
> Thanks Danilo,
>
> That was what I was looking for - is it possible to assign a variable to
> the fields i.e.
>
> ReviewerID = 1
> i=0
>
> Do While Not UBound(MyArray)
> "Reviewer"&ReviewerID = (MyArray(0,0) & "" & MyArray(1,0))
> i = i +1
> ReviewerID = ReviewerID +1
> Loop

Alex,

I'm not sure what you're asking for here because your code doesn't make sense to me. The first line seems to say to put you into an infinite loop if you have any elements in the array.

Then there is this line:
"Reviewer"&ReviewerID = (MyArray(0,0) & "" & MyArray(1,0))

First off you can't have the left side of the = do what you're doing (adding two values together). Plus the right side of the = doesn't make sense to me, as that says take the value of first row first column add an empty string then add the value of the second row first column. Perhaps it would make sense depending on exactly what would be in the recordset, but even then you're doing the same operation each time throughout the loop (which you're never going to exit as it's an infinite loop)

What exactly are you trying to do here?

--
Danilo Celic
| Extending Knowledge Daily : http://CommunityMX.com/
| Adobe Community Expert

Report · Sep 16, 2007

Danilo,

Sorry for the confusion, what I was after was to combine the 1st column and
2nd column strings to a new variable reviewerX:

so you would get:

reviewer1 = value of row 1 column 1 & row 1 column 2
reviewer2 = value of row 2 column 1 & row 2 column 2
reviewer3 = value of row 3 column 1 & row 3 column 2
reviewer4 = value of row 4 column 1 & row 4 column 2
reviewer5 = value of row 5 column 1 & row 5 column 2
reviewer6 = value of row 6 column 1 & row 6 column 2
etc until there are none left

hope this makes sense

"danilocelic AdobeCommunityExpert" <danilo@shimmerphase.com> wrote in
message news:fchkle$l6v$1@forums.macromedia.com...
> MM User wrote:
>> Thanks Danilo,
>>
>> That was what I was looking for - is it possible to assign a variable to
>> the fields i.e.
>>
>> ReviewerID = 1
>> i=0
>>
>> Do While Not UBound(MyArray)
>> "Reviewer"&ReviewerID = (MyArray(0,0) & "" & MyArray(1,0))
>> i = i +1
>> ReviewerID = ReviewerID +1
>> Loop
>
> Alex,
>
> I'm not sure what you're asking for here because your code doesn't make
> sense to me. The first line seems to say to put you into an infinite loop
> if you have any elements in the array.
>
> Then there is this line:
> "Reviewer"&ReviewerID = (MyArray(0,0) & "" & MyArray(1,0))
>
> First off you can't have the left side of the = do what you're doing
> (adding two values together). Plus the right side of the = doesn't make
> sense to me, as that says take the value of first row first column add an
> empty string then add the value of the second row first column. Perhaps it
> would make sense depending on exactly what would be in the recordset, but
> even then you're doing the same operation each time throughout the loop
> (which you're never going to exit as it's an infinite loop)
>
> What exactly are you trying to do here?
>
>
>
> --
> Danilo Celic
> | Extending Knowledge Daily : http://CommunityMX.com/
> | Adobe Community Expert

Report · Sep 16, 2007

MM User wrote:
> Danilo,
>
> Sorry for the confusion, what I was after was to combine the 1st column and
> 2nd column strings to a new variable reviewerX:
>
> so you would get:
>
> reviewer1 = value of row 1 column 1 & row 1 column 2
> reviewer2 = value of row 2 column 1 & row 2 column 2
> reviewer3 = value of row 3 column 1 & row 3 column 2
> reviewer4 = value of row 4 column 1 & row 4 column 2
> reviewer5 = value of row 5 column 1 & row 5 column 2
> reviewer6 = value of row 6 column 1 & row 6 column 2
> etc until there are none left
>
> hope this makes sense
>
> "danilocelic AdobeCommunityExpert" <danilo@shimmerphase.com> wrote in
> message news:fchkle$l6v$1@forums.macromedia.com...
>> MM User wrote:
>>> Thanks Danilo,
>>>
>>> That was what I was looking for - is it possible to assign a variable
>>> to the fields i.e.
>>>
>>> ReviewerID = 1
>>> i=0
>>>
>>> Do While Not UBound(MyArray)
>>> "Reviewer"&ReviewerID = (MyArray(0,0) & "" & MyArray(1,0))
>>> i = i +1
>>> ReviewerID = ReviewerID +1
>>> Loop
>>
>> Alex,
>>
>> I'm not sure what you're asking for here because your code doesn't
>> make sense to me. The first line seems to say to put you into an
>> infinite loop if you have any elements in the array.
>>
>> Then there is this line:
>> "Reviewer"&ReviewerID = (MyArray(0,0) & "" & MyArray(1,0))
>>
>> First off you can't have the left side of the = do what you're doing
>> (adding two values together). Plus the right side of the = doesn't
>> make sense to me, as that says take the value of first row first
>> column add an empty string then add the value of the second row first
>> column. Perhaps it would make sense depending on exactly what would be
>> in the recordset, but even then you're doing the same operation each
>> time throughout the loop (which you're never going to exit as it's an
>> infinite loop)
>>
>> What exactly are you trying to do here?
>>
>>
>>
>> --
>> Danilo Celic
>> | Extending Knowledge Daily : http://CommunityMX.com/
>> | Adobe Community Expert
>

--
Danilo Celic
| Extending Knowledge Daily : http://CommunityMX.com/
| Adobe Community Expert

Report · Sep 16, 2007

MM User wrote:
> Danilo,
>
> Sorry for the confusion, what I was after was to combine the 1st column and
> 2nd column strings to a new variable reviewerX:
>
> so you would get:
>
> reviewer1 = value of row 1 column 1 & row 1 column 2
> reviewer2 = value of row 2 column 1 & row 2 column 2
> reviewer3 = value of row 3 column 1 & row 3 column 2
> reviewer4 = value of row 4 column 1 & row 4 column 2
> reviewer5 = value of row 5 column 1 & row 5 column 2
> reviewer6 = value of row 6 column 1 & row 6 column 2
> etc until there are none left
>
> hope this makes sense

Sorry about that other post, accidentally hit the send button when trying to move my message window around.

Also, I was wrong earlier when I was talking about what was stored in each dimension of the array returned by GetRows. What is actually returned are the column values in the first dimension and the rows in the second dimension. So MyArrry(0,1) refers to the first column (zero based) in the second row.

Anyway, exactly how you do it would again depend on what you're trying to do with the group of "reviewers". The code below does two things as it loops over the array returned by GetRows, it first writes out the values and then stuffs the value into another array. Again, depending on what you want to do with the data will depend on what you will end up doing. If all you're doing would be to write out the values, then you could have just as easily have looped over the recordset in the first place, which would be easy to do with Dreamweaver's built in tools. Here's the code:

<%
Dim MyArray
MyArray = RecordsetName.GetRows()

' Array to store all the reviewers
' Using ReDim to be able to use a variable to size the Reviewers array
Dim Reviewers: Reviewers = Array()
Redim Reviewers(UBound(MyArray)-1)

Dim idx
idx=0

While idx < UBound(MyArray)
Response.Write(MyArray(0,idx) & MyArray(1, idx) & "<br />" )
Reviewers(idx) = MyArray(0,idx) & MyArray(1, idx)
idx = idx + 1
Wend

%>

--
Danilo Celic
| Extending Knowledge Daily : http://CommunityMX.com/
| Adobe Community Expert

Report · Sep 17, 2007

Danilo,

Thanks again, I tried the code you provided but it seems I only retrieve 2
entries, if there is I entry it pops an error Subscript out of range: '1'

The reason for me storing the entries is so I may it elesewhere in the page
it appears several time i.e. <%=Reviewer1%>

Regards!

"danilocelic AdobeCommunityExpert" <danilo@shimmerphase.com> wrote in
message news:fckm41$sej$1@forums.macromedia.com...
> MM User wrote:
>> Danilo,
>>
>> Sorry for the confusion, what I was after was to combine the 1st column
>> and
>> 2nd column strings to a new variable reviewerX:
>>
>> so you would get:
>>
>> reviewer1 = value of row 1 column 1 & row 1 column 2
>> reviewer2 = value of row 2 column 1 & row 2 column 2
>> reviewer3 = value of row 3 column 1 & row 3 column 2
>> reviewer4 = value of row 4 column 1 & row 4 column 2
>> reviewer5 = value of row 5 column 1 & row 5 column 2
>> reviewer6 = value of row 6 column 1 & row 6 column 2
>> etc until there are none left
>>
>> hope this makes sense
>
> Sorry about that other post, accidentally hit the send button when trying
> to move my message window around.
>
> Also, I was wrong earlier when I was talking about what was stored in each
> dimension of the array returned by GetRows. What is actually returned are
> the column values in the first dimension and the rows in the second
> dimension. So MyArrry(0,1) refers to the first column (zero based) in the
> second row.
>
> Anyway, exactly how you do it would again depend on what you're trying to
> do with the group of "reviewers". The code below does two things as it
> loops over the array returned by GetRows, it first writes out the values
> and then stuffs the value into another array. Again, depending on what you
> want to do with the data will depend on what you will end up doing. If all
> you're doing would be to write out the values, then you could have just as
> easily have looped over the recordset in the first place, which would be
> easy to do with Dreamweaver's built in tools. Here's the code:
>
> <%
> Dim MyArray
> MyArray = RecordsetName.GetRows()
>
> ' Array to store all the reviewers
> ' Using ReDim to be able to use a variable to size the Reviewers array
> Dim Reviewers: Reviewers = Array()
> Redim Reviewers(UBound(MyArray)-1)
>
> Dim idx
> idx=0
>
> While idx < UBound(MyArray)
> Response.Write(MyArray(0,idx) & MyArray(1, idx) & "<br />" )
> Reviewers(idx) = MyArray(0,idx) & MyArray(1, idx)
> idx = idx + 1
> Wend
>
> %>
>
>
> --
> Danilo Celic
> | Extending Knowledge Daily : http://CommunityMX.com/
> | Adobe Community Expert

Report · Sep 17, 2007

MM User wrote:
> Danilo,
>
> Thanks again, I tried the code you provided but it seems I only retrieve 2
> entries, if there is I entry it pops an error Subscript out of range: '1'

Ah, I see...I tried getting the length of the columns dimension to loop over not the rows dimension. Try something like this:
<%
Dim MyArray
MyArray = Recordset1.GetRows()
' Array to store all the reviewers
' Using ReDim to be able to use a variable to size the Reviewers array
Dim Reviewers: Reviewers = Array()
Redim Reviewers(UBound(MyArray,2)-1)

Dim idx
idx=0

While idx < UBound(MyArray,2)
Response.Write(MyArray(0,idx) & MyArray(1, idx) & "<br />" )
Reviewers(idx) = MyArray(0,idx) & MyArray(1, idx)
idx = idx + 1
Wend

%>

This works on my system with a recordset that has 76 rows in it.

> The reason for me storing the entries is so I may it elesewhere in the page
> it appears several time i.e. <%=Reviewer1%>

As long as you know the index of the review, you can use to output the first reviewer (zero based index):
<%=Reviewers(0)%>

PS: Please do not write offlist unless you are asked to. I say this for a couple of reason:
1. The conversation on the forum allows the threads to be viewed in it's entirety to get to the end result, and makes it searchable.

2. Many folks have junk filters on their newsgroup addresses.

--
Danilo Celic
| Extending Knowledge Daily : http://CommunityMX.com/
| Adobe Community Expert

Report · Sep 18, 2007

Thanks Danilo,

That's what I needed - but for some reason I am always one record short - I
know from testing with an example recordset there should be 6 records but
response.write statment only shows 5?

Regards

"danilocelic AdobeCommunityExpert" <danilo@shimmerphase.com> wrote in
message news:fcmi6u$4c4$1@forums.macromedia.com...
> MM User wrote:
>> Danilo,
>>
>> Thanks again, I tried the code you provided but it seems I only retrieve
>> 2
>> entries, if there is I entry it pops an error Subscript out of range:
>> '1'
>
> Ah, I see...I tried getting the length of the columns dimension to loop
> over not the rows dimension. Try something like this:
> <%
> Dim MyArray
> MyArray = Recordset1.GetRows()
> ' Array to store all the reviewers
> ' Using ReDim to be able to use a variable to size the Reviewers array
> Dim Reviewers: Reviewers = Array()
> Redim Reviewers(UBound(MyArray,2)-1)
>
> Dim idx
> idx=0
>
> While idx < UBound(MyArray,2)
> Response.Write(MyArray(0,idx) & MyArray(1, idx) & "<br />" )
> Reviewers(idx) = MyArray(0,idx) & MyArray(1, idx)
> idx = idx + 1
> Wend
>
> %>
>
> This works on my system with a recordset that has 76 rows in it.
>
>
>> The reason for me storing the entries is so I may it elesewhere in the
>> page
>> it appears several time i.e. <%=Reviewer1%>
>
>
> As long as you know the index of the review, you can use to output the
> first reviewer (zero based index):
> <%=Reviewers(0)%>
>
>
> PS: Please do not write offlist unless you are asked to. I say this for a
> couple of reason:
> 1. The conversation on the forum allows the threads to be viewed in it's
> entirety to get to the end result, and makes it searchable.
>
> 2. Many folks have junk filters on their newsgroup addresses.
>
>
> --
> Danilo Celic
> | Extending Knowledge Daily : http://CommunityMX.com/
> | Adobe Community Expert

Report · Sep 18, 2007

MM User wrote:
> Thanks Danilo,
>
> That's what I needed - but for some reason I am always one record short
> - I know from testing with an example recordset there should be 6
> records but response.write statment only shows 5?

Well durn, my recordset actually has 77. I'm so sorry for running you around like this giving a quick solution and not testing it adequately. Try changing the following two lines.

>> Redim Reviewers(UBound(MyArray,2)-1)
Redim Reviewers(UBound(MyArray,2))
>>
>> Dim idx
>> idx=0
>>
>> While idx < UBound(MyArray,2)
While idx <= UBound(MyArray,2)

--
Danilo Celic
| Extending Knowledge Daily : http://CommunityMX.com/
| Adobe Community Expert