Why am I getting a Field Count Mismatch error after the first Fetch?

Question

I have tried everything I can think of but I am still getting an error on the Fetch

I am looping through a multi-value field for each Select statement and need to store the return values in another multi-value field. It works for the first loop and then errors out on the Fetch the 2nd time through.

If lc_conn.IsConnected Then     
    For a = 0 To UBound(uniquePlans)            
        b = 0
        SelectStatement2 = "Select Count(CSSN) as counter From cbsdta.covgold where " &_
        " CEFFDT<" & todaysDate & " And (CTRMDT=0 Or CTRMDT>=" & todaysDate & ") And " &_
        "CDTASRC='GIAS' AND CABSGP='" & groupNo & "' AND CPRODTA='" & uniquePlans(a) & "'"
        
        Count2 = lc_conn.Execute(SelectStatement2, fldLstRecord2 )
        if Count2 <> 0 then

            While (LC_Conn.Fetch( fldLstRecord2 ) > 0) '<---- Error here on 2nd loop

                redim Preserve CoveredLives(b)
                Set CoveredLives(b) = fldLstRecord2.Lookup("counter").value
                b = b + 1
            Wend
        End if
    Next
    doc.CoveredLives = CoveredLives
End If

All ideas or suggestions appreciated

Answer 1

It turned out that I needed to clear fldListRecord2 using a ‘Delete fldListRecord2’ command within the the loop. Once I added that, everything worked. I also had to Dim the LCFieldList within the loop to reinitialize it each time through.

If lc_conn.IsConnected Then
    ReDim Preserve CoveredLives(UBound(uniquePlans))        
    For a = 0 To UBound(uniquePlans)
        Dim fldLstRecord2 As New LCFieldList            
        If (lc_conn.Execute ("Select Count(CSSN) as counter From cbsdta.covgold where " &_
        " CEFFDT < " & todaysDate & " And (CTRMDT = 0 Or CTRMDT > " & todaysDate & ") And " &_
        "CDTASRC = 'GIAS' AND CABSGP = '" & groupNo & "' AND CPRODTA = '" & Right(uniquePlans(a),7) & "'", fldLstRecord2) <> 0) Then            
        End If          
        Set fld = fldLstRecord2.Lookup ("counter")
        While (lc_conn.Fetch(fldLstRecord2) > 0)                
            CoveredLives(a) = fld.text(0)                
            Delete fldLstRecord2
        Wend            
    Next        
    doc.CoveredLives = CoveredLives     
End If

Thanks for the ideas