Error! "operation is not allowed,when the object is closed"

[csKanna]

I installed SQL Server and wrote my StoredProcedures in SQL, then wrote
below commands for connection to database in VB6.0:

Set Cnn = New ADODB.Connection
Set Rss = New ADODB.Recordset
Set cmd = New ADODB.Command
Cnn.ConnectionString = provider
Cnn.Open
cmd.ActiveConnection = Cnn
cmd.CommandText = "CounterDoc_View" 'storedprocedurd name!
 
cmd.Parameters.Append cmd.CreateParameter("@DocID", adInteger,
adParamInput, 4, id)
cmd.Parameters.Append cmd.CreateParameter("@UserLanguage", adChar,
adParamInput, 2, UserLanguage)
cmd.Parameters.Append cmd.CreateParameter("@LoginName", adVarChar,
adParamInput, 50, Loginname)
 
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 0
cmd.Prepared = True
Set Rss = cmd.Execute

and my StoredProcedure is:

CREATE PROCEDURE CounterDoc_View

@DocID int,
@UserLanguage char(2),
@LoginName nvarchar(50)

as
Declare    @TemSqlStr as varchar(4000)

Set @TemSqlStr='Select LoginName, Name, Family, ID '

if (@UserLanguage='En')
       Set @TemSqlStr=@TemSqlStr+', FirstAccessDate, LastAccessDate '
else
       Set @TemSqlStr=@TemSqlStr+' ,dbo.Jal2Ch(FirstAccessDate) as
FirstAccessDate, dbo.Jal2Ch(LastAccessDate) as LastAccessDate  '

Set @TemSqlStr=@TemSqlStr+' From View_DocCounter where ID<>0  '
Set @TemSqlStr=@TemSqlStr+' and (ID ='+Convert(varchar(5), @DocID)+') '

if ((@LoginName!='') and (@LoginName!='Empty') and (@LoginName is not
Null))
  Set @TemSqlStr=@TemSqlStr+' and LoginName='''+@LoginName+''''

Exec (@TemSqlStr)
Go

after execution of my program in VB6.0 on this command "If Not Rss.EOF
Then" (and when program wants retrieve resuls of execution of SP)below
error will be appear:
"operation is not allowed,when the object is closed"

Thanks for your attention to me.
Regards!

[RobDog888]

If you step through your code by pressing F8, does your Cnn.Open
open without error? Do you have any "On Error Resume Next" statements?

[TonyDataMan]

I had the same problem. I found that the VBA recordset wouldn't open for my SP but it did for all the others.
I simply added "SET NOCOUNT ON" after the "AS" at the top of the SP and that fixed it. Recordset opens every time
It took an hour of puzzling before I found this out.

[ashishp1709]

I had the same problem. I found that the VBA recordset wouldn't open for my SP but it did for all the others.
I simply added "SET NOCOUNT ON" after the "AS" at the top of the SP and that fixed it. Recordset opens every time
It took an hour of puzzling before I found this out.

Thanks This is usefull for me