title: run-time error '3704' operation is not allowed when the object is closed in VB6 while using SP using temp tables
body: <p>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 -
  <strong>run-time error '3704' operation is not allowed when the object is closed</strong>
  I searched in google a lot and changed these things -
   1. set NOCOUNT ON for the Stored procedure.<br>
   2. set SET ANSI_WARNINGS OFF for stored procedure.</p>
  
  <pre><code>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  &lt;-- Error is occuring here
      Set tblSop.DataSource = rsnew
    End If
    rsnew.Close
    Set rsnew = Nothing
  
  End Sub
  </code></pre>
  
  <p>Note- My SP is using the temp table- here is my sp</p>
  
  <pre><code>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 &lt;= 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 &lt;= 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
  </code></pre>
  
  <p>On executing the SP the following result set appear, i want to display it in MSFlexGrid</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Kindly help me out of this, any suggestion/help highly appreciated.</p>