Requery problem, closing my RecordSet

[MaBen]

I am using SQL server 7.0, VB6 sp4 and ADO 2.5.
When I'm running this code, witch call two Stored Procedures, first is a simple Select and the second is a Delete, I get an error after The requery "This operation is not authorized if the object is closed." my RecordSet is closed. Why???

Anybody help!

  Dim cnnDossier As ADODB.Connection
  Dim cmdDossier As ADODB.Command
  Dim rsKM As ADODB.Recordset
  Dim rsTemp As ADODB.Recordset

  Set cnnDossier = New ADODB.Connection
  
  ' Création de la conection
  With cnnDossier
    .CursorLocation = adUseClient
    .Open "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Dossiers;Data Source=Serveur"
  End With
  
  Set cmdDossier = New ADODB.Command
  Set rsKM = New ADODB.Recordset



  With cmdDossier
    Set .ActiveConnection = cnnDossier
    .CommandText = "sproc_CompHeuresKM_UserName_User"
    .CommandType = adCmdStoredProc
  End With
  
  rsKM.Open cmdDossier, , adOpenDynamic, adLockOptimistic
  Debug.Print "Record count after open = " & rsKM.RecordCount

  With cmdDossier
    Set .ActiveConnection = cnnDossier
    .CommandText = "sproc_CompHeuresKM_Delete_User"
    .CommandType = adCmdStoredProc
    .Parameters("@Numero_1") = x.Numero
  End With
  
  Set rsTemp = New ADODB.Recordset
  rsTemp.Open cmdDossier, , adOpenDynamic, adLockOptimistic
  Set rsTemp = Nothing
  Debug.Print "Record count after delete = " & rsKM.RecordCount
  
  rsKM.Requery
  Debug.Print "Record count after requery = " & rsKM.RecordCount  'Error message
  ' This operation is not authorized if the object is closed.

  
  Set rsKM = Nothing
  Set cmdDossier = Nothing
  Set cnnDossier = Nothing

[Paul Warren]

I've only just started working with ADO and I haven't used stored procedures but an educated guess would be that when you change cmdDossier after the initial .Execute the recordset is reset ( closed ? )

Why not move the line :

rsTemp = Nothing

to after the line

  Debug.Print "Record count after delete = " & rsKM.RecordCount

and change it to print the rsTemp.RecordCount instead.

OR, if that doesn't return the right value, take a copy of the RecordCount in rsKM after executing the first command and subtract the number of users you delete.

OR, if that doesn't work you'll have to execute another command to retrieve the number of records left in the table.

I could be completely wrong of course but I'm willing to take the chance.

[MaBen]

Thank's Paul

But it's not working,
The stat or rsTemp after Delete stored procedure is close.
And subtracting 1 from recordCount will work, but I need more than just the RecordCount.

The only solution that I found yet is:
Instead of using stored procedure for delete, is using :

rsKM.Delete

and forgot the requery thing.

or

rerun the first stored procedure, that will fill up the RecordSet with fresh data.