Classic ASP 3.0 Create Array from a Recordset

Question

I'm trying to fix an ASP Classic app and when I try to create an array from a Recordset Object. However I Can't get it to work correctly.

This code gives me a single record (the last one), but as far as I can see it is correct:

Dim Products
Dim Products_cmd
Dim Products_numRows

Set Products_cmd = Server.CreateObject ("ADODB.Command")
Products_cmd.ActiveConnection = Conn
Products_cmd.CommandText = "SELECT prod_id, prod_description FROM dbo.products ORDER BY prod_description ASC" 
Products_cmd.Prepared = true

Set Products = Products_cmd.Execute
Products_numRows = 0

Dim arrProducts()
arrProducts = Products.GetRows()

Using this code gives me an "Subscript out of range: 'UBound'

Dim Products
Dim Products_cmd
Dim Products_numRows

Set Products_cmd = Server.CreateObject ("ADODB.Command")
Products_cmd.ActiveConnection = Conn
Products_cmd.CommandText = "SELECT prod_id, prod_description FROM dbo.products ORDER BY prod_description ASC" 
Products_cmd.Prepared = true

Set Products = Products_cmd.Execute
Products_numRows = 0
Dim arrProducts()
Dim counter

For counter = 0 to Products.RecordCount - 1
    ReDim Preserve arrProducts(counter,2)
    arrProducts(counter,0) = Products.Fields.Item("prod_id").Value
    arrProducts(counter,1) = Products.Fields.Item("prod_description").Value
    Products.MoveNext
Next
Response.Write(Str(UBound(arrProducts)))

Any ideas would be GREATLY appreciated...

Answer 1

Your are almost there, the problem is that GetRows() returns a 2 dimensional array, and you need to tell Ubound what dimension do you want.

Working code:

Dim Products
Dim Products_cmd
Dim Products_numRows

Set Products_cmd = Server.CreateObject ("ADODB.Command")
Products_cmd.ActiveConnection = Conn
Products_cmd.CommandText = "SELECT prod_id, prod_description FROM dbo.products ORDER BY prod_description ASC" 
Products_cmd.Prepared = true

Set Products = Products_cmd.Execute

Dim arrProducts
arrProducts = Products.GetRows()

dim i
response.write "<table>"
For i = 0 to ubound(arrProducts, 2)
   response.write "<tr>"
   response.write("<td>" + trim(i+1))
   response.write("<td>" + trim(arrProducts(0,i)))
   response.write("<td>" + trim(arrProducts(1,i)))
next
response.write "</table>"
%>

Answer 2

To expand on amit_g explanation:

<% OPTION EXPLICIT %>
<%

sub echo(x)
    response.write x
end sub

dim conn : set conn = server.createobject("ADODB.CONNECTION")
conn.open("Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=tinker;uid=sa;pwd=devpassword")

dim sql : sql = "select '1a' as ColA , '1b' as ColB union all select '2a' , '2b' union all select '3a' , '3b' "

dim rs : set rs = conn.execute(SQL)

dim arr : arr = rs.GetRows()

dim cols : cols = ubound(arr,1)
dim rows : rows = ubound(arr,2)

dim x , y

echo "<table border='1' style='border-collapse:collapse;'>"
echo  "<tr>"
echo   "<td>&nbsp;</td>"
for x = 0 to cols
    echo   "<th>Col " & x & "</th>"
next
echo  "</tr>"
for y = 0 to rows
    echo  "<tr>"
    echo   "<th>Row " & y & "</th>"
    for x = 0 to cols
        echo   "<td>" & arr(x,y) & "</td>"
    next
    echo  "</tr>"
next
echo "</table>"

%>

Answer 3

The first code block looks correct. Are you sure that you are reading the data in second dimension of the returned array? That is how the GetRow populates the array.

arrProducts(0, 0) => prod_id - row 1 arrProducts(1, 0) => prod_description - row 1

arrProducts(0, 1) => prod_id - row 2 arrProducts(1, 1) => prod_description - row 2

and so on. Also

Dim arrProducts()

should be

Dim arrProducts

http://www.asp101.com/samples/viewasp.asp?file=db_getrows.asp