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.