PDA

View Full Version : Using Record Set in loop



viky30
03-11-2015, 10:12 AM
I am getting the empty record set while using the record set in loop.
Can someone tell me what I doing wrong here?

Here is the code :


Sub extractData(CELL_NAME_AND_FUNCTION_ALIAS_IN_DICTIONARY As Scripting.Dictionary)
Dim functionAlias As String
Dim cellName As String
Dim Key As Variant
For Each Key In CELL_NAME_AND_FUNCTION_ALIAS_IN_DICTIONARY.Keys
Dim rs As New ADODB.Recordset
cellName = Key
functionAlias = CELL_NAME_AND_FUNCTION_ALIAS_IN_DICTIONARY(Key)

'-----------------------------------------

Dim dataArray As Variant

closeRS
OpenDB filePath:="P:\WorkSpace", fileName:="Reporting Application.xlsm"

strSQL = "SELECT * FROM [Configuration $] WHERE [Cell_Alias] ='" & functionAlias & "' order by Sequence"
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

If rs.recordCount > 0 Then
dataArray = rs.GetRows(rs.recordCount)
Else
Debug.Print "Empty Record Set"
End If
If rs.recordCount > 0 Then
For intRecord = 0 To UBound(dataArray, 2)

For intColumns = 0 To rs.Fields.Count - 1

Debug.Print " " & dataArray(intColumns, intRecord)
Next intColumns
'Debug.Print record
Next intRecord
Else
MsgBox "I was not able to find any Records.", vbCritical + vbOKOnly
Exit Sub
End If

'-----------------------------------------
Set dataArrayFromAllCommitmentFile = Nothing
Next Key
End Sub


Excel Data :



Cell_Alias

Function

Paramters

Sequence



F_HC-C/1a2

extractLoanDataFromAllCommitmentSheet


1



F_HC-C/1a2

extractDataFromSampleSheet


2



massageData

extractDataFromSampleSheet


2



massageData

extractLoanDataFromAllCommitmentSheet


1

Aflatoon
03-12-2015, 04:46 AM
Recordcount is not always reliable (it will often return -1 depending on cursor type and location) so I suggest you use:

If Not rs.EOF Then
instead of:

If rs.recordCount > 0 Then

viky30
03-12-2015, 12:45 PM
Got that Thanks.