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

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

VB Code:

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:

Code:

---

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!

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?

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.

Quote:

---

Originally Posted by 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.

---

Thanks This is usefull for me