Collections

[Kdev]

Normally I use arrays for data processing, however the program I am working on has a nested loop iterating over 2 arrays that can contain 5000+ entries in each. The outside array always has to iterate over every item but the inside array is just looking to see if that array contains the same serial number as the outside array. I'm afraid I'm not being very clear. What I want to do is iterate over one group and for each item in that group (array of serial numbers) check to see if the serial number is in another group.

I tried using collections keyed by serial numbers so that my program would look something like:

for each serial in SerialsIn
sDateOut = DatesOut(serial)
...
further processing
...
next serial



One problem I may have is that not every serial in the SerialsIn collection will be in the DatesOut collection. Besides that though I attempted to retrieve a date by forcing the issue like this:

sDateOut = DatesOut("00012345")



I get an error on the above line "Object is no longer valid" I know that the serial number used is in the collection. Why do I get this error? Is there a better way to process such information? What happens if I use a key on a collection when that key is not in the collection?

-K

[Clearcode]

There is a problem with Collection keys in Visual basic - if the key is numeric, VB tries to evaluate it as an index instead.
Try adding the letter K to the beggining of the key when you add items and looking for that i.e.


DatesOut.Add Serial, "K" & Serial




Then

sDateOut = DatesOut("K"&Serial)




HTH,
Duncan

-------------------------------------------------
Ex. Datis: Duncan Jones
Merrion Computing Ltd
http://www.merrioncomputing.com

[Kdev]

Good point; that was one I overlooked. However this does not solve my problem. Let me add some more information which may be helpful.

I create the collection in one function then I pass this collection by referenece to the function that loads the data and keys the collection. Finally this collection is passed to a 3rd function by value for processing of the information. It is in this 3rd function that I get the error. Incidentally, I also noticed that I can display DatesOut.Count (which is 1327) but if I try to display DatesOut(9) I get the Object Is No Longer Valid error. DatesOut contains all string-dates and index 9 certainly exists.

-K

[Kdev]

Ok here is an update. Initially I was adding items to the collection via:

DatesOut.Add(rs.fields("DATE"))

This method worked fine until I decided to start adding keys to the collection. VB would not let me process the collection thus:

DatesOut.Add(rs.fields("DATE"), rs.fields("SRL"))

VB told me that it was expecting to return a value so I had to modify the line like so:

Call DatesOut.Add(rs.fields("DATE"), rs.fields("SRL"))

The error only occurs on collections that I have keyed with the above line. What am I doing wrong?

-K

[Clearcode]

I haven't code dthis to check the theory, so forgive me if its wrong, but :

Call DatesOut.Add(rs.fields("date"), rs.fields("SRL"))



Is adding an object of type Field to the dates collection, not the string. Try:

Call DatesOut.Add(rs.fields("date").Value, rs.fields("SRL").Value)




Otherwise when you close the rs recordset, the fields objects become invalid.

HTH,
Duncan

-------------------------------------------------
Ex. Datis: Duncan Jones
Merrion Computing Ltd
http://www.merrioncomputing.com

[dfwade]

I Think I understand what you are trying to do, however i don't think that a collection is the best way to go about this. Collections are notoriously slow in processing a large quantity of records. You would propbaly be better off to use a string. This is an Example:

option Explicit
private sArray1() as string
private sArray2() as string
private sArray1Join as string
private sArray2Join as string

private Sub Command1_Click()
Label1 = CheckArray(List1.Text)
End Sub

private Sub Form_Load()
'''''''Add a list box, commane button and a lable to the form

Dim lngLoop as Long
Dim lngCnt as Long

LoadArrays
lngCnt = UBound(sArray1) - 1
for lngLoop = 0 to lngCnt
List1.AddItem sArray1(lngLoop)
next

End Sub
private Sub LoadArrays()
Dim lngLoop as Long
Dim lngCnt as Long

'Populate the Arrays
ReDim Preserve sArray1(1000)
ReDim Preserve sArray2(2000)
lngCnt = UBound(sArray1) - 1

'Fill all Values 0 to 1000
for lngLoop = 0 to lngCnt
sArray1(lngLoop) = "Base Test " & lngLoop
next
sArray1Join = Join(sArray1, "|") 'Load the Array Values into a string

lngCnt = UBound(sArray2) - 1

'Fill all Values 0 to 2000 but only the first 200 will contain matching values to sArray1
for lngLoop = 0 to lngCnt
sArray2(lngLoop) = "Base Test " & lngLoop + lngLoop + 50
next
sArray2Join = Join(sArray2, "|") 'Load the Array Values into a string

End Sub

private Function CheckArray(CheckValue as string) as Boolean
Dim x as Integer
x = InStr(1, sArray1Join, CheckValue)
If x > 0 then 'Hey it is in the first array
x = 0
x = InStr(1, sArray2Join, CheckValue) 'Check the Second Array
If x > 0 then
CheckArray = true
End If
End If


End Function





Yahoo Messenger ID dfWade10900

[Kdev]

Merçi! That worked great.

Another strange thing about VB: when I add an object to a collection with only specifying rs.fields("DATE") I get the string value but if I attempt to specify a key as well it then returns a field object like you said. I do not like the inconsistency in VB. Sometimes it is a joy because I do not get nearly as many errors as when I code with Borland C++ Builder, but at least Borland's product forces me to learn the "correct" syntax for programming

-K

[John G Duffy]

I may be all wet on this, but why don't you use an IN-Memory Recordset instead of a collection?
Much more flexible and you can use SQL commands for locating things. Below is a crude sample of this. You will need a reference to Microsoft ActiveX Data Objects Recordset 2.5 Library for the sample to work plus a command button.

option Explicit
Dim rs as Recordset

public Function ReadDirectory(optional Path as string, optional FilePattern as string = "*.*") as Recordset
'This Function loads a directory into an in memory Recordset and then sorts on the date
'set rs = new Recordset
Dim intCnt as Integer
Dim str as string
If len(Path) = 0 then Path = App.Path & "\" else Path = Path & "\"
With rs
.Fields.Append "FileName", adChar, 255
.Fields.Append "FileDate", adDate
.CursorLocation = adUseClient
.Open
.AddNew
.Fields("FileName") = Dir(Path & FilePattern, vbNormal)
.Fields("Filedate") = FileDateTime(Trim(Path & .Fields("FileName")))
.Update
End With


'loop through the directory and load the recordset
Do
str = Dir
If len(str) = 0 then Exit Do

With rs
.AddNew
.Fields("FileName") = str
.Fields("Filedate") = FileDateTime(Path & str)
.Update
End With
Loop Until len(str) = 0

'You now have all of the files and their date of creation

rs.Sort = "Filedate Desc"
set ReadDirectory = rs
End Function



private Sub Command1_Click()

Dim iret
set rs = new Recordset
iret = ReadDirectory("C:\Windows\", "*.*")

With rs
.MoveFirst
' other stuff
Do Until rs.EOF
Debug.print Trim(.Fields("Filename").Value); " "; .Fields("FileDate").Value
.MoveNext
Loop
End With
End Sub




John G