by I created a sample Stored Proc in SQLServer 2000 which Selects a field
from a "User" tabe and inserts it into a temporary table (#tmpUser):
CREATE PROCEDURE [TestProc]
As
Select UserID as UserID into #tmpUser from [User]
Select * from #tmpUser
GO
I test the procedure using SQL Query Analyzer and 15 records are show.
I then tried to excute the Proc from VB which resulted in an error:
(Run Time error: 3704 'Operation is not allowed when the object is
closed')
If I remove the temp table in the stored procedure and do a simple
Slect statement (i.e. Select UserID from [User]), the VB code runs
fine.
Can anyone shed some light as to what the problem may be and how to
resolve this problem?
Thanks in advance.
' ****** VB Code *******'
Private Sub Form_Load()
Dim Cnn As Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Const CnString =
"PROVIDER=SQLOLEDB;SERVER=Dad_Desktop;UID=sa;PWD=;DATABASE=Friends;"
Set Cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
Cnn.Open CnString
With rs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With
With cmd
.ActiveConnection = Cnn
.CommandType = adCmdStoredProc
.CommandText = "TestProc"
End With
rs.Open cmd
While Not rs.EOF
Print rs(0)
rs.MoveNext
Wend
Cnn.Close
Set rs = Nothing
Set cmd = Nothing
Set Cnn = Nothing
End Sub