Tom Tom - 1 year ago 67
SQL Question

Populating dictionary from RecordSet

I'm wondering if anyone can help me. I'm trying to populate a public dictionary from a recordset returned from an sql table.

It all seems to work fine, except one part. I don't know if it's not counting the number of keys in the dictionary correctly, or they aren't being entered correctly but for whatever reason when I try and show the total count, it always comes back with only 1 (supposed to be around 15) and only displays the first row details.

Can anyone help?

Public UserList As New scripting.Dictionary
Sub UserDL()
Dim USList As Range
Dim USArr(0 To 11) As Variant

Call ConnecttoDB

Set Cmd = New adodb.Command: Set rs = New adodb.Recordset

With Cmd
.CommandTimeout = 30
.ActiveConnection = cn
.CommandText = "CSLL.DLUsers"

Set rs = .Execute
End With

With rs
If Not .BOF And Not .EOF Then
While (Not .EOF)
For i = 1 To 11
USArr(i - 1) = rs(i)
Next i

With UserList
If Not .Exists(rs("Alias")) Then
.Add Key:=rs("Alias"), Item:=USArr
End If
End With
End If
End With

IA = UserList.Items
Debug.Print UserList.Count & " Items in the dictionary"
For Each element In IA
For i = 0 To 10
Debug.Print element(i)
Next i
Next element

Set Cmd = Nothing: Set rs = Nothing ': Set UserList = Nothing
End Sub

Answer Source

store rs("Alias") in a variable and then use that variable as key.

 dim sKey as String

    With UserList
       If Not .Exists(sKey) Then
           .Add sKey,USArr
       End If
     End With