When to close the result set (Basic ODBC question)

Question

I am working on some small project for the local firm and the following code runs fine on my machine, but it produces errors on their server. Currently I don't have access to that server, and this is not a field that I know a lot about, so I have to ask you guys.

The page is written in the classic ASP (javascript for scripting). The logic goes like this:

conn.Open("myconnection");
bigQuery = "...";
rs = conn.execute(bigQuery);
while (!rs.eof) {
    ...
    smallQuery = "..."
    rssmall = conn.execute(smallQuery);
    ...
    rssmall.close();
    ...
    rs.movenext();
}
rs.close();
conn.close();

As I said this runs fine on my machine, but it returns some error (the worst thing is that I don't even know what error) on company's server if bigQuery returns more than ~20 rows. Is there something wrong with my code (this really isn't my field, but I guess it is ok to gather data in the loop like this?), or is there something wrong with their IIS server.

Thanks.

edit: More info: It 's Access database. Everything is pretty standard:

conn=Server.CreateObject("ADODB.Connection");
conn.Provider="Microsoft.Jet.OLEDB.4.0";
conn.Open("D:/db/testingDb.mdb");

Queries are bit long, so I wont post them. They are totally ordinary selects so they aren't the issue.

Answer 1

I had a legacy Classic ASP application which I inherited that was very similar (big queries with other queries running within the loop retrieving the first query's results) that ran fine until forty or more records were returned. The way I "solved" the problem was to instantiate another connection object to run the other query. So using your pseudo code, try --

conn.Open("myconnection");
conn2.Open("myconnection")

bigQuery = "...";
rs = conn.execute(bigQuery);
while (!rs.eof) {
    ...
    smallQuery = "..."
    rssmall = conn2.execute(smallQuery);
    ...
    rssmall.close();
    ...
    rs.movenext();
}
rs.close();
conn2.close();
conn.close();

Answer 2

What server are they actually running?

Most newer versions of Windows Server don't actually come with the Jet 4.0 driver for 64 bit at all so you can't use an access database with that driver if your app runs as a 64 bit app. You can try running as 32 bit which might solve the problem.

There is an alternative driver packaged as an office component which may be an option.

Try writing a simple test page that literally opens and closes the database connection like so:

<%
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open("D:/db/testingDb.mdb")

Response.Write("Database Opened OK")

conn.Close()

%>

Run this on the production server and if you see Database Opened OK then you'll know it's definitely the query rather than the database causing the issue.

If you get an error trying to open the database then you need to changed to using the newer driver or try the app in 32 bit mode

In the case that it is the query causing the issue then it may be that you'll need to use the various additional arguments to the Open() method to try using a different cursor (forward only if you only need to iterate over the results once) which will change how ADODB retrieves the data and hopefully mediate any performance bottleneck related to the query.

Edit

If you want to try debugging the code a bit more add the following at the start of the file. This causes the ASP script processor to continue even if it hits an error

On Error Resume Next

Then at intervals throughout the file where you expect an error might have happened do

If Err <> 0 Then
    Response.Write(Err.Number & " - " & Err.Description)
End If

See this article from ASP 101 for the basics of error handling in ASP and VBScript