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

本教程将介绍运行更高级的查询时关闭对象时不允许操作的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

运行更高级的查询时关闭对象时不允许操作 教程 第1张

问题描述

当我尝试在 ASP 页面上运行更高级的 SQL 查询时,我收到以下错误:

对象关闭时不允许操作

当我运行这段代码时,它正在工作:

...sql = "从 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)') ,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交叉应用(选择''dt'',dt union all select''result'',当dt ='+@currentYear+'然后结果结束)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...

这是对查询的更好概述:

声明@cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX),@orderby nvarchar(max),@currentYear varchar(4)选择@currentYear = cast(year(getdate()) as varchar(4))选择@cols= STUFF((SELECT ',' + QUOTENAME(year([datefrom]))来自 tbl_teams按年份分组([datefrom])按年份排序([datefrom]) descFOR XML PATH(''), 类型).value('.', 'NVARCHAR(MAX)'),1,1,'')select @orderby = 'ORDER BY ['+cast(year(getdate()) as varchar(4)) + '] desc'设置@query = '选择团队,赢得= [1],丢失=[2],平局 = [3]' + @cols + ',总计从(选择团队,new_col,全部的从(选择团队,dt = 年([日期从]),结果,总计 = count(*) over(按团队划分)来自 tbl_teams) d交叉申请(选择 ''dt'',dt 联合所有选择 ''result'',如果 dt = '+@currentYear+' 然后结果结束) c (old_col_name, new_col)) X枢(计数(new_col)对于 new_col in ([1], [2], [3],' + @cols + ')) p '+ @orderbyexec sp_executesql @query

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

解决方案

这是一个常见问题,原因是在将 ADODB 与 SQL Server 一起使用时,行数被解释为存储过程的输出.p>

为了避免这种情况记得设置

SET NOCOUNT ON;

在您的存储过程中,这将阻止 ADODB 返回关闭的记录集,或者如果出于某种原因您不想这样做(不知道为什么,因为您总是可以使用 @@ROWCOUNT 来传递行倒数),你可以使用

'返回下一个记录集,这将是存储过程的结果,而不是' SET NOCOUNT OFF 时生成的行数(默认).设置 rs = rs.NextRecordset()

如果 ADODB 检测到存储过程返回了下一个 ADODB.Recordset(最好检查 rs.State <> adStateClosed处理多个 ADODB.Recordset 对象).

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

When I run this code it's working:

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

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_teamsgroup 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) dcross 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

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

解决方案

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;

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

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

好了关于运行更高级的查询时关闭对象时不允许操作的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。