运行更高级的查询时，对象关闭时不允许操作 [英] 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:

对象关闭时不允许操作

当我运行此代码时，它正在工作:

When I run this code it's working:

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

但是，当我运行此代码时(如果我在 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
...

这是查询的更好概述:

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

我是否需要以另一种方式运行查询或者这段代码有什么问题?

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

推荐答案

这是一个常见问题，当将 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 <> 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屋！