ADO Recordsets in the Global.asa file

[Yazster]

Hi, I've been racking my brain for the past week trying to figure out what to do, and I'm hoping someone can help point me in the right direction...

I have this fairly large-scaled reporting application that has existed for a little while designed almost entirely in vbscript (client-side). The application itself works great, but in the past little while we've been having problems with new users logging in not having the necessary requirements, namely MDAC.

Our technical team has decided to go ASP. So, we've begun the transition. The problem I'm having is with the Recordsets. The application often returns very large amounts of data, and we had a page that displayed 20 records at a time, using ADO paging, with the standard next/previous features.

One of the programmers has put together a session scope recordset created in the GLOBAL.ASA file.
In Page A, the user selects the paramaters in a standard form
In Page B, the form data is analyzed and the recordset is populated accordingly.
In Page C, the populated recordset is displayed, 20 records at a time.

All of this works fine, but I've been hearing that it's bad practice to use session scope recordsets in this manner. However the alternative of populating the recordset in the ASP page and then displaying 20 records on the same page is not an option, as we can't afford to re-query the database everytime, since so many records can be returned.

Is there anything wrong with declaring a recordset in the GLOBAL.ASA file and using it in this manner? If so, what alternatives are there?

What actually concerns me here is that we've seen in some cases (can't yet identify how), that even after closing the recordset, re-opening it and requerying, we've had problems in an ASP page we have for exporting the contents to EXCEL. In some instances, the data exported actually existed in the previous query, not the new one, even though displaying the data in a web page shows the new data. This one has me pulling my hair out, because on some machines it works fine, on others it just won't refresh!

Thanks in advance!
Yazster

 

[vbkris]

>>Is there anything wrong with declaring a recordset in the GLOBAL.ASA file and using it in this manner?

No problem at all (except for the space taken up in the memory and hard disk of the srever for the session)...

Known is handfull, Unknown is worldfull

 

[Yazster]

Thanks for the feedback vbkris.

So if I do use a session recordset, do you see any reason why when trying to export the contents to excel that the data would be from a previous recordset? Here's the code:

<%
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment;filename=SystemExport.xls"
tmpstring= "<table border='1' bordercolor='#000000' cellpadding='3' cellspacing='0' width='100%'><tr>"
rs.movefirst
for each f in rs.fields
tmpstring=tmpstring & "<th>" & f.name & "</th>"
next
tmpstring=tmpstring & "</TR>"
while not rs.eof
tmpstring=tmpstring & "<TR>"
for each f in rs.fields
tmpstring=tmpstring & "<TD>" & f & "</TD>"
next
tmpstring=tmpstring & "</TR>"
rs.movenext
wend
tmpstring=tmpstring & "</TABLE>"
response.write tmpString
%>

Like I mentioned previously, this is a problem for some users, not others. What's weird is that the site that navigates to this export page also displays the current recordset to the screen, and the data is good. When it gets to the export page, using the EXACT SAME RECORDSET, the data is from a previous query.

Any thoughts?

 

[baddos]

You should never store a recordset in a session or application variable. Any reporting recordset programming should be done on the ASP page, not in the global.asa. The global.asa can have a recordset or DB query run from it, but shouldn't then store that recordset to any global variable.

 

[mike2312]

If you would like to NOT use the global/session recordset consider using the getrows() method of a recordset. It can create some fun 2D arrays but you wont have to requery the DB with each page change.