We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Problem with collections

clsmaster
clsmaster asked
on
Medium Priority
213 Views
Last Modified: 2010-05-03
I have a collection that I'm adding items to these items are base off a database recordset.  The problem that I'm having is that each time I add a new item to the collection it adds successfully, but it also overwrites all previous items in the collection with the new information and I have now clue why.
Here is the function that adds the items to the collection:

Public Function GetHistory(ByVal Town As String, ByVal Map As String, ByVal Lot As String, Optional ByVal Unit As String) As Collection
  'returns a collection of all the Sales Transaction for the Property
  Dim colHistory As New Collection  'The Collection
  Dim hisItem As New cHistoryItem   'The item
  Dim rs As New ADODB.Recordset     'The recordset
  Dim cn As New ADODB.Connection    'The connection to the database
 
  'open the database
  cn.Open "FILEDSN=Assessor"
  If Len(Unit) Then

    rs.Open "SELECT [ID],[Town],[Owner],[SaleDate],[SalePrice],[DeedNo],[Book],[Page] FROM [History] WHERE [Map] = '" & Map & "' AND [Lot] = '" & Lot & "' AND [Unit] = '" & Unit & "' AND [Town] = " & Town & " ORDER BY [SaleDate]", cn, adOpenForwardOnly, adLockReadOnly

  Else

    rs.Open "SELECT [ID],[Town],[Owner],[SaleDate],[SalePrice],[DeedNo],[Book],[Page] FROM [History] WHERE [Map] = '" & Map & "' AND [Lot] = '" & Lot & "' AND [Town] = " & Town & " ORDER BY [SaleDate]", cn, adOpenForwardOnly, adLockReadOnly

  End If

  'loop throught the returned recordset
  Do While Not rs.EOF

    'clear old values
    hisItem.Clear

    'assign new values
    hisItem.Owner = rs.Fields("Owner")
    hisItem.SaleDate = rs.Fields("SaleDate")
    hisItem.SalePrice = rs.Fields("SalePrice")
    hisItem.Book = rs.Fields("Book")
    If Not IsNull(rs.Fields("Page")) Then hisItem.Page = rs.Fields("Page")
    If Not IsNull(rs.Fields("DeedNo")) Then hisItem.DeedNo = rs.Fields("DeedNo")
    hisItem.Town = rs.Fields("Town")

    'add item to the collection
    colHistory.Add hisItem, CStr(rs.Fields("ID"))

    'move to the next item in the recordset
    rs.MoveNext
  Loop
  Set GetHistory = colHistory

  'cleanup resources
  rs.Close
  Set rs = Nothing
End Function
Comment
Watch Question

Try it without putting the key value in (CStr(rs.Fields("ID"))) during the add. I know it sounds strange, but your code looks fine. Nothing glaringly wrong pops up. See if the values will go into the collection without a key, or if you end up with the same problem.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
I mean the line,

Set hisItem = Nothing

Not Set hisItem = Nothing. ( NO . )

I mean the line,

Set hisItem = Nothing

Not Set hisItem = Nothing. ( NO . )

Author

Commented:
That did it thanks

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.