I am having a problem returning a recordset when calling a SQL Server 2000 store procedure from VB6 using ADO. The following code example works fine if I comment out the insert statement, before the Select statement. But if the insert statement is in the store procedure I get a run-time error "3704, Operation is not allowed when the object is closed" when I check for end of file on the recordset "rst.EOF". I have no problem running the stored procedure in SQL Query Analyzer. Can anyone tell me what I am doing wrong? Thanks


'SQL Server 2000 Stored Procedure
--------------------------------------------------------------------------------------------------
CREATE PROCEDURE spTest AS

--Table Definition
--CREATE TABLE testTable (FileNames Varchar(110))

insert into testTable values('c:\test\test.txt')


select * from testTable
go


VB Code
---------------------------------------------------------------------------------
Private Sub Command1_Click()
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim value As String

Dim strConnectionString As String

strConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;" + _
"Initial Catalog=iiA;Data Source=" + "iiapro2"

Set cnn = New ADODB.Connection

cnn.ConnectionString = strConnectionString

cnn.Open , "sa", "temp"

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sptest"

Set rst = New ADODB.Recordset

Set rst = cmd.Execute

If rst.EOF = False Then
value = rst.Fields(0)
End If
rst.Close
Set rst = Nothing
Set cmd = Nothing
Set cnn = Nothing
End Sub