超急，各位帮帮忙 ：asp+sql调用存储过程分页~~无法取得记录集~~
存储过程：CREATE PROCEDURE dbo.getUser
@iPageCount int OUTPUT, --总页数
@iPage int, --当前页号
@iPageSize int, --每页记录数
@sql varchar(40) output
as
set nocount on
begin
create table t (ID int IDENTITY,
bmb_id int,
bmb_mc varchar(40))
declare @iRecordCount int
insert into t
select bmb_id,bmb_mc from dbo.[bmb]
order by bmb_id
select @iRecordCount=@@rowcount --count(*) from t
IF @iRecordCount%@iPageSize=0
SET @iPageCount=CEILING(@iRecordCount/@iPageSize)
ELSE
SET @iPageCount=CEILING(@iRecordCount/@iPageSize)+1
IF @iPage > @iPageCount
SELECT @iPage = @iPageCount
DECLARE @iStart int --start record
DECLARE @iEnd int --end record
SELECT @iStart = (@iPage - 1) * @iPageSize + 1
SELECT @iEnd = @iStart + @iPageSize -1
--if @@error <> 0 return
--set @sql=select * from t where ID> @iStart and ID <@iEnd
if @@error <> 0 return
SELECT @sql=bmb_mc
from dbo.[t]
where ID > = @iStart and ID <=@iEnd
DROP TABLE t
return @iRecordCount
return
end
GO
asp页面调用存储过程：
<!--#include file= "conn.asp " -->
<!--#include file= "adovbs.inc " -->
<% DIM pagenow,pagesize,pagecount,recordcount
DIM MyComm,MyRst
pagenow = 2
'自定义函数用于验证自然数
if isnumeric(pagenow) = false then pagenow = 1
pagesize = 5
Set MyComm = Server.CreateObject( "ADODB.Command ")
with MyComm
.ActiveConnection = conn
.CommandText = "getUser "
.CommandType = 4
.Prepared = true
.Parameters.append .CreateParameter( "RETURN ",2,4)
.Parameters.append .CreateParameter( "@iPageCount ",3,2,4)
.Parameters.append .CreateParameter( "@iPage ",3,1,4,pagenow)
.Parameters.append .CreateParameter( "@iPageSize ",3,1,4,pagesize)
.Parameters.append .CreateParameter( "@sql ",200,2,40)
Set MyRst =.Execute()
end with
response.write MyComm(0)& " <br> "
response.write MyComm(1)& " <br> "
response.write MyComm(2)& " <br> "
response.write MyComm(3)& " <br> "
response.write MyComm(4)& " <br> "
'for i=1 to pagesize
response.write ubound(MyRst(2))& "213 "
'next
for each parm in mycomm.parameters
response.write parm.name& "= "&parm& " <br> "
next
response.end()
if MyRst.state = 0 then '未取到数据，MyRst关闭
recordcount = -1
else
MyRst.close
recordcount = MyComm(0)
pagecount = MyComm(1)
if cint(pagenow)> =cint(pagecount) then pagenow=pagecount
end if
Set MyComm = Nothing
if recordcount = 0 then
Response.Write "无记录 "
elseif recordcount > 0 then
MyRst.open
do while MyRst.EOF
response.write myrst(0)
loop
else
Response.Write "参数错误 "
end if %>
[解决办法]
存储过程：
CREATE PROCEDURE [dbo].[getUser]
@PageNoint,
@PageSizeint,
@RecordCount int output,
@PageCount int output
AS
/*Define private para*/
declare @BeginID int
declare @EndID int
declare @RowCount int
/*close count*/
set nocount on
/*Get @RecordCount */
select @RecordCount = count(*) from bmb
if (@RecordCount = 0)
return 0
/*Get @PageCount*/
set @PageCount = @RecordCount / @PageSize
if @PageCount * @PageSize < @RecordCount
set @PageCount = @PageCount + 1
/*Check @PageNo*/
if (@PageNo - 1) * @PageSize > @RecordCount
return (-1)
/*Get @BeginID*/
set @RowCount = (@PageNo - 1) * @PageSize + 1
/*Recount Constol*/
set rowcount @RowCount
select @BeginID = bmb_id from bmb order by bmb_id
/*Get @EndID*/
set @RowCount = @PageNo * @PageSize
/*Recount Constol*/
set rowcount @RowCount
select @EndID = bmb_id from bmb order by bmb_id
/*Open system count*/
set rowcount 0
set nocount off
select * from bmb where bmb_id between @BeginID and @EndID order by bmb_id
return(@PageCount)
GO
ASP页面调用存储过程：
<%
Dim objCmd
Dim objRs
Dim strConn
PageNo = 1
PageSize = 5
Set objConn = Server.CreateObject( "ADODB.Connection ")
objConn.Open "provider=sqloledb;data source=(local);initial catalog=MyTest;user id=sa;password=; "
Set objCmd = Server.CreateObject( "ADODB.Command ")
Set objRs = Server.CreateObject( "ADODB.Recordset ")
objCmd.ActiveConnection = objConn
objCmd.CommandText = "getUser "
objCmd.CommandType = 4
objCmd.Parameters.Append objCmd.CreateParameter( "@PageNo ", 3, 1, , CInt(PageNo))
objCmd.Parameters.Append objCmd.CreateParameter( "@PageSize ", 3, 1, , CInt(PageSize))
objCmd.Parameters.Append objCmd.CreateParameter( "@RecordCount ", 3, 4)
objCmd.Parameters.Append objCmd.CreateParameter( "@PageCount ", 3, 4)
Set objRs = objCmd.Execute()
Do While Not objRs.Eof '显示记录
Response.Write objRs(1) & " <br> "
objRs.MoveNext
Loop
objRs.Close
Set objRs = Nothing
RecordCount = objCmd.Parameters( "@RecordCount ").Value '返回总记录数
PageCount = objCmd.Parameters( "@PageCount ").Value '返回总页数
Response.Write "总记录数： " & RecordCount & " <br> "
Response.Write "总页数： " & PageCount & " <br> "
Set objCmd = Nothing
%>
[解决办法]
说明 设置 SET ROWCOUNT 选项将使大多数 Transact-SQL 语句在已受指定数目的行影响后停止处理。这包括触发器和 INSERT、UPDATE 及 DELETE 等数据修改语句。ROWCOUNT 选项对动态游标无效，但限制键集的行集和不感知游标。使用该选项时应谨慎，它主要与 SELECT 语句一起使用。
如果行数的值较小，则 SET ROWCOUNT 替代 SELECT 语句 TOP 关键字。
SET ROWCOUNT 的设置是在执行或运行时设置，而不是在分析时设置。
