Maximum open cursor exceeded

[discusmania]

hi guys....

When i try to loop through and array to display the records and list box item, it only manage to list down until 48 records before showing this error:

Microsoft OLE DB Provider for Oracle
error '80004005'
ORA-01000: maximum open cursors exceeded


while the actual records are around 200. Below is the function to put the recordset in and array:


dim aResp()
Function disp_resp()
set rsresp=Server.createobject("ADODB.Recordset&quot
strresp="select badge from user_access where cop ='"&request("cop&quot&"' and lvl>=2 order by badge"
rsresp.open strresp,rs2,1,2
i=0
redim aResp(0)
while not rsresp.eof
redim preserve aResp(ubound(aResp)+1)
aResp(i)=rsresp("badge&quot
i=i+1
rsresp.movenext
wend

rsresp.Close
set rsresp=nothing
End Function

and this is where i call the function:


<SELECT name=&quot;cb_resp&quot; size=5 id=&quot;cb_resp&quot; style=&quot;width=200;font-size=10&quot; multiple>
<%disp_resp
for i=0 to ubound(aResp)-1
respname =chk_name(aResp(i))
%>
<option value=&quot;<%=aResp(i)%>&quot;><%=aResp(i)%>(<%=respname%>)
<%next%>
</SELECT>


anyone can help??. Please, it's urgent.

Thanks
Ron

 

[link9]

I think it has to do with the whole redim'ing on every iteration of the loop. You really should try to avoid that, as it is VERY costly (and apparently will return errors after a while).

Try doing this:

dim someArray()
dim recordCount
recordCount = rs.recordCount
redim someArray(recordCount)
while ....


That way, you only redim one time, and you get the desired results.

hope that helps!
Paul Prewett

 

[lobstah]

if all you need to do is store your recordset in an array, you can do it using the GetRows property also:

Dim aResp

set rsresp=Server.createobject(&quot;ADODB.Recordset&quot
strresp=&quot;select badge from user_access where cop ='&quot;&request(&quot;cop&quot&&quot;' and lvl>=2 order by badge&quot;
rsresp.Open strresp, rs2, 0, 1
aResp = rsresp.GetRows

It creates a 2-dimensional array, with the first dimension being the field offset, and the second dimension being the record offset, ie:

badge from first record would be aResp(0,0)
from second record aResp(0,1)
from third record aResp(0,2)
etc.

If you selected multiple fields, such as badge, name, location, then:

1stbadge = aResp(0,0)
1stname = aResp(1,0)
1stloc = aResp(2,0)
2ndbadge = aResp(0,1)
2ndname = aResp(1,1)
2ndloc = aResp(2,1)
3rdbadge = aResp(0,2)
3rdname = aResp(1,2)
3rdloc = aResp(2,2)

and so on and so on....