ADODB strange behavior

Question

Recently I had a very strange problem. The application is written in classic ASP, but I guess it is the same case for every connection that uses ADO/OLEDB.

Those are connection parameters.

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

In short this code:

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

Doesn't work if bigQuery returns more than certain number of rows (in my case ~20). But if I use one more connection for inner loop like stealthyninja suggested:

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();

Problem vanishes.

I am using Access database and IIS7 if that matters.

Does anyone has a logical explanation for this?

Answer 1

Michael Todd's comment has it. ADODB doesn't support MARS (Multiple Active Result Sets) which is what you are trying to do. The reason it seems to work with only 20 records is because that's how much it is initially transmitting to the client-side.

Standard solutions to this are

1. Retrieve the whole outer rowset into a holding structure or cache first, then process it and execute the inner queries, or

2. Use two different connections, as you have demonstrated.