从存储过程中返回记录总数的问题

[susan0603]

asp代码:
<p > <% Dim CmdSP,rs
set CmdSP = Server.CreateObject("ADODB.Command")
CmdSP.ActiveConnection = cnn
CmdSP.CommandText = "sp_search"
CmdSP.CommandType = adCmdStoredProc
CmdSP.Prepared = true
Set rs = cnn.Execute("sp_Search 'v_datasheets ', 'Part,Name ', 'part ',10," & request.querystring("p") & ",0,1, 'brand=" & rs("ID") & " ' ")
While Not rs.EOF
% <br > <%=rs("name")% > <br >
<%rs1.MoveNext
Wend% > </p >

使用上面的代码能正确执行,但不能取得rs记录总数,请问怎样才能获取到记录总数.


存储过程代码:
ALTER PROCEDURE sp_Search

@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '* ', -- 需要返回的列
@fldName varchar(255)= ' ', -- 排序的字段名
@PageSize int = 25, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 1, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = ' ' -- 查询条件 (注意: 不要加 where)

AS

declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型

if @doCount != 0
begin
if @strWhere != ' '
set @strSQL = 'select count(*) as Total from [ ' + @tblName + '] where '+@strWhere
else
set @strSQL = 'select count(*) as Total from [ ' + @tblName + '] '
end

else
begin
if @OrderType != 0
begin
set @strTmp = ' <(select min '
set @strOrder = ' order by [ ' + @fldName + '] desc '
end

else

begin
set @strTmp = ' >(select max '
set @strOrder = ' order by [ ' + @fldName + '] asc '
end

if @PageIndex = 1
begin
if @strWhere != ' '
set @strSQL = 'select top ' + str(@PageSize) + ' '+@strGetFields+ ' from [ ' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) + ' '+@strGetFields+ ' from [ '+ @tblName + '] '+ @strOrder
end

else
begin
set @strSQL = 'select top ' + str(@PageSize) + ' '+@strGetFields+ ' from [ '
+ @tblName + '] where [ ' + @fldName + '] ' + @strTmp + '([ '+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [ '+ @fldName + '] from [ ' + @tblName + '] ' + @strOrder + ') as tblTmp) '+ @strOrder

if @strWhere != ' '
set @strSQL = 'select top ' + str(@PageSize) + ' '+@strGetFields+ ' from [ '
+ @tblName + '] where [ ' + @fldName + '] ' + @strTmp + '([ '
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [ '
+ @fldName + '] from [ ' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end

end

exec (@strSQL)

[hztgcl1986]

rs.RecordCount

[susan0603]

试过了,rs.RecordCount获取的为-1


另外还有一贴问的同样问题,帮我搞掂了一并送分结贴
http://topic.csdn.net/u/20071024/15/2451f95a-f281-447a-9eae-5f6eedc23e06.html

[toury]

过程前加: set nocount off 试一下;
还不行的话就把数据库连接cn改为ODBC

[susan0603]

试过了一样的,取到的是-1

[susan0603]

按存储过程的意思来,应该是@doCount bit=0这个参数不为0就可以返回记录总数,但是在改成这样时
Set rs1 = cnn.Execute("sp_Search3 'v_datasheets', 'Part,Name,nameAB,descr', 'part',10," & request.querystring("p") & ",1,1,'brand=" & rs("ID") & "' ")
执行时出错

ADODB.Recordset 错误 800a0cc1

在对应所需名称或序数的集合中，未找到项目。

[susan0603]

没人遇到过还是分少呀,怎么没人给解答一下!