Populate array from a database query

[jasonsalas]

What's the best qay to populate the elements of an Array object in VBScript for ASP 3.0 coming out of a database? I'm stuck on this one.

 

[Tarwn]

Well, if you know the number of fields than you could do this:

dim myArray(10,2) 'maximum 11 rows with 3 fields
dim ctr
ctr = 0
Do Until rs.EOF
   myArray(ctr,0) = rs("firstField")
   myArray(ctr,1) = rs("secondField")
   myArray(ctr,2) = rs("thirdField")
   ctr = ctr + 1
   rs.MoveNext
Loop

For a more dynamic solution you could do the following:

Dim myArray(1000,15) 'max 1001 records w/ 16 fields each
Dim rowCtr, fieldCtr, numFields
numFields = rs.fields.count

For fieldCtr = 0 to numFields - 1
   myArray(0,fieldCtr) = rs.fields.item(fieldCtr).name 'check syntax on this one
Next

rowCtr = 1
Do Until rs.EOF
   For fieldCtr = 0 to numFields - 1
      myArray(rowCtr,fieldCtr) = rs.fields.item(fieldCtr)
   Next
   rs.MoveNext
   rowCtr = rowCtr + 1
Loop

That was all on the fly, so I may have misworder it, but the logic should be sound.

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
For my next trick I will pull a hat out of a rabbit (if you think thats bad you should see how the pigeon feels...)

 

[jasonsalas]

Thanks for the tip. I was able to mess with it for awhile and arrive at something...

<%

Dim arrAcronyms(), objConn, objRS, counter
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot
objConn.Open &quot;dsn=DB;uid=;pwd=&quot;

sql = &quot;SELECT * FROM Table&quot;

' this script requires that you are using a dynamic cursor (a non-forward-only moving cursor so it
' can be stepped back through the recordset)
objRS.Open sql,objConn,3,3

' populate the contents of an array dynamically from the values stored in the DB
counter = 0
ReDim arrAcronyms(objRS.RecordCount-1)
Do While Not objRS.EOF
arrAcronyms(counter) = objRS(0).Value
counter = counter + 1
objRS.MoveNext
Loop
objRS.Close

%>

Thanks for your help!

 

[RaceAap]

Here an example from the ms website:

http://support.microsoft.com/default.aspx?scid=KB;en-us;189360

   'This sample code demonstrates how to pass an array of field
   'names to the GetRows Fields Option.

   Dim cn As New Connection
   Dim rs As New Recordset
   Dim vFields() As Variant
   Dim vdata as Variant

   'This example is connecting to SQL Server's Sample Pubs database.
   cn.Open "driver={SQL Server};" & _
      &quot;server=<server_name>;uid=<user_id>;pwd=<password>;database=pubs&quot;
   rs.Open &quot;select * from authors&quot;, cn
   ReDim vFields(1)
   vFields(0) = &quot;au_fname&quot;
   vFields(1) = &quot;au_lname&quot;
   vData = rs.GetRows(adGetRowsRest, , vFields)

   For i = 0 To UBound(vdata, 2) - 1
   Debug.Print vdata(0, i) & &quot; &quot; & vdata(1, i)
   Next i