array not fully populated

[twisted-pixel]

Hi clever people (thought I'd start with a bit of flattery)

I have a calendar on one of my sites that uses various tables to pull the information from. One of these tables is the Event Type, which contains the colour in which the link should be displayed.

so the table looks like this

TypeID, Name, Colour
1, League, Blue
2, Social, Red

There are 7 entries in total.

I read these into an array

Set rsTypes = Server.CreateObject("ADODB.Recordset")
rsTypes.Open "SELECT * FROM Type", Conn, 3, 3
dim colorArray
colorArray=rsTypes.GetRows

In the Events table, I have an EventType that links it to the Type Table, and I use the following code to compare the type from the Events Table, with the Type in the array to get the colour

For i = LBound(colorArray) TO UBound(colorArray)
   If colorArray(0,i) = (rsEventsDay("EventType")) Then
   colorLink = colorArray(2,i)
Next

but, the problem I'm having is that for some reason (and I can't work out why), it only ever reads the first three records from the type table

I've added a response.write in the code to check what the array is, and this only outputs the first 3 records

For i = LBound(colorArray) TO UBound(colorArray)
   Response.write colorArray(0,i)
   If colorArray(0,i) = (rsEventsDay("EventType")) Then
   colorLink = colorArray(2,i)
Next

I wondered if it was something else in the code that was affecting this, so I wrote

Set rsTypes = Server.CreateObject("ADODB.Recordset")
rsTypes.Open "SELECT * FROM Type", Conn, 3, 3
dim colorArray
colorArray=rsTypes.GetRows'(7,1,2)
For i = LBound(colorArray) TO UBound(colorArray)
response.write i & " - " & colorarray(0,i) & " <BR> 
next

but the output is still only the first 3 rows....anyone have any ideas as to why? Is it related to the fact that there are 3 fields and so the array is being limited to just 3 records?

[twisted-pixel]

figured it out...it needs to be

For i = LBound(colorArray,2) TO UBound(colorArray,2)

[gk53]

I just do not understand why you need arrays? If you have recordsets originally you can compare values in recordsets, just set cursor location to client, open as keyset and set active connection to nothing. After that you can filter, or loop thru you recordsets and it will be mach simpler and faster

[twisted-pixel]

essentially, I'm doing it because I'm opening multiple recordsets...so I load the data from one into an array, and then use this array to compare against when opening subsequent recordsets

[gk53]

I understand that and you do not need that array, you can have disconnected recordset and do the same compare or just join tables on SQL and get results from database...