我正在使用经典ASP中的SQL Server存储过程,其中包含3个参数。 我正在尝试查找记录计数,但它返回"-1"。
我看到了一个类似的帖子,按照它的建议做了(检查游标类型,并在存储过程中添加"设置 nocounton"(,但这些更改不会影响 -1 记录计数。
下面是我在经典 ASP 页中的代码。
strInterestName = request("InterestName")
strActiveDate = request("activedate")
strExpireDate = request("expiredate")
Set objCommandSec = CreateObject("ADODB.Command")
Set objRS = CreateObject("ADODB.RecordSet")
objRS.cursorlocation = 3
objRS.cursortype = adOpenStatic
With objCommandSec
Set .ActiveConnection = objConnection
.CommandText = "[01_cms_search_pg_select_news_items_4]"
.CommandType = 4
.Parameters.Append .CreateParameter("@InterestName",adVarChar,
adParamInput, 25)
.Parameters.Append .CreateParameter("@ActiveDate",adDate, adParamInput)
.Parameters.Append .CreateParameter("@ExpireDate",adDate,
adParamInput)
.Parameters("@InterestName") = strInterestName
.Parameters("@ActiveDate") = strActiveDate
.Parameters("@ExpireDate") = strExpireDate
set objRS =.Execute()
End With
下面是存储过程的代码:
ALTER PROCEDURE [dbo].[01_cms_search_pg_select_news_items_4]
@InterestName varchar(50),
@ActiveDate datetime,
@ExpireDate datetime
AS DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT * ' +
' FROM news ' +
' WHERE ' +
' bulletin_display_indicator = ''true'' '+
' AND ' +
' website_homepg_display_indicator= ''false'' '
IF @InterestName is not null
SELECT @sql = @sql + ' AND (InterestName = @InterestName)
IF @ExpireDate is not null and @ExpireDate IS NOT NULL
SELECT @sql = @sql + ' AND (expiredate between @ActiveDate and @ExpireDate)
SELECT @sql = @sql + '; '
EXEC sp_executesql @sql, N'@InterestName varchar(50), @ActiveDate
DateTime, @ExpireDate DateTime',@InterestName, @ActiveDate,
@ExpireDate
我为此挣扎了一段时间,然后找到了适合我的东西。它不漂亮,但它可以完成工作。
让存储过程返回 2 个记录集:一个包含所需的表数据,后跟一个包含记录计数:
SELECT <all_you_need> FROM <your_table> WHERE <your_arguments>
SELECT [myCount] = @@ROWCOUNT
然后,在 ASP 文件中:
dim objRS, dataRS, countRS
Set objRS = CreateObject("ADODB.RecordSet")
Set dataRS = CreateObject("ADODB.RecordSet")
Set countRS = CreateObject("ADODB.RecordSet")
[your original call to the stored procedure]
set objRS =.Execute()
set dataRS = objRS
set countRS = objRS.nextrecordset()
countRS 现在包含一个记录集,其中包含一行和一个名为"myCount"的单列,您可以查询该记录集以获取记录计数。dataRS 包含您的原始数据集。
注意:如果在处理数据集之前不需要知道记录计数,则可以像这样简化:
dim objRS, countRS
Set objRS = CreateObject("ADODB.RecordSet")
Set countRS = CreateObject("ADODB.RecordSet")
[your original call to the stored procedure]
set objRS =.Execute()
[process your dataset as you need to]
set countRS = objRS.nextrecordset()