VBScript create a multi-dimensional array and add to it?
Solution 1
(1) The best way to get an ADO resultset into a two-dimensional array is to use the .GetRows method. Then your problem just vanishes.
(2) There are two kind of arrays in VBScript. Fixed arrays are declared by specifying their UBounds:
Dim aFix(2, 3)
They can't be resized. Dynamic arrays can be changed by ReDim [Preserve]
. The best way to create such an array is
ReDim aDyn(2, 3)
if you know the starting size, or
Dim aDyn : aDyn = Array()
if you want to start with an empty one. The catch 22 is: you can use Preserve only for the last dimension.
(3) Your
Dim data2()
is an abomination - a fixed array of no size. It's a pity that the 'compiler' is too stupid to catch such a beast that VBScript can't handle properly:
>> Dim data2()
>> WScript.Echo UBound(data2)
>>
Error Number: 9
Error Description: Subscript out of range
The nastiness of the Dim a()
statement is hidden by the fact that a later ReDim
will store a proper dynamic array into that variable:
>> Dim data2() ' <-- abomination
>> ReDim data2(1,1) ' <-- overwritten by a dynamic array
>> data2(0,0) = 0
>> ReDim Preserve data2(1,5) ' last dimension increased; 'old' data preserved
>> data2(1,5) = 1
>> WScript.Echo data2(0,0), data2(1,5)
>>
0 1
Update wrt jmbpiano's comment:
(1) I gave evidence that you can't get the UBound for a variable dimmed with (), so I stick to my claim that such beasts are abominations. Just look at the question (or this one) to see that using the () will give you trouble.
(2) I said that you should use ReDim a(KnownUbound)
to 'declare' a dynamic array with known size, but I didn't give evidence for the 'Option Explicit'-compatibility of this idiom. So :
Option Explicit
ReDim a(4711)
ReDim b(4,7,1,1)
a(0) = "qed"
b(0,0,0,0) = "qed"
WScript.Echo b(0,0,0,0)
output:
cscript 19888987.vbs
qed
Solution 2
This may be off-topic, but after seeing your exact code, why aren't you using the built-in ADO function: GetRows()
?
sub grabdata
SQL_query = "SELECT * FROM MSAccess_table"
Set rsData = conn.Execute(SQL_query)
If Not rsData.EOF Then aData = rsData.GetRows()
end sub
This returns all your column # as the first index, and the rows (data) in the second.
So to loop through it, you would:
If IsArray(aData) Then
For x = lBound(aData,2) to uBound(aData,2) 'loops through the rows
Col1 = aData(0,x)
Col2 = aData(1,x)
Col3 = aData(2,x)
Response.Write "Row #" & x+1 & "<br>"
Response.Write "This is the data in Column1: " & Col1 & "<br>"
Response.Write "This is the data in Column2: " & Col2 & "<br>"
Response.Write "This is the data in Column3: " & Col3 & "<br>"
Next
End If
*NOTE: Rows (and columns) start on 0 in the array by default.
Quaking-Mess
Updated on September 16, 2020Comments
-
Quaking-Mess over 3 years
This is a doozy for me haha, I've pretty much checked nearly every page on Google Search and I still don't quiet understand how to do it.
I want to create a multi dimensional array in VB Script called data2. Trying the examples that I've seen but I'm getting a "Subscript out of range" error
Dim data2() sub grabdata SQL_query = "SELECT * FROM MSAccess_table" Set rsData = conn.Execute(SQL_query) Do Until rsData.EOF = True ReDim Preserve data2(UBound(data2) + 1) data2(UBound(data2)) = Array(rsData("id"),rsData("column_1"),rsData("column_2"),rsData("column_3"),rsData("column_4")) rsData.moveNext Loop end sub
Basically I'm trying to learn how to make a multi-dimensional array in VB script and add to it with a loop. What are some basic examples that can work in my case?
-
AutomatedChaos over 10 years"The catch 22 is: you can use Preserve only for the last dimension"; you can overcome that with a dynamic array of dynamic arrays, but is more difficult to keep it clean in your code and to extract the data from:
a = array(array(1,2,3),array(4,5),array(6,7,8,9))
now you can get dataa(x)(y)
style:a(2)(1) -> displays 7
-
Quaking-Mess over 10 yearsThe array structure is back to front to that of Javascript. I need data2 to run in a javascript function afterwards. Why does GetRows organize data in this way? data2(0,1) where 0 is the element and 1 is the index
-
Ekkehard.Horner over 10 years@Quaking-Mess - there is a toArray method (msdn.microsoft.com/en-us/library/8943ay3x%28v=vs.90%29.aspx), but it looses the structure. You shouldn't try to mix languages.
-
Quaking-Mess over 10 yearsI think I will try AccessDB a JavaScript Interface Library for Microsoft Access instead.
-
jmbpiano about 9 years"Dim data2()" is just a variable declaration indicating the intended type of the variable (a dynamic array rather than a variant). The only difference being you initialize the resultant variable with a ReDim instead of an assignment.The Dim/ReDim syntax may be slightly awkward (why MS didn't include a keyword to declare a dynamic array with explicit initial dimensions in the a single statement baffles me), but it's hardly an "abomination" and is required if you're using Option Explicit (generally a Good Idea).
-
jmbpiano about 9 years@Ekkehard - I stand corrected. I had tested similar code before posting my comment and had gotten an error when omitting the Dim, but upon testing your code, I see it does indeed work. I must have introduced some other error while doing my test.