当运行更高级的查询时，该对象已关闭不允许操作 [英] operation not allowed when the object is closed when running more advanced query

问题描述

当我尝试在ASP页上运行一个更高级的SQL查询我得到这个错误：

When I try to run a more advanced SQL query on an ASP page I get this error:

当对象被关闭不允许运行

operation not allowed when the object is closed

当我运行这个code它的工作：

When I run this code it's working:

...
sql = "SELECT distinct team FROM tbl_teams"
rs.open sql, conndbs, 1, 1
...

但是，当我运行这个code（这code为工作，如果我在Microsoft SQL Server Management Studio中运行它），我得到的错误...

But when I run this code (and this code is working if I run it in Microsoft SQL Server Management Studio), I get the error...

...
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)')         ,1,1,'')  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, conndbs, 1, 1
...

这是查询的一个更好的概述：

This is a better overview of the query:

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)') 
        ,1,1,'')

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

我是否需要运行在另一种方式或查询什么是错的这个code？

Do I need to run the query on another way or what is wrong with this code?

推荐答案

这是由行的共同问题计数是间$ P $使用时PTED作为输出从存储过程 ADODB 与SQL Server。

This is a common problem caused by row counts being interpreted as output from a Stored Procedure when using ADODB with SQL Server.

要避免这种情况记得来设置

To avoid this remember to set

SET NOCOUNT ON;

在您的存储过程，这将阻止ADODB返回一个封闭的记录，或者出于某种原因你不想这样做（不知道为什么，你总是可以使用 @@ ROWCOUNT 通过行数背面），可以使用

in your Stored Procedure this will stop ADODB returning a closed recordset, or if for whatever reason you don't want to do this (not sure why as you can always use @@ROWCOUNT to pass the row count back), you can use

'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.Recordset 如果ADODB检测由存储过程返回（可能是最好的选择其一 rs.State＆LT ;方式＆gt; adStateClosed 与多个ADODB.Recordset对象打交道时）

which returns the next ADODB.Recordset if ADODB has detected one being returned by the Stored Procedure (might be best to check rs.State <> adStateClosed when dealing with multiple ADODB.Recordset objects).

这篇关于当运行更高级的查询时，该对象已关闭不允许操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！