Dynamic array error

[emozley]

I am trying to write a series of values into an array. If I set up the array by declaring a number of 'slots' the script works fine but if I try to use a dynamic array I get the following error:

Microsoft VBScript runtime error '800a0009'

Subscript out of range

/assets2/getlist2.asp, line 27

My code is as follows:

<%

Dim HostsArray()
counter=0

Const ADS_SCOPE_SUBTREE = 2
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

Set objDomain = getObject("LDAP://rootDse")
Domain = objDomain.Get("defaultNamingContext")
LDPATH = Chr(39) & "LDAP://" & Domain & Chr(39)

Set objCOmmand.ActiveConnection = objConnection
objCommand.CommandText = _
"Select Name, Location from " & LDPATH _
& "where objectClass='computer'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 30
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.Properties("Cache Results") = False
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
HostsArray(counter)= objRecordSet.Fields("Name").Value
objRecordSet.MoveNext
counter=counter+1
Loop

%>

Any ideas?

Thanks very much

Ed

 

[tsuji]

[tt]Do Until objRecordSet.EOF
[red]redim preserve HostsArray(counter)[/red]
HostsArray(counter)= objRecordSet.Fields("Name").Value
[/tt]
There exists an getrows method which would be more efficient. But basically, this is a valid approach.

 

[emozley]

Excellent thanks!

 

[theniteowl]

emozley, look up the getrows method that tsuji mentioned.
It is a FAR more efficient means of getting your data.

Your loop cycles through every record in the recordset adding the data one by one to the array, redimensioning the array on each pass.
The getrows method grabs the recordset and dumps it directly into the array in one swipe without having to loop through each record or redimension the array. So it is much faster not only in your ASP code but in your database.
If you are pulling more than 2,000 records you can page the results for better performance.

Your code is essentially what I have done many many times for different purposes and if it is just a small amount of data it really is no problem or major performance hit to do it that way but if you want to learn the more sophisticated and efficient methods for future use this is an excellent thing to get experience with. Also look at getstring.



It's hard to think outside the box when I'm trapped in a cubicle.

 

[Genimuse]

Agreed absolutely on the GetRows. You'd replace this:

objRecordSet.MoveFirst
Do Until objRecordSet.EOF
  redim preserve HostsArray(counter)
  HostsArray(counter)= objRecordSet.Fields("Name").Value
  objRecordSet.MoveNext
  counter=counter+1
Loop

with this:

HostsArray = objRecordSet.GetRows()

Poof, like magic, and way, way faster. Way.