Index out of bounds

[kimtp]

Trying to populate a treeview. Works if a record is not deleted from the database. However, once a record or two are deleted the treeview will not populate because of the 'index out of bounds' msg.

There are three levels (nodes?) for this treeview. The first is to show the artist, the second his/her albums and the third their recordings. All artists have Albums but not all recordings have been entered. Because of the error, if an artist has multiple albums only the first in the DB shows up.

The DB has the ArtistID and AlbumID (two tables) set as autonumber. If they are changed to Integers, the gap between the IDs can be diminished, ie if the second last artist has an ID of 8 and the next ID is 11, an error occurs. If the ID of 11 is changed to 9, the tree view loads with no apparent problems.

I found this code to load the treeview at

http://support.microsoft.com/?id=209927

' Open a recordset and loop through it to fill tvwMusic
    ' Fill Level 1 using ArtistID as Key property
    If rst.State = adStateOpen Then rst.Close
    rst.Open "tblArtists", cn, adOpenForwardOnly
    Do Until rst.EOF
        frmSSTab.tvwMusic.Nodes.Add , , "a" & rst.Fields("ArtistID").Value, _
            rst.Fields("DisplayName").Value '& " " & rst.Fields("Lastname").Value
        rst.MoveNext
    Loop
    rst.Close
    ' Fill Level 2
    rst.Open "tblAlbums ORDER BY Album", cn, adOpenDynamic
    Do Until rst.EOF
        ' Link to level 1 by referencing the ArtistID key and set
        ' the Node as a child node of Level 1.  Use "o" and the StrConv()
        ' function in the new key property for Level 2, because AlbumID is a numeric field
        sOrderKey = StrConv("o" & rst.Fields("AlbumID").Value, vbLowerCase)
        frmSSTab.tvwMusic.Nodes.Add rst.Fields("ArtistID").Value, tvwChild, sOrderKey, _
                rst.Fields("Album").Value
        rst.MoveNext
    Loop
    rst.Close
    ' Fill level 3
    rst.Open "tblTracks", cn, adOpenForwardOnly
    Do Until rst.EOF
        ' Link to Level 2 by referencing the sOrderKey key and set
        ' the node as a child node of level 2.
        ' Product Key is made alpha with "p" for parsing
        sOrderKey = StrConv("o" & rst!AlbumID, vbLowerCase)
        sProductID = StrConv(sOrderKey & "p" & rst.Fields("TrackID").Value, vbLowerCase)
        frmSSTab.tvwMusic.Nodes.Add sOrderKey, tvwChild, sProductID, rst.Fields("Title").Value & ""
        rst.MoveNext
    Loop
    rst.Close

I thought of incrementing the IDs manually but that does not make sense since if any Artists/Albums are deleted the same problem occurs.

 

[DataLWalker]

Hi kimtp,

I think I see your problem.

BUG-FIX:
In your code for Level 2 change the line,
frmSSTab.tvwMusic.Nodes.Add rst.Fields("ArtistID").Value, tvwChild, sOrderKey, rst.Fields("Album").Value
to this,
frmSSTab.tvwMusic.Nodes.Add "a" & rst.Fields("ArtistID").Value, tvwChild, sOrderKey, rst.Fields("Album").Value

REASON BUG OCCURED:
The Treeview control requires the use of the Key property to link Nodes. While linking Child node to a Parent node the Child node's Parent (Reference) Key must match the Parent's Node Key.

In your code above let us consider that Parent is Level 1 and Child is Level 2
Level 1
Node Key : "a" & rst.Fields("ArtistID").Value
Parent Key : <not applicable, this is top-most level>

Level 2
Node Key : sOrderKey=StrConv("o" & rst.Fields("AlbumID").Value, vbLowerCase)
Parent Key : rst.Fields("ArtistID").Value

The Level 2 Parent Key is different from the Level 1 Node Key by a single character, namely "a"

Just prefix the "a" as per my suggestion above and it will not matter if records are added/deleted. All Artists will show up, and those that have no Albums will show up but will not have any child nodes, just like you have intended! Rock on...

Please feel free to post-back here if you have any more questions/problems with this.


HTH,
DataLWalker

 

[kimtp]

Thanx for the suggestion. Now the key is not unique and nothing loads. an out of bounds might have ocurred because of a record being entered without a proper ID.

Kim

 

[DataLWalker]

...and you are welcome, have you stepped through the code to check where the "Out of bounds" error actually occurs?

Which key is not unique? Artist or Album? The Treeview control's Key property requires some non-numeric value, so using only the Autonumber field will not work. You will have to prefix/suffix some character(s) to the Autonumber and then assign that value as the Key for any level. In this case, the numeric value will not matter.

HTH,
DataLWalker

 

[kimtp]

As outlined in the above procedure, I have prefixed each auto number with a different character. The first is "a", the second is "o" and the third is "p".

 

[DataLWalker]

Replace the code you have posted above with this code

Dim sArtistKey As String
Dim sAlbumKey As String
Dim sTrackKey As String
Dim rst1, rst2, rst3 As Recordset

' Open a recordset and loop through it to fill tvwMusic
    ' Fill Level 1 using ArtistID as Key property
    If rst1.State = adStateOpen Then rst1.Close
    rst1.Open "tblArtists", cn, adOpenForwardOnly
    Do Until rst1.EOF
        sArtistKey = StrConv("a" & rst1.Fields("ArtistID").Value, vbLowerCase)
        frmSSTab.tvwMusic.Nodes.Add , , sArtistKey, rst1.Fields("DisplayName").Value
        ' Fill Level 2
        rst2.Open "tblAlbums ORDER BY Album", cn, adOpenDynamic
        Do Until rst2.EOF
            ' Link to level 1 by referencing the ArtistID key and set
            ' the Node as a child node of Level 1.  Use "o" and the StrConv()
            ' function in the new key property for Level 2, because AlbumID is a numeric field
            sAlbumKey = StrConv(sArtistKey & "o" & rst2.Fields("AlbumID").Value, vbLowerCase)
            frmSSTab.tvwMusic.Nodes.Add sArtistKey, tvwChild, sAlbumKey, rst2.Fields("Album").Value & ""
            ' Fill level 3
            rst3.Open "tblTracks", cn, adOpenForwardOnly
            Do Until rst3.EOF
                ' Link to Level 2 by referencing the sOrderKey key and set
                ' the node as a child node of level 2.
                ' Product Key is made alpha with "p" for parsing
                sTrackKey = StrConv(sAlbumKey & "p" & rst3.Fields("TrackID").Value, vbLowerCase)
                frmSSTab.tvwMusic.Nodes.Add sAlbumKey, tvwChild, sTrackKey, rst3.Fields("Title").Value & ""
                rst3.MoveNext
            Loop
            rst3.Close
            rst2.MoveNext
        Loop
        rst2.Close
        rst1.MoveNext
    Loop
    rst1.Close

    If Not rst1 Is Nothing Then Set rst1 = Nothing
    If Not rst2 Is Nothing Then Set rst2 = Nothing
    If Not rst3 Is Nothing Then Set rst3 = Nothing

I am assuming that cn is a valid db Connection. Let me know how it went.


-DataLWalker

 

[kimtp]

Error msg 'key is not unique' when using the replaced code.

Reverting to the original code, if an artists ID is not sequential then the out of bounds error occurs, Therefore, if a title and/or an album is deleted, there are no errors. Only when an artist is deleted.

The out of bounds error occurs when trying to fill level 3

Set nodTemp = frmSSTab.tvwMusic.Nodes.Add(sOrderKey, tvwChild, sProductID, _
                rst.Fields("Title").Value, "note")
sOrderKey = "o" & rst.Fields("AlbumID").Value
sProductID = sOrderKey & "p" & rst.Fields("TrackID").Value

It does not matter whether the Artist is deleted within the program or manually in the db, as one would suspect.

 

[CasperTFG]

So what you are trying to do is this... correct me if I am wrong...

You want a Tree that will be like this...

Pink Floyd
   --->The Wall
         --->Track 1
         --->Track 2
         --->Track 3
   --->Dark Side of the Moon
         --->Track 1
         --->Track 2
         --->Track 3
The Smiths
   --->Meat is Murder
         --->Track 1
         --->Track 2
         --->Track 3

If above is what you want, then what is the parent node for Say Dark Side of the Moon if you ahve deleted Pink Floyd?

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[kimtp]

Since the tables have a relationship of one to many (tblArtists to tblAlbums to tblTracks) if the parent node is deleted then all tracks for the artist are deleted first then all albums are deleted then the artist is deleted. Thus, there is no parent node.

 

[CasperTFG]

Hmmm Okay how about this...

Dim sArtistKey As String
Dim sAlbumKey As String
Dim sTrackKey As String
Dim rst1, rst2, rst3 As Recordset

' Open a recordset and loop through it to fill tvwMusic
    ' Fill Level 1 using ArtistID as Key property
    If rst1.State = adStateOpen Then rst1.Close
    rst1.Open "tblArtists", cn, adOpenForwardOnly
    rst2.Open "tblAlbums ORDER BY Album", cn, adOpenDynamic
    rst3.Open "tblTracks", cn, adOpenForwardOnly
    
    
    
    Do Until rst1.EOF
        sArtistKey = StrConv("a" & rst1.Fields("ArtistID").value, vbLowerCase)
        frmSSTab.tvwMusic.Nodes.Add , , sArtistKey, rst1.Fields("DisplayName").value
        ' Fill Level 2
        rst2.Filter = "ArtistID=" & rst1.Fields("ArtistID").value
        Do Until rst2.EOF
            ' Link to level 1 by referencing the ArtistID key and set
            ' the Node as a child node of Level 1.  Use "o" and the StrConv()
            ' function in the new key property for Level 2, because AlbumID is a numeric field
            sAlbumKey = StrConv(sArtistKey & "o" & rst2.Fields("AlbumID").value, vbLowerCase)
            frmSSTab.tvwMusic.Nodes.Add sArtistKey, tvwChild, sAlbumKey, rst2.Fields("Album").value & ""
            ' Fill level 3
            rst3.Filter = "ArtistID=" & rst1.Fields("ArtistID").value & " AND AlbumID=" & rst2.Fields("AlbumID").value
            Do Until rst3.EOF
                ' Link to Level 2 by referencing the sOrderKey key and set
                ' the node as a child node of level 2.
                ' Product Key is made alpha with "p" for parsing
                sTrackKey = StrConv(sAlbumKey & "p" & rst3.Fields("TrackID").value, vbLowerCase)
                frmSSTab.tvwMusic.Nodes.Add sAlbumKey, tvwChild, sTrackKey, rst3.Fields("Title").value & ""
                rst3.MoveNext
            Loop
            rst2.MoveNext
        Loop
        rst1.MoveNext
    Loop
    rst1.Close
    rst2.Close
    rst3.Close

    If Not rst1 Is Nothing Then Set rst1 = Nothing
    If Not rst2 Is Nothing Then Set rst2 = Nothing
    If Not rst3 Is Nothing Then Set rst3 = Nothing

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[kimtp]

Correction - the 'index out of bounds' error is showing up on the second level. After deleting the artist the treeview re-populates first by collecting the artists, then the albums. This is where the index error occurs this time.

When a new artist is added, then only the artist is listed. The artistid immediately following the deleted record will list no child nor childchild. His data lists under the following record. The data for this artist now list under the new artist.

Finally, the next artist and data are correct. Something like this:

Pink Floyd
--->The Wall
--->Track 1
--->Track 2
--->Track 3
--->Dark Side of the Moon 'this record is deleted
--->Track 1
--->Track 2
--->Track 3
The Smiths 'this data is listed under
--->Meat is Murder river boat five
--->Track 1
--->Track 2
--->Track 3
River Boat Five
--->Ma! They’re Comin’ Down the Street 'this shows up
--->Track 1 under new artist
--->Track 2
--->Track 3
New Artist

'Tis a puzzlement.

 

[kimtp]

Casper,

Tried it and get a 'key is not unique' msg at the very beginning of the parent node

Do Until rst1.EOF
  sArtistKey = StrConv("a" & rst1.Fields("ArtistID").Value, vbLowerCase)
  frmSSTab.tvwMusic.Nodes.Add , , sArtistKey,_ rst1.Fields("DisplayName").Value 'error occurs here

Kim

 

[CasperTFG]

Hey Kim, do you have duplicate Artist ID's?

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[kimtp]

Only one in each table - tblArtists, tblAlbums and tblTracks.