运行更高级的查询时关闭对象时不允许操作
当我尝试在 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_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
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).
相关文章