Going from iterating record to GetRows() - Is it possible to rs("id", counter)?

Question

I am getting a huge amount of data from my database which I before iterated through in a recordset like this:

sql = "select * from table"
set rs = conn.execute(sql)

if not rs.eof then
  do until rs.eof
    id = rs("id")
    fullname = rs("fullname")

    response.write("<a href='/" & id & "'>Hi " & fullname & ".<br />")
  rs.movenext
loop

Now I am using a static recordset using GetRows() like this:

sql = "select * from table"
set rssql = conn.execute(sql)

if not rssql.eof then
  rs = rssql.getrows()
end if

rssql.close

if isarray(rs) then
  for counter = lbound(rs) to ubound(rs)
    id = rs(0, counter)
    fullname = rs(1, counter)

    response.write("<a href='/" & id & "'>Hi " & fullname & ".<br />")
  next
end if

Is it really not possible to do something like rs("id", counter) instead of using static numbers? I have a dynamic amount of coloumns (created by the system) in my table and it is very dynamic which of them I need. The number of coloumns I need in each rows are specificed by another coloumn in the row.

score 4 · Accepted Answer · answered Jan 20 '12 at 14:32

4

GetRows returns a vanilla array & as such is only addressable by a numeric index.

If you feel you need to dispose of the connection as soon as possible for some reason, you can fully populate the recordset and disconnect is as describe here.

(You can always use the initial recordset to populate another array or collection with the column names via the .Fields collection)

answered Jan 20 '12 at 14:32

Alex K.

171,639
30
264
288

I created another array with the names using .fields and afterwards a function to find the arraynumber.. thanks for the ideas! :) – MicBehrens Jan 20 '12 at 15:24
A [`Scripting.Dictionary`](http://msdn.microsoft.com/en-us/library/x4k5wbx4%28VS.84%29.aspx) where the key is the column name and the value is the array index might be easier to use. – Cheran Shunmugavel Jan 22 '12 at 07:05
I used an array with the array index being the ID and the value the name, thereafter I created a very simple function to go through it. It worked perfectly and the whole thing boosted my application from being 10 minutes to load to 10 seconds. So I'm pretty happy ;) – MicBehrens Jan 22 '12 at 15:34

score 1 · Answer 2 · answered Jan 20 '12 at 14:36

1

rs.Fields(counter).Name will give you access to column names. Unfortunately getrows() does not create an associative array, so you cannot look up data by column name.

answered Jan 20 '12 at 14:36

Diodeus - James MacFarlane

112,730
33
157
176

Going from iterating record to GetRows() - Is it possible to rs("id", counter)?

2 Answers2