为什么我得到一个字段计数不匹配错误后，第一次取回?

Question

我已经尝试了我能想到的一切，但我仍然在Fetch上得到一个错误

我正在为每个Select语句循环一个多值字段，并且需要将返回值存储在另一个多值字段中。它在第一次循环中工作，然后在第二次Fetch时出错。

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

感谢所有的想法或建议

Answer 1

根据HCL文档中的示例，Lookup在Fetch循环之外使用。试试这个:

Dim fieldCounter As LCField
...
Set fieldCounter = fldLstRecord2.Lookup("counter")
While (LC_Conn.Fetch( fldLstRecord2 ) > 0) 
redim Preserve CoveredLives(b)
Set CoveredLives(b) = fieldCounter.value(0)
b = b + 1
Wend

LCField的value总是返回一个值数组，所以你应该使用.value(0)。

Answer 2

事实证明，我需要在循环中使用' Delete fldListRecord2 '命令清除fldListRecord2。一旦我添加了这个，一切都工作了。我还必须Dim循环中的LCFieldList，以便每次通过。

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

谢谢你的建议