Dynamic array declaration 1

[camillaj]

Im very new to VB and I'm simply trying to declare a
dynamic array. That is, I'm retrieving data from DB with
set rs = cnn.execute(query).

I want to go through every recordset and add the content to
an array. Is there a way to get the size of rs?

If not I tried to declare a dynamic array as:
dim Messages() as String

but here I get 'expected end of statement' error. Why?
How can I add messages to my array?

Thanks for help

 

[ICISYD]

Before you can use Messages you have to ReDim it. Eg...

Dim aMyArray() As String 'The array
Dim iNoItems As Integer 'The number of items

Sub AddToArray(byVal sToAdd As String)
ReDim Preserve aMyArray(iNoItems)
aMyArray(iNoItems) = sToAdd
iNoItems = iNoItems + 1
End Sub

Basically this code uses a dynamic array and everytime you call it it adds another string onto the end of the array.

 

[camillaj]

Thanks for that, but I still get the error saying
'end of statement expected'. I feel this has something to do with the fact that I have this code in a asp page as
<%

Dim aMyArray() As String 'The array
Dim iNoItems As Integer 'The number of items

...
%>

I know this is a Vb forum, but does anyone know why I get this? The declarations seem to be right to. This is the first the page does when it loads.

Thaannks..

 

[LazyMe]

Isn't asp all variants?

Can you actually dim something as string?
I think that's the problem......

Furthermore:
For the sake of performance, do not use dynamic allocation like that. Reallocating the array will cost you a lot of overhead. Rather do something like this (done by heart, so probably full of mistakes, but it should give you an idea):

dim astrArray() as variant '(don't know if as variant should be there...)

redim astrArray(rs.Recordcount-1) 'you now have enough space for your data...


Greetings,
Rick

 

[CCLINT]

Try:
Dim astrArray
set rs = cnn.execute(query)

If Not rs.EOF Then astrArray = rs.GetRows()

This will give you a two dimensional (zero based) array:

astrArray(Field, Record)

So if you have 20 fields and 500 records then the 10th field value for the 155th record is:

astrArray(9, 154)

 

[CCLINT]

BTW, you could use the GetString method to returen a one dimensional array, with all fields delimited in one array element, and then use the Split() method to extract data for each record:

Dim RecArray
Dim ColArray
set rs = cnn.execute(query)
    If Not rs.EOF Then
        'Array with all records
        RecArray = Split(rs.GetString(adClipString, ColumnDelimeter:=",", rowdelimeter:=vbTab), vbTab)
  
        'First Record
        Debug.Print RecArray(0)

        'Array with All Field values for First Record
        ColArray = Split(RecArray(0), ",")

        'Field value for first field
        Debug.Print ColArray(0)
    End If