RE：使用存储过程分页recorset [英] RE: Paging a recorset using a stored procedure
问题描述
我会让你轻松...
罪魁祸首就是你的行
设置rs = conn.Execute（" exec q_anagrafica"）
当你以这种方式打开记录集时，你*总是*得到一个只向前游标
（adForwardOnly）和一个只读记录集（adLockReadOnly）。
这意味着你可以*不*然后使用RS.AbsolutePage或其任何亲属。
所以，当你说，你希望使用
RS.open conn，exec q_anagrafica，3
来获取分页，使用静态光标。
但现在罪魁祸首是你在这里尝试使用EXEC而且访问
并没有真正优雅地处理EXEC。
但幸运的是，当从SQL查询中查看
时，Access STORED QUERY与TABLE没有什么不同。所以你可以改为
RS.open conn，SELECT * FROM q_anagrafica，3
它应该都可以。
最后，有几个技巧。你可以用来帮助表现：
<％
CONST PAGESIZE = 10''或者其他什么
connStr =" Provider = Microsoft.Jet.OLEDB.4.0;数据来源= &安培; _ $ / $
Server.MapPath（" test.mdb"）
设置conn = Server.CreateObject（" ADODB.Connection"）
conn.Open connStr
设置rs = Server.CreateObject（" ADODB.Recordset"）
rs.MaxRecords = PAGESIZE
rs.CursorLocation = 3''客户端游标
RS.open conn，SELECT * FROM q_anagrafica，3
rs .PageSize = PAGESIZE
rs.AbsolutePage = pag
对于rnum = 1到PAGESIZE
如果RS.EOF然后退出
''不要把问题放在回应的参数上。写！
''它实际上只是轻微的性能打击
response.write rs（" id"）& " - &安培; rs（nome）& "< HR>" &安培; vbNewLine
rs.Movenext
下一页
....
％>
非常感谢...我现在就实施这个解决方案！
你能推荐吗？关于这个主题的好书（我的意思是ASP - 我知道
ASP.net更好，但目前...... - 和数据库）？
再见
Paolo
Old Pedant写道：
我会轻松搞定你...
罪魁祸首是你的行
设置rs = conn.Execute（" exec q_anagrafica"）
当你以这种方式打开记录集时，你*总是*得到一个只向前游标
（adForwardOnly）和一个只读记录集（adLockReadOnly）。
这意味着你可以*不*然后使用RS.AbsolutePage或其任何亲属。
所以，正如你所说，你期望使用
RS.open conn，exec q_anagrafica，3
使用静态光标进行分页。
但现在罪魁祸首是你在这里尝试使用EXEC和Access
不优雅地处理EXEC。
但幸运的是，当从SQL查询中查看
时，Access STORED QUERY与TABLE没什么区别。所以你可以改为
RS.open conn，SELECT * FROM q_anagrafica，3
它应该都可以。
最后，有几个技巧。你可以用来帮助表现：
<％
CONST PAGESIZE = 10''或者其他什么
connStr =" Provider = Microsoft.Jet.OLEDB.4.0;数据来源= &安培; _ $ / $
Server.MapPath（" test.mdb"）
设置conn = Server.CreateObject（" ADODB.Connection"）
conn.Open connStr
设置rs = Server.CreateObject（" ADODB.Recordset"）
rs.MaxRecords = PAGESIZE
rs.CursorLocation = 3''客户端游标
RS.open conn，SELECT * FROM q_anagrafica，3
rs .PageSize = PAGESIZE
rs.AbsolutePage = pag
对于rnum = 1到PAGESIZE
如果RS.EOF然后退出
''不要把问题放在回应的参数上。写！
''它实际上只是轻微的性能打击
response.write rs（" id"）& " - &安培; rs（nome）& "< HR>" &安培; vbNewLine
rs.Movenext
下一页
...
％>
对不起，只是最后一个问题：
如果我必须将参数传递给查询怎么办？
我的意思是q_anagrafica 2008之类的东西。只有2008年的员工？
如何更改
RS.open conn，SELECT * FROM q_anagrafica，3
脚本？
谢谢
Old Pedant写道：
我会让你轻松...
罪魁祸首就是你的行
设置rs = conn.Execute （exec q_anagrafica）
当你以这种方式打开记录集时，你*总是*得到一个只向前游标
（adForwardOnly）和一个只读记录集（adLockReadOnly）。
这意味着您可以*不*然后使用RS.AbsolutePage或其任何亲属。
所以，正如你所说，你希望使用
RS.open conn，exec q_anagrafica，3
来获取分页，使用静态游标。
但现在罪魁祸首就是你在这里尝试使用EXEC和Access
并没有真正优雅地处理EXEC。
但幸运的是，当从SQL查询中查看
时，Access STORED QUERY与TABLE没什么区别。所以你可以改为
RS.open conn，SELECT * FROM q_anagrafica，3
它应该都可以。
最后，有几个技巧。你可以用来帮助表现：
<％
CONST PAGESIZE = 10''或者其他什么
connStr =" Provider = Microsoft.Jet.OLEDB.4.0;数据来源= &安培; _ $ / $
Server.MapPath（" test.mdb"）
设置conn = Server.CreateObject（" ADODB.Connection"）
conn.Open connStr
设置rs = Server.CreateObject（" ADODB.Recordset"）
rs.MaxRecords = PAGESIZE
rs.CursorLocation = 3''客户端游标
RS.open conn，SELECT * FROM q_anagrafica，3
rs .PageSize = PAGESIZE
rs.AbsolutePage = pag
对于rnum = 1到PAGESIZE
如果RS.EOF然后退出
''不要把问题放在回应的参数上。写！
''它实际上只是轻微的性能打击
response.write rs（" id"）& " - &安培; rs（nome）& "< HR>" &安培; vbNewLine
rs.Movenext
下一页
...
％>
我会这样做：
set rs = createobject（" adodb.recordset"）
rs.cursortype = 3
conn.q_anagrafica 2008，rs
相信我，这很有效。您还可以使用此技术执行不返回记录的操作
查询，只留下记录集参数：
conn.q_action 2008
Paolo Galli写道：
对不起，只是最后一个问题：
什么关于我是否必须将参数传递给查询？
我的意思是q_anagrafica 2008只有2008
员工？
如何更改
RS.open conn，SELECT * FROM q_anagrafica" ，3
脚本？
谢谢
Old Pedant写道：
< blockquote class =post_quotes>
>我会让你轻松...
罪魁祸首是你的行
设置rs = conn.Execute（" ; exec q_anagrafica"）
当你以这种方式打开一个记录集时，你*总是*得到一个只向前游标（adForwardOnly）和一个只读记录集（adLockReadOnly）。 />
这意味着你可以*不*然后使用RS.AbsolutePage或其任何亲属。
所以，正如你所说，你期望使用
RS.open conn ，exec q_anagrafica，3
使用静态光标进行分页。
但现在的罪魁祸首就是你在这里尝试使用EXEC并且
访问没有优雅地处理EXEC。
但幸运的是，Access STORED QUERY也没有什么不同。从SQL查询中查看时的表格。相反，你可以做一下RS.open conn，SELECT * FROM q_anagrafica，3
它应该都可以工作。
最后，几个 ;花样"你可以用它来帮助提高性能：
<％
CONST PAGESIZE = 10''或者其他什么
connStr =" Provider = Microsoft.Jet.OLEDB。 4.0;数据来源= &安培; _
Server.MapPath（" test.mdb"）
设置conn = Server.CreateObject（" ADODB.Connection"）
conn.Open connStr
设置rs = Server.CreateObject（" ADODB.Recordset"）
rs.MaxRecords = PAGESIZE
rs.CursorLocation = 3''客户端游标
RS.open conn， SELECT * FROM q_anagrafica，3
rs.PageSize = PAGESIZE
rs.AbsolutePage = pag
对于rnum = 1到PAGESIZE
如果RS.EOF然后退出对于
''不要把问题放在响应的参数上。写！
''它实际上是一个小的性能命中
response.write rs（ id）& " - &安培; rs（nome）& "< HR>" &
vbNewLine rs.Movenext
下一页
...
％>
-
Microsoft MVP - ASP / ASP.NET
请回复新闻组。这个电子邮件帐户是我的垃圾邮件陷阱所以我
不经常检查它。如果您必须离线回复，请删除
无垃圾邮件
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
....
%>
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
...
%>
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
...
%>
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"
这篇关于RE：使用存储过程分页recorset的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
