[RESOLVED] SPROC and VB6 Question

[Mark Gambo]

I have the following SPROC:

USE IADATA
IF EXISTS (select * from syscomments where id = object_id ('TestSP'))
    DROP PROCEDURE TestSP
 
GO
CREATE PROCEDURE TestSP
    /*Declare Variables*/
    @ListStr varchar(100) /*Hold Delimited String*/
AS
 
DECLARE @ListTbl Table (InvUnit varchar(50)) /*Creates Temp Table*/
DECLARE @CP int /*Len of String */
DECLARE @SV varchar(50) /*Holds Result */
 
While @ListStr<>''
Begin
    Set @CP=CharIndex(',',@ListStr) /*Sets length of words - Instr */
    If @CP<>0
    Begin
        Set @SV=Cast(Left(@ListStr,@CP-1) as varchar) /*Copies Portion of String*/
        Set @ListStr=Right(@ListStr,Len(@ListStr)-@CP) /*Sets up next portion of string*/
    End
    Else
    Begin
        Set @SV=Cast(@ListStr as varchar)
        Set @ListStr=''
    End
    Insert into @ListTbl Values (@SV) /*Inserts variable into Temp Table*/
End
 
Select InvUnit From @ListTbl LT
INNER Join dbo.Incidents ST on ST.Inv_Unit=LT.InvUnit

and I am using the following VB code to access the SPROC:

Private Sub Command1_Click()
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim strLegend As String
Dim strData As String
 
Set adoConn = New ADODB.Connection
adoConn.Open connString
 
Set adoRS = New ADODB.Recordset
Set adoCmd = New ADODB.Command
 
With adoCmd
    Set .ActiveConnection = adoConn
    .CommandText = "TestSP"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("ListStr", adVarChar, adParamInput, 100)
    .Parameters("ListStr").Value = "Unit 41,Unit 32,Unit 34,Unit 54"
    
    Set adoRS = .Execute
    
    Do While Not adoRS.EOF
        Debug.Print adoRS.Fields(0).Value
    Loop
    
End With
 
adoRS.Close
Set adoRS = Nothing
Set adoCmd = Nothing
adoConn.Close
Set adoConn = Nothing
 
End Sub

I get an operation not allowed when object is closed error on the:

Do While Not adoRS.EOF

line. Any ideas?

Thanks!