RE: Paging a recorset using a stored procedure

[=?Utf-8?B?T2xkIFBlZGFudA==?=]

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

Old Pedant wrote:

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]

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

Old Pedant wrote:

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
...
%>

[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

Paolo Galli wrote:

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

Old Pedant wrote:

>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
...
%>

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[=?Utf-8?B?T2xkIFBlZGFudA==?=]

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]]

Old Pedant wrote:

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! :-)

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[=?Utf-8?B?T2xkIFBlZGFudA==?=]

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]]

Old Pedant wrote:

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 ...

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[=?Utf-8?B?T2xkIFBlZGFudA==?=]

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]]

Old Pedant wrote:

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.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows [MVP]]

Bob Barrows [MVP] wrote:

Old Pedant wrote:

>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.

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.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"