Cursor C1 already open

["Adam Lang" <aalang@xxxxxxxxxxxxxxxxxxxx>]

I am writing some short VB code to query information of the AS/400 using ADO
(ole db).  I want to create on connection and recall a function to keep
running the same query over and over with only a parameter change.

I have my connection routines in a class.

I create my connection in the initialize procedure.

Private m_sAS400Con As String
Private cnAS400 As ADODB.Connection
'---------------------------------------------------------------------------
------
Private Sub Class_Initialize()

m_sAS400Con = "Provider=IBMDA400;Password="""";User ID="""";Data
Source=clinton;" & _
                "Transport Product=Client Access;SSL=DEFAULT"

Set cnAS400 = New ADODB.Connection

    cnAS400.Open m_sAS400Con

End Sub

Then I have a  a public function that returns a string.

'---------------------------------------------------------------------------
------
Public Function GetSymbol(ByVal sPolNum As String) As String
Dim rsAS400 As ADODB.Recordset
Dim sSQL As String
Dim fd As ADODB.Field

sSQL = "SELECT symbol FROM RUGR80DAT.PMSP0200 WHERE policy0num = '" & _
    sPolNum & "'"

Set rsAS400 = New ADODB.Recordset

With rsAS400
    .CursorLocation = adUseClient
    .Open sSQL, cnAS400, adOpenStatic, adLockReadOnly
    .ActiveConnection = Nothing

    .MoveFirst
    Set fd = .Fields(0)
    GetSymbol = fd.Value

    .Close
End With

Set fd = Nothing
Set rsAS400 = Nothing

End Function

Then, from my form I call this:

Private Sub cmdRunMe_Click()
Dim MyClass As clsInterface
Dim MyFSO As FileSystemObject
Dim MyTSin As TextStream
Dim MyTSout As TextStream
Dim MyTSbad As TextStream
Dim readin As String
Dim sSymbol As String
Dim sPath As String
Dim i As Integer

Set MyClass = New clsInterface
Set MyFSO = New FileSystemObject
Set MyTSin = MyFSO.OpenTextFile("policy.txt", ForReading)
Set MyTSout = MyFSO.OpenTextFile("pol-sym.txt", ForWriting, True)
Set MyTSbad = MyFSO.OpenTextFile("pol-bad.txt", ForWriting, True)


Do Until MyTSin.AtEndOfStream
    i = i + 1

    'flushing
    If i = 30 Then
        lblDisplay.Caption = lblDisplay.Caption + 30
        Form1.Refresh
        MyTSout.Close
        MyTSbad.Close
        DoEvents
        Set MyTSout = MyFSO.OpenTextFile("pol-sym.txt", ForAppending)
        Set MyTSbad = MyFSO.OpenTextFile("pol-bad.txt", ForAppending)
        i = 0

    End If

    readin = MyTSin.Read(7)
    sPath = MyTSin.ReadLine

    ' Check that it is numeric. If it isn't, it is a garbage readin.
    ' We'll log those anyway and the path so we know they are bad.
    If IsNumeric(readin) Then
        sSymbol = MyClass.GetSymbol(readin)
        MyTSout.WriteLine sSymbol & vbTab & readin & vbTab & sPath
    Else
        ' Log the bad one
        MyTSbad.WriteLine readin & vbTab & sPath
    End If
Loop

MyTSout.Close
MyTSin.Close
MyTSbad.Close

Set MyTSbad = Nothing
Set MyTSin = Nothing
Set MyTSout = Nothing
Set MyFSO = Nothing
Set MyClass = Nothing

MsgBox "Done"

End Sub


I run the program.  I get the prompt to sign in to the AS/400.  It runs fine
for the first query, but when it goes to run a secodn query I get the error
"Cursor C1 already open".
Cause:
The cursor specified in an OPEN statement is already open for this call of
the program.  Recovery ... : Clsoe cursor C1 and then try the OPEN statement
again or change the name of the cursor, and then precompile the program
again.

I am a bit stumped on how to fix this.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com