sql – 在运行更高级的查询时关闭对象时不允许操作

当我尝试在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

我是否需要以其他方式运行查询或此代码有什么问题？

解决方法

为避免这种情况,请记住设置

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).

（编辑：滁州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!