How to get recordset into bidimensional array in vbscript?

Question

I want to get a table into a bidimensional array in vbscript (I'm using asp to access a mdb), anyway, I was trying to make it like this:

        'Levels'
        Dim one : one = Array()
        Dim two : two = Array()
        Dim three : three = Array()
        Dim four : four = Array()
        Dim five : five = Array()
        Dim level

        recordset.open sql, connection

        If recordset.RecordCount <> 0 Then
            For Each record In recordset
                level = record("Level")
                Select Case level
                    Case 1
                    Case 2
                    Case 3
                    Case 4
                    Case 5
                End Select
            Next
        End If

but it seems it doesn't work, also I'm not sure if the bidimensional variable declaration is the right way of doing it.

Can some one enlighten me? Thanks in advance.

score 4 · Accepted Answer · answered Aug 18 '14 at 00:40

4

Why not use the built-in GetRows() method instead. This takes your recordset and puts it into a two dimensional array. Here's the sample code from w3schools.com which illustrates this method well:

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from Customers", conn

'The first number indicates how many records to copy
'The second number indicates what recordnumber to start on
p=rs.GetRows(2,0)
rs.close
conn.close

'This example returns the value of the first
'column in the first two records
response.write(p(0,0))
response.write("<br>")
response.write(p(0,1))

'This example returns the value of the first
'three columns in the first record
response.write(p(0,0))
response.write("<br>")
response.write(p(1,0))
response.write("<br>")
response.write(p(2,0))
%>

answered Aug 18 '14 at 00:40

johna

10,540
14
47
72

I already did see this `GetRows()`, but never found a good example like this one, it seems I was distracted, thanks a lot, it's what a really need, by the way do you know how can I randomize the order of the array? Thanks very much, You're awesome!!! – Francisco Costa Aug 18 '14 at 01:14
2

It is probably not easy or efficient to randomise the order of the array. But you can randomise the results of your SQL query. If SQL server you can add `order by newid()` to get the results in random order. – johna Aug 18 '14 at 04:34
1

@FranciscoCosta wouldn't recommend w3schools.com methods to anyone (Sorry John). Couple of things to note, `If Not rs.EOF Then p = rs.GetRows()` or you may get a `Recordset is closed` error then use `IsArray(p)` to check that you have an `Array` returned before attempting to access it. – user692942 Aug 18 '14 at 08:31
1

@FranciscoCosta You may find this helpful, it gives a full example of how to use `GetRows()`. [Answer to Why should I close and destroy a recordset?](http://stackoverflow.com/a/22305896/692942). – user692942 Aug 18 '14 at 08:38
Thank you @Lankymart, obviously w3schools examples are very basic, just to help understand how it works, anyway thanks for your help, especially the `IsArray()` check. – Francisco Costa Aug 18 '14 at 11:21
@John I tried `order by newid()` it gave error I used it this way `sql = "SELECT * FROM Questions ORDER BY newid()"`, is something wrong? – Francisco Costa Aug 18 '14 at 11:28
@FranciscoCosta That will only work if your using SQL Server, `NEWID()` is a SQL Server specific function as John mentioned in [their comment](http://stackoverflow.com/questions/25354446/how-to-get-recordset-into-bidimensional-array-in-vbscript#comment39534797_25354974). Are you using SQL Server? – user692942 Aug 18 '14 at 12:50
There is a free version of SQL Server called the Express edition. What database are you using as there should be a way of randomising results for all databases. – johna Aug 18 '14 at 22:17

How to get recordset into bidimensional array in vbscript?

1 Answers1