run-time error '3704' operation is not allowed when the object is closed in VB6 while using SP using temp tables

Question

I am using the Stored procedure "sp_Missingdata" to get the data and to display in the MSFlexGrid, But i am getting the error as - run-time error '3704' operation is not allowed when the object is closed I searched in google a lot and changed these things - 1. set NOCOUNT ON for the Stored procedure.
2. set SET ANSI_WARNINGS OFF for stored procedure.

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

Note- My SP is using the temp table- here is my 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

On executing the SP the following result set appear, i want to display it in 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

Kindly help me out of this, any suggestion/help highly appreciated.

Answer 1

You need SET NOCOUNT ON in the body of the procedure, as the first line, not outside of the declaration, that only applies when altering the stored proc.