当我尝试在ASP页面上运行更高级的SQL查询时,我收到此错误：
operation not allowed when the object is closed
当我运行此代码时它正在工作：
... sql = "SELECT distinct team FROM tbl_teams" rs.open sql,conndbs,1,1 ...
但是,当我运行此代码时(如果我在Microsoft sql Server Management Studio中运行此代码,则此代码正常工作),我收到错误…
... sql = "DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX),@orderby nvarchar(max),@currentYear varchar(4) select @currentYear = cast(year(getdate()) as varchar(4)) select @cols = STUFF((SELECT ',' + QUOTENAME(year([datefrom])) from tbl_teams group by year([datefrom]) order by year([datefrom]) desc FOR XML PATH(''),TYPE ).value('.','NVARCHAR(MAX)'),'') select @orderby = 'ORDER BY ['+cast(year(getdate()) as varchar(4)) + '] desc' set @query = 'SELECT team,Won = [1],Lost=[2],Draw = [3]' + @cols + ',Total from ( select team,new_col,total from ( select team,dt = year([datefrom]),result,total = count(*) over(partition by team) from tbl_teams ) d cross apply ( select ''dt'',dt union all select ''result'',case when dt = '+@currentYear+' then result end ) c (old_col_name,new_col) ) x pivot ( count(new_col) for new_col in ([1],[2],[3],' + @cols + ') ) p '+ @orderby exec sp_executesql @query" rs.open sql,1 ...
这是对查询的更好概述：
DECLARE
@cols AS NVARCHAR(MAX),@currentYear varchar(4)
select @currentYear = cast(year(getdate()) as varchar(4))
select @cols
= STUFF((SELECT ',' + QUOTENAME(year([datefrom]))
from tbl_teams
group by year([datefrom])
order by year([datefrom]) desc
FOR XML PATH(''),TYPE
).value('.','')
select @orderby = 'ORDER BY ['+cast(year(getdate()) as varchar(4)) + '] desc'
set @query = 'SELECT team,Total
from
(
select
team,total
from
(
select team,total = count(*) over(partition by team)
from tbl_teams
) d
cross apply
(
select ''dt'',dt union all
select ''result'',case when dt = '+@currentYear+' then result end
) c (old_col_name,new_col)
) x
pivot
(
count(new_col)
for new_col in ([1],' + @cols + ')
) p '+ @orderby
exec sp_executesql @query
解决方法
当将ADODB与sql Server一起使用时,行计数被解释为存储过程的输出,这是一个常见问题.
为避免这种情况,请记住设置
SET NOCOUNT ON;
在你的存储过程中,这将停止ADODB返回一个关闭的记录集,或者如果由于某种原因你不想这样做(不知道为什么你总是可以使用@@ ROWCOUNT来传递行数),你可以使用
'Return the next recordset,which will be the result of the Stored Procedure,not 'the row count generated when SET NOCOUNT OFF (default). Set rs = rs.NextRecordset()
如果ADODB检测到存储过程返回了一个ADODB,则返回下一个ADODB.Recordset(在处理多个ADODB.Recordset对象时,最好检查rs.State<> adStateClosed).
