sql - 运行时错误 '3704' 在 VB6 中使用临时表使用 SP 时关闭对象时不允许操作

Question

我正在使用存储过程“sp_Missingdata”来获取数据并显示在 MSFlexGrid 中，但是我得到的错误是 -运行时错误“3704”对象关闭时不允许操作我在谷歌搜索了很多并改变了这些东西 - 1. 为存储过程设置 NOCOUNT ON。
2. 为存储过程设置 SET ANSI_WARNINGS OFF。

Private Sub Command2_Click()
Dim cmd As ADODB.Command
  Dim sqlnew, dd
Set cmd = New ADODB.Command
 cmd.ActiveConnection = ArtmConn
 cmd.CommandType = adCmdStoredProc
 cmd.CommandText = "sp_missingData"

  Dim rsnew As ADODB.Recordset
  Set rsnew = New ADODB.Recordset
  Set rsnew = cmd.Execute


  If Not rsnew.EOF Then  <-- Error is occuring here
    Set tblSop.DataSource = rsnew
  End If
  rsnew.Close
  Set rsnew = Nothing

End Sub

注意 - 我的 SP 正在使用临时表 - 这是我的 sp

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO
SET ANSI_WARNINGS OFF
GO

ALTER procedure [dbo].[sp_missingData] --exec [sp_missingData]
as begin
delete from tbl_missingData
select ol_code as outletNo, start as StartreceiptNo into #temp  from
(select l.s_ol_code as ol_code, l.sno + 1 as start
from vSalesNo as l
  left outer join vSalesNo as r on l.sno + 1 = r.sno and l.s_ol_code = r.s_ol_code
where r.sno is null) as tmpa inner join
(select s_ol_code, max(s_no) as s_no from sales group by s_ol_code) as tmpb
on s_ol_code = ol_code and start <= substring(s_no, 0, 11) where start!=0
union all
select ol_code as outletNo, start as EndreceiptNo   from
(select l.s_ol_code as ol_code, l.sno - 1 as start
from vSalesNo as l
  left outer join vSalesNo as r on l.sno - 1 = r.sno and l.s_ol_code = r.s_ol_code
where r.sno is null) as tmpa inner join
(select s_ol_code, max(s_no) as s_no from sales group by s_ol_code) as tmpb
on s_ol_code = ol_code and start <= substring(s_no, 0, 11) where start!=0
order by ol_code, start;
insert into tbl_missingData
SELECT * FROM
    (SELECT ROW_NUMBER() 
        OVER (ORDER BY outletNo) AS rownumber, 
        * 
    FROM #temp) AS Documents

select *,dbo.countOfIds(outletNo,rowNumber) as rpNo into #temp1 from tbl_missingData where outletno!='9163'
select * from #temp1
end

在执行 SP 时出现以下结果集，我想在 MSFlexGrid 中显示它

1   101 6381    p1
2   101 6472    p2
3   101 6534    p3
4   101 6565    p4
5   102 292 p1
6   117 234 p1
7   121 385 p1
8   121 6874    p2
9   121 6917    p3
10  121 6936    p4
11  121 6941    p5
12  121 6953    p6
13  121 6963    p7
14  121 7044    p8
15  121 7047    p9
16  124 14  p1
17  126 279 p1
18  127 5685    p1
19  127 5693    p2
20  139 650 p1
21  139 652 p2
22  401 942 p1
23  401 946 p2
24  401 951 p3
25  401 951 p4
26  401 953 p5
27  401 953 p6
28  401 956 p7
29  401 965 p8
30  401 972 p9
31  401 972 p10
32  401 974 p11
33  401 975 p12
34  401 980 p13
35  401 986 p14
36  401 999 p15
37  401 1000    p16

请帮助我解决这个问题，非常感谢任何建议/帮助。

Answer 1

您需要在过程主体中使用 SET NOCOUNT ON 作为第一行，而不是在声明之外，这仅在更改存储过程时适用。