Paging a recorset using a stored procedure

[Paolo Galli]

hi everybody
I'm trying to get this code working but I'm having problems...

I'm using Access and a query on it
What I want is paging the recordset I get
I can get the rs filled but I don't know how to page it... the only
example I have works but only using a rs.AbsolutePage method that
doesn't work with my code...

here is the snippet

'getting data
connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("test.mdb")

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open connStr

Set rs = Server.CreateObject("ADODB.Recordset")

Set rs = conn.Execute ("exec q_anagrafica")

rs.PageSize = number
rs.AbsolutePage = pag

While NOT rs.EOF And counter < number

response.write ( rs("id") & " - " & rs("nome") )
response.write ( "<hr>" )

rs.Movenext
counter = counter + 1
WEnd

the error I'm getting is the following

Current Recordset does not support bookmarks. This may be a limitation
of the provider or of the selected cursortype

if instead of the previous rc conn.execute I use the following

rs.open ("SELECT * from t_anagrafica"), conn, 3

the pagination works correctly.
How can I correct the code in order to have it working with my query?

thanks a lot
Paolo

 

[Bob Barrows [MVP]]

hi everybody
I'm trying to get this code working but I'm having problems...

I'm using Access and a query on it
What I want is paging the recordset I get
I can get the rs filled but I don't know how to page it... the only
example I have works but only using a rs.AbsolutePage method that
doesn't work with my code...

You should probably read here to see some more efficient techniques
(this article does contain the answer to your problem even if you are
not interested in using a different technique):
http://www.aspfaq.com/show.asp?id=2120

 

[Paolo Galli]

thanks Bob but I can't understand it...

it uses a stored procedure on the DB to paginate... if I understood right

I'm using Access and just need to paginate the recordset I got using a
query and not a SELECT

Paolo

 

[Bob Barrows [MVP]]

You needed to be looking at the first few recordset examples. Ignore the
ones that use stored procedures

 

[Old Pedant]

I'll make it easy on you...

The culprit is your line
Set rs = conn.Execute ("exec q_anagrafica")

When you open a recordset that way, you *always* get a forward-only cursor
(adForwardOnly) and a read-only recordset (adLockReadOnly).

Which means you can *NOT* then use RS.AbsolutePage or any of its kin.

So, as you said, you expected to use
RS.open conn,"exec q_anagrafica", 3
to get pagination, using the static cursor.

But now the culprit is that fact you are trying to use EXEC here and Access
doesn't really handle EXEC gracefully.

But luckily, an Access STORED QUERY is no different than a TABLE, when
viewed from a SQL query. So you can, instead, just do
RS.open conn,"SELECT * FROM q_anagrafica", 3
and it should all work.

Finally, a couple of "tricks" you can use to help performance:

<%
CONST PAGESIZE = 10 ' or whatever

connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("test.mdb")
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connStr

Set rs = Server.CreateObject("ADODB.Recordset")
rs.MaxRecords = PAGESIZE
rs.CursorLocation = 3 ' client side cursor

RS.open conn,"SELECT * FROM q_anagrafica", 3

rs.PageSize = PAGESIZE
rs.AbsolutePage = pag

For rnum = 1 To PAGESIZE
If RS.EOF Then Exit For
' don't put parens around the argument to response.Write!
' it's actually a minor performance hit
response.write rs("id") & " - " & rs("nome") & "<hr>" & vbNewLine
rs.Movenext
Next
....
%>

 

[Paolo Galli]

Thanks a lot... I'll implement this solution right now!

Can you recommend a good book on this themes (I mean ASP - I know
ASP.net is better but for the moment... - and database)?

bye
Paolo

 

[Paolo Galli]

Sorry, just the last question:

what about if I have to pass a parameter to the query?
I mean something like "q_anagrafica 2008" to have just the 2008 employees?

How change the

RS.open conn,"SELECT * FROM q_anagrafica", 3

script?

thanks

 

[Bob Barrows [MVP]]

I would do it like this:

set rs=createobject("adodb.recordset")
rs.cursortype=3
conn.q_anagrafica 2008,rs

Trust me, this works. You can also use this technique to execute an action
query that does not return records, just leave off the recordset argument:

conn.q_action 2008

 

[Old Pedant]

HUH!!! I never even thought of using an Access stored query like this!

I actually had to go TRY it.

In general, Access queries are too useless to bother with (can't do real
coding...just use conditionals) so I've ignored them except when a single
query gets too complex for the poor little baby. But now you've made me
rethink my use of them.

Fun stuff!

Thanks.

 

[Bob Barrows [MVP]]

HUH!!! I never even thought of using an Access stored query like
this!

I actually had to go TRY it.

In general, Access queries are too useless to bother with (can't do
real coding...just use conditionals) so I've ignored them except when
a single query gets too complex for the poor little baby. But now
you've made me rethink my use of them.

Fun stuff!

Thanks.

And the same technique works with SQL Server stored procedures!

 

[Old Pedant]

Oh, sure...and with MySQL.

I just meant I hadn't thought of using it with Access queries because they
aren't "true" stored procedures.

But I guess the presence of a parameter is enough to trigger ADO to doing
the right thing.

I even tried a query where I did
SELECT ... FROM table WHERE id = @id OR parentid = @id
and I only needed to pass a single value. Which of course I would have
*expected* with SQL Server. Just didn't think poor little Access would do it
right. But of course it's not REALLY Access doing it; it's the JET engine.
So I guess that's understandable. Fun.

 

[Bob Barrows [MVP]]

Oh, sure...and with MySQL.

I just meant I hadn't thought of using it with Access queries because
they aren't "true" stored procedures.

Well ... if you look at them with ADOX, guess what collection they are in?

Actually, a saved query that returns records but does not accept parameters
is contained in the Views collection.

And there are advantages to using them besides the ease of use: they are
parsed and compiled so Jet doesn't have to come up with an execution plan
every time they are run ...

 

[Old Pedant]

Well ... if you look at them with ADOX, guess what collection they are in?

LOL! Never thought of looking at them ADOX. Not surprising, given that I
didn't use them for the other reasons mentioned.

And there are advantages to using them besides the ease of use: they are
parsed and compiled so Jet doesn't have to come up with an execution plan
every time they are run ...

And not to mention that you can use queries and subqueries and thus build up
SQL that will actually run in Access/JET but that can't run if you try to do
it all as a single ad hoc query. Can't tell you how many times I've gotten
"expression too complex" and fixed it within minutes by using stored queries.

So... You've just given me more reason to use them.

Of course, now that I learn all these tricks, my days of using Access with
ASP are numbered. The only use I make of that combination nowadays is on a
couple of ".org" sites I helped set up for some non-profits. And even those,
if I ever get around to recoding them, I'll likely move to SQL Server, just
because. I moved them all to use GoDaddy's cheapest hosting plan, and even
at $4 a month they give you one 200MB SQL Server DB, which is overkill for
all these little sites.

Anyway...too bad I didn't know some of this stuff 7 and 8 years ago. (Of
course, back then I don't think this stuff worked, did it? When did the
ability to call sp's by name from connection object arrive on the scene? ADO
2.5? 2.6? Hmmm...could n't find it. Found docs for 2.1 and clearly wasn't
there at that time.)

 

[Bob Barrows [MVP]]

Anyway...too bad I didn't know some of this stuff 7 and 8 years ago.
(Of course, back then I don't think this stuff worked, did it? When
did the ability to call sp's by name from connection object arrive on
the scene? ADO
2.5? 2.6? Hmmm...could n't find it. Found docs for 2.1 and clearly
wasn't there at that time.)

I believe 2.5 introduced it. And, my memory is a little foggy on this, but I
believe it did not work with Jet until the 4.0 provider was released, since
that was the first provider to expose the saved queries as procedures.

 

[Bob Barrows [MVP]]

I believe 2.5 introduced it. And, my memory is a little foggy on
this, but I believe it did not work with Jet until the 4.0 provider
was released, since that was the first provider to expose the saved
queries as procedures.

Hmm, Jet 4.0 was released with MDAC 2.1, so i would be surprised it the
procedure-as-connection-method techniques was not possible with 2.1. I don't
have the docs for that version, but you would find it if it's there in the
overview of the connection object.