在线等一个ASP与存储过程分页问题
存储过程代码如下：
CREATE PROCEDURE haocai_product_searchpage
(
@strTblNameVARCHAR(8000),--表名或视图表
@strFieldsVARCHAR(8000)= '* ',--欲选择字段列表
@strOrdFieldVARCHAR(100),--排序字段
@strOrdField2VARCHAR(100),--排序字段 2
@strKeyFieldVARCHAR(100),--主键
@intPageNoINT= 0,--页号,从0开始
@intPageSizeINT= 10,--页尺寸
@strWhereVARCHAR(4000)= ' ',--条件
@bitOrdTypeBIT= 1--排序,1=降序,0=升序
)
AS
SET NOCOUNT ON
DECLARE @strSQL VARCHAR(8000)
IF @intPageNo = 0
BEGIN
SET @strSQL= 'SELECT TOP ' + CAST(@intPageSize AS VARCHAR) + ' ' + @strFields + ' FROM ' + @strTblName
IF @strWhere != ' '
SET @strSQL= @strSQL + ' WHERE ' + @strWhere
IF @bitOrdType = 1
SET @strSQL= @strSQL + ' ORDER BY ' + @strOrdField + ' DESC '
ELSE
SET @strSQL= @strSQL + ' ORDER BY ' + @strOrdField + ' ASC '
END
ELSE
BEGIN
SET @strSQL= 'DECLARE @intCurPageNo int; '
SET @strSQL= @strSQL + 'DECLARE @intNextPageNo int; '
SET @strSQL= @strSQL + 'SET @intCurPageNo = ' + CAST(@intPageNo AS VARCHAR) + '* ' + CAST(@intPageSize AS VARCHAR) + '; '
SET @strSQL= @strSQL + 'SET @intNextPageNo = ' + CAST(@intPageNo+1 AS VARCHAR) + '* ' + CAST(@intPageSize AS VARCHAR) + '; '
SET @strSQL= @strSQL + 'DECLARE @strSQL VARCHAR(8000); '
IF @bitOrdType = 1
BEGIN
SET @strSQL= @strSQL + 'SET @strSQL = ' 'SELECT ' + @strFields + ' FROM (SELECT TOP 4 * FROM ' + @strTblName
IF @strWhere != ' '
SET @strSQL= @strSQL + ' WHERE ' + @strWhere
SET @strSQL= @strSQL + ' ORDER BY ' + @strOrdField + ' DESC) AS atmp WHERE ' + @strKeyField
+ ' NOT IN (SELECT TOP 2 ' + @strKeyField + ' FROM ' + @strTblName
IF @strWhere != ' '
SET @strSQL= @strSQL + ' WHERE ' + @strWhere
SET @strSQL= @strSQL + ' ORDER BY ' + @strOrdField + ' DESC) ORDER BY ' + @strOrdField2 + ' DESC ' '; '
END
ELSE
BEGIN
SET @strSQL= @strSQL + 'SET @strSQL = ' 'SELECT ' + @strFields + ' FROM (SELECT TOP ' ' + CAST(@intNextPageNo AS VARCHAR) + ' ' * FROM ' + @strTblName
IF @strWhere != ' '
SET @strSQL= @strSQL + ' WHERE ' + @strWhere
SET @strSQL= @strSQL + ' ORDER BY ' + @strOrdField + ' ASC ) AS atmp WHERE ' + @strKeyField
+ ' NOT IN (SELECT TOP ' ' + CAST(@intCurPageNo AS VARCHAR) + ' ' ' + @strKeyField + ' FROM ' + @strTblName
IF @strWhere != ' '
SET @strSQL= @strSQL + ' WHERE ' + @strWhere
SET @strSQL= @strSQL + ' ORDER BY ' + @strOrdField + ' ASC) ORDER BY ' + @strOrdField2 + ' ASC ' '; '
END
SET @strSQL=@strSQL + 'EXECUTE(@strSQL); '
END
print @strSQL
EXECUTE(@strSQL)
--SET @strSQL= 'SELECT COUNT(*) FROM ' + @strTblName
--IF @strWhere != ' '
--SET @strSQL= @strSQL + ' WHERE ' + @strWhere
--EXECUTE(@strSQL)
SET NOCOUNT OFF
GO
asp代码如下：
<%
keywords = saferequest(request.querystring( "keywords "))
Pd_No = CInt(Saferequest(Request.querystring( "Page ")))
'@strTblNameVARCHAR(100),--表名或视图表
'@strFieldsVARCHAR(4000)= '* ',--欲选择字段列表
'@strOrdFieldVARCHAR(100),--排序字段
'@strKeyFieldVARCHAR(100),--主键
'@intPageNoINT= 0,--页号,从0开始
'@intPageSizeINT= 10,--页尺寸
'@strWhereVARCHAR(4000)= ' ',--条件
'@bitOrdTypeBIT= 1--排序,1=降序,0=升序
Set oCommand= CreateObject( "ADODB.Command ")
With oCommand
.CommandText = "searchpage "
.CommandType = 4
sTblName = "dbo.Hc_List "
sFldList = "ProductChinese,Product_Brand_English,Product_name_name "
sOrdField = "dbo.Hc_List.Intime "
sOrdField2 = "Pd_Intime "
sKeyField = keywords
IpageNo = Pd_No
IpageSize = 2
strKeyField = "Code "
swhere = "dbo.Hc_brand.Chinese like '% "&Replace(sKeyField, "+ ", " ")& "% ' "
.Parameters.Append .CreateParameter( "@strTblName ", 200, 1, 4000, sTblName)
.Parameters.Append .CreateParameter( "@strFields ", 200, 1, 4000, sFldList)
.Parameters.Append .CreateParameter( "@strOrdField ", 200, 1, 100, sOrdField)
.Parameters.Append .CreateParameter( "@strOrdField2 ", 200, 1, 100, sOrdField2)
.Parameters.Append .CreateParameter( "@strKeyField ", 200, 1, 100, strKeyField)
.Parameters.Append .CreateParameter( "@intPageNo ", 3, 1, 4, iPageNo-1)
.Parameters.Append .CreateParameter( "@intPageSize ", 3, 1, 4, iPageSize)
.Parameters.Append .CreateParameter( "@strWhere ", 200, 1, 4000, sWhere)
.Parameters.Append .CreateParameter( "@bitOrdType ", 16, 1, 1, iOrderType)
%>
asp代码略写了！
问题1：分页第1页无错误 第2页就出现错误 print @strSQL打印出来的 SQL语句完全可以在视图下正确执行 可就是在页面上出错
问题2：想用建立rs对象 但rs.movefirst不能用 不能移动记录集。
请各位指点下 那里有错误 多谢 问题解决马上给分 谢谢各位
[解决办法]
rs。open conn，1，3用游标打开
