ASP 调用 存储过程,关于分页的事,在asp论坛解决不了,所以过来了
下面的存储过程以及在ASP中调用时都没有错误出现,我认为是 OK 的。
问题是:
当我
Set rs=myComm.Execute
response.write rs.recordCount
无法输出 rs.recordCount的值,请大家帮忙看看
-------------------------------------------------------------
存储过程如下:
CREATE PROC myx_prPageRecordset
@queryStr nvarchar(1000),
@keyField nvarchar (200),
@pageSize int,
@pageNumber int
AS
BEGIN
DECLARE @sqlText AS nvarchar(4000)
DECLARE @sqlTable AS nvarchar(4000)
SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber + 1) * @pageSize AS varchar(30)) + ' ' + @queryStr
SET @sqlText =
'SELECT TOP ' + CAST(@pageSize AS varchar(30)) + ' * ' +
'FROM (' + @sqlTable + ') AS tableA ' +
'WHERE ' + @keyField + ' NOT IN(SELECT TOP ' +
CAST(@pageNumber * @pageSize AS varchar(30)) + ' ' + @keyField +
' FROM (' + @sqlTable + ') AS tableB)'
print @sqlText
EXEC (@sqlText)
END
GO
在 ASP 中调用如下:
<%
PageSize=10
myPage=2
set myComm = server.CreateObject("adodb.command")
myComm.ActiveConnection = conn
myComm.CommandType = adCmdStoredProc
myComm.CommandText = "myx_prPageRecordset"
myComm.prepared=True
sql=" * from cw82014.hyxx order by newsid DESC"
myComm.Parameters.Append myComm.CreateParameter("@queryStr", adVarWChar, adParamInput, 1000, sql)
myComm.Parameters.Append myComm.CreateParameter("@keyField", adVarWChar, adParamInput, 200, "[newsid]")
myComm.Parameters.Append myComm.CreateParameter("@pageSize", adVarWChar, adParamInput, 1000, PageSize)
myComm.Parameters.Append myComm.CreateParameter("@pageNumber", adVarWChar, adParamInput, 1000, myPage - 1)
Set rs=myComm.Execute
response.write rs.recordCount
%>