-
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!
Code:
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
-
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 :
to after the line
Code:
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.
-
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 :
and forgot the requery thing.
or
rerun the first stored procedure, that will fill up the RecordSet with fresh data.
