Returning more than 1000 rows in classic asp adodb.recordset

Question

My code in asp classic, doing a mssql database query:

        rs.pagesize = 1000  ' this should enable paging
        rs.maxrecords = 0   ' 0 = unlimited maxrecords      

        response.write "hello world 1<br>"
        rs.open strSql, conn 

        response.write "hello world 2<br>"

My output when there are fewer than 1000 rows returned is good. More than 1000 rows and I don't get the "hello world 2".

I thought that setting pagesize sets up paging and thus allows all rows to be returned regardless of how many rows there are. Without setting pagesize, paging is not enable and the limit is 1000 rows. However my page is acting as if pagesize is not working at all.

Please advise.

Answer 1

Try changing your rs.open line to:

rs.Open strSQL, Conn, 3, 1, &H0001

Here's the breakdown of the function call and parameters: recordsetobject.Open Source, ActiveConnection, CursorType, LockType, Options

3 - adOpenStatic

1 - adLockReadOnly

&H0001 - adCmdText

I pull this from some old code of mine. I don't remember why this combination of parameters is necessary but it's what is necessary to implement paging.

Unlimited records sounds great but I would set a limit even if it's quite hi.

If you are not getting the "Hello World 2" output, is there an error? That would be helpful as well.