Tree View 2

[kimtp]

Did a search on tree view and nothing. Where can I go to get some idea on 'how to'?

thanx.

 

[johnwm]

Try:

http://www.ipdg3.com/tutorialresults.php?option=tutorial&id=000028

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/VBRef98/html/vbmscLROverview.asp

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[kimtp]

johnwm,

thanx. a bit late but nonetheless appreciated. Am just now getting into the tree view thing. Am sure I will be back.

 

[kimtp]

Got the treeview populated. But...

Have several tables. dealing with only two at the moment, tblArtists and tblAlbums. Connect by ArtistID which is primary key in tblArtists and not in tblAlbums.

Found some code at

http://support.microsoft.com/default.aspx?scid=kb;en-us;209927#XSLTH4160121123120121120120

and applied it. However, albums for artistID=1 show up under ArtistID 2.

' Open a recordset and loop through it to fill tvwMusic
' Fill Level 1 using ArtistID as Key property

sSql = "Select * FROM tblArtists ORDER BY SortName"
rst.Open sSql, cn, adOpenForwardOnly

Do Until rst.EOF
 frmSSTab.tvwMusic.Nodes.Add , , "a" _
 & rst.Fields("ArtistID").Value, _
 rst.Fields("Display Name").Value
rst.MoveNext
Loop
rst.Close

' Fill Level 2
rst.Open "tblAlbums ORDER BY Album", cn, adOpenForwardOnly
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
 strOrderKey = StrConv("o" & _
  rst.Fields("AlbumID").Value, vbLowerCase)
  frmSSTab.tvwMusic.Nodes.Add _
    rst.Fields("ArtistID").Value, tvwChild, strOrderKey, _
    rst.Fields("AlbumID").Value & "  " &   rst.Fields("Album").Value
  rst.MoveNext
Loop
rst.Close

I have only two Artists entered into tblArtists. One artist with one album in tblAlbums and the other with three. Checked the tblAlbums and it has the correct ArtistsID.

Have not added tblTracks until this is working. Any help is appreciated.

Kim

 

[kimtp]

If the 'ORDER BY SortName' is removed, then the Albums are displayed under the correct Artist. The question becomes how to sort the list by Artist?

Thanx.

Kim

 

[gmmastros]

You really should be using the database to do your sorting, and you need to get the database to link your tables correctly. I don't see anywhere where you indicate the type of database that you are using. I will assume you are using Microsoft Access. If this is an incorrect assumption, let me know.

It's hard to tell from the information that you posted, exactly what the table structure is. So, I'll guess a little here.

tblArtists
ArtistId
Display Name
SortName
LastName
FirstName

tblAlbums
AlbumId
ArtistId
Album

So each Album has 1 Artist, but each Artist can have multiple Albums.

In a different thread, you had...

    sSql = "SELECT tblArtists.ArtistID, Lastname, Firstname From tblArtists" & _
           " INNER JOIN tblAlbums on tblArtists.ArtistID = tblAlbums.AlbumID" & _
           " ORDER BY tblAlbums.Album"

In that thread, I pointed out that you may not be joining the tables properly. You appear to be joining the ArtistId to the AlbumId.

I suggest that you execute the following SQL statement. Make sure it is returning the correct data.

Select   * 
From     tblAlbums 
         Inner Join tblArtists 
           On tblAlbums.ArtistId = tblArtists.ArtistId
Order By tblAlbums.Album

Make sure this is returning the correct results. I do not recommend 'Select *' because you will ultimately return more data than you are using. After you get the data correct, the modify the select string to only unclude fields you are interested in. Ex...

SELECT   tblArtists.ArtistID, 
         Lastname, 
         Firstname 
From     tblAlbums 
         Inner Join tblArtists 
           On tblAlbums.ArtistId = tblArtists.ArtistId
Order By tblAlbums.Album

For this query, it looks like you are trying to get a list of artists and what albums they produced. Try this...

Select   * 
From     tblArtists 
         Inner Join tblAblums 
           On tblArtists.ArtistId = tblAlbums.ArtistId
Order by SortName

Again, once you get the data correct, the replace the 'select *' with 'Select Field1, field2, fieldwhatever' so that only the data you want is returned from the query.

This is an important concept. Once you master the ability to link tables together, your queries will be more efficient and your application will be a LOT faster.

I made some guesses and assumptions regarding your table structures. If I got anything wrong, just post back with more information and I (or someone else) will be able to help more.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kimtp]

Many thanx, gmmastros. I changed the original one that you mentioned and it works fine now. Your other guesses are pretty much on target.

However, whenever an Inner Join is used, the treeview says that the key is not unique. Only when a SELECT on a stand alone table are the desired results achieved.

Next tried was a simple:

 rst.Open "tblArtists ORDER BY Sortname", cn, adOpenForwardOnly

Is there a reason not to use this code? Or should sql be used? ORDER BY does not work.

Do not understand the statement 'using the database to do your sorting'. Could you elaborate?

Also, the third part of the treeview has been added which returns the tracks of the albums:

    ' Fill level 3
rst.Open "tblTracks", cn, adOpenForwardOnly
Do Until rst.EOF
' Link to Level 2 by referencing the strOrderKey key and set
' the node as a child node of level 2.
' Product Key is made alpha with "p" for parsing
   strOrderKey = StrConv("o" & rst!AlbumID, vbLowerCase)
   strProductID = StrConv(strOrderKey & "p" &  rst.Fields("TrackID").Value, vbLowerCase)
frmSSTab.tvwMusic.Nodes.Add strOrderKey, tvwChild, strProductID, rst.Fields("Title").Value
    rst.MoveNext
    Loop
    rst.Close

Inching closer but a long way to go.

Thanx.

Kim

 

[kimtp]

When an INNER JOIN is constructed in Access the correct records are listed. However, when the same code is used in VB, an error msg 'Item can not be found in the collection corresponding to the requested name or ordinal'.

sSql = "SELECT tblArtists.ArtistID, tblArtists.LastName, tblArtists.Firstname, tblAlbums.Album," & _
" tblTracks.Title, tblTracks.Duration, tblTracks.TrackID" & _
" FROM (tblArtists INNER JOIN tblAlbums ON tblArtists.ArtistID = tblAlbums.ArtistID)" & _
" INNER JOIN tblTracks ON (tblArtists.ArtistID = tblTracks.ArtistID)" & _
" AND (tblAlbums.AlbumID = tblTracks.AlbumID)" & _
" WHERE tblAlbums.Album = '" & tvwMusic.SelectedItem & "'"

rst.Open sSql, cn, adOpenDynamic, adLockOptimistic, adCmdText

Any Help is appreciated.

Kim

 

[gmmastros]

The error message 'Item can not be found in the collection corresponding to the requested name or ordinal' usually occurs when you try to reference a field in the recordset that does not exist in the recordset.

Ex.

Using your query, if you wanted to display information to the user, you do stuff like...

txtLastName.Text = rst("LastName")
txtFirstName.Text = rst("FirstName")

If you did...

txtShoeSize.Text = rst("ShoeSize")

You would get the same error you mention because ShoeSize is not a field returned by the recordset. My suggestion is to double check the spelling for your field names.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kimtp]

Thank you. What I do not understand then is how to extract the info from the recordset using multiple tables. Whenever this is used:

    sSql = "SELECT Lastname, Firstname FROM tblArtists WHERE [Display name] =  '" & tvwMusic.SelectedItem & "'"
text1.text = rst!Firstname
text2.text = rst!Lastname

the correct info is loaded. However, when this is used:

    sSql = "SELECT tblArtists.ArtistID, tblArtists.LastName, tblArtists.Firstname, tblAlbums.Album," & _
" tblTracks.Title,tblTracks.Duration, tblTracks.TrackID" & _
" FROM (tblArtists INNER JOIN tblAlbums ON tblArtists.ArtistID = tblAlbums.ArtistID)" & _
" INNER JOIN tblTracks ON (tblArtists.ArtistID = tblTracks.ArtistID)" & _
" AND (tblAlbums.AlbumID = tblTracks.AlbumID)" & _
" WHERE tblAlbums.Album = '" & tvwMusic.SelectedItem & "'"

text1.text = rst!Firstname
text2.text = rst!Lastname

the error msg 'Item can not be found...' appears. How is the info extracted? thanx.

 

[gmmastros]

I'm stumped. I don't see anything wrong. Sorry.

There is a way around this, though. You could reference the fields by ordinal value instead of field name.

In your example...

Ordinal Value   Field
     0          ArtistId
     1          LastName
     2          FirstName
     3          Album
     4          Title
     5          Duration
     6          TrackId

The ordinal value follows the order in which you select the fields, starting at 0. So...

txtLastName.text = rst(1)
txtFirstName.Text = rst(2)

Another suggestion for you...

When dealing with in-line sql code, you need to be careful with apostrophes in data.

This line...
" WHERE tblAlbums.Album = '" & tvwMusic.SelectedItem & "'"
would error out if there was an album with an apostrophe in the name. You can accomodate apostrophes by doubling them.

Ex...

" WHERE tblAlbums.Album = '" & Replace(tvwMusic.SelectedItem, "'", "''") & "'"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kimtp]

Thanx, again.

I subbed the ordinals for the field names and now get a 'either eof or bof....' msg when using the Inner Join sql.

Talked about being stumped.

 

[gmmastros]

Hmmm.... One trick I use when I find myself in this situation is to print out the query so you can examine it more.

sSql = "SELECT tblArtists.ArtistID, tblArtists.LastName, tblArtists.Firstname, tblAlbums.Album," & _
" tblTracks.Title, tblTracks.Duration, tblTracks.TrackID" & _
" FROM (tblArtists INNER JOIN tblAlbums ON tblArtists.ArtistID = tblAlbums.ArtistID)" & _
" INNER JOIN tblTracks ON (tblArtists.ArtistID = tblTracks.ArtistID)" & _
" AND (tblAlbums.AlbumID = tblTracks.AlbumID)" & _
" WHERE tblAlbums.Album = '" & tvwMusic.SelectedItem & "'"

[red]Debug.Print sSql[/red]

rst.Open sSql, cn, adOpenDynamic, adLockOptimistic, adCmdText

Add the line in red. Then, put the cursor on the next line (beginning with rst.open) and press the F9 key. You have just created a breakpoint. Now, press F5 to run the project. Click menus and buttons, whatever, to get this code to execute. When execution gets to the breakpoint, it will stop and throw you back in to VB. In the immediate window, you should see your query (because debug.print put it there). Copy/Paste the query in to a blank access query window and run it. With any luck, by the time you get this far in the process, you will have found your problem.

Good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kimtp]

The debug.print ssql procedure you describe returns all of the correct stuff. When I insert this:

lblArtist.Caption = rst.Fields("tblalbums.album")

is when the item can not be found msg. I am at a loss.

Thanx for your ideas. Any others would be helpful also.

Kim

 

[gmmastros]

Ah ha! It's so obvious now. You cannot reference the table name when getting data.

You should ONLY use the field name.
lblArtist.Caption = rst.Fields("album")

Because you can link tables together in to a single SQL String, it is possible to get duplicate field names. When this happens, you should use a field alias.

Ex.

Select tblAlbums.ArtistId [red]As AlbumsArtistId[/red],
       tblArtist.ArtistId As [red]ArtistArtistId[/red]
From   tblAlbums
       Inner Join tblArtists 
          On tblAlbums.ArtistId = tblArtists.ArtistId

Then...

debug.print RS("AlbumsArtistId")
debug.print RS("ArtistsArtistId")

Of course, in this example, it's kinda dumb because the values MUST be the same because we are joining the tables on that field, but hopefully the concept makes sense.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kimtp]

If the field is not one that is inner joined then the debug.print rst("Albums") returns the correct value. However, using the alias still returns the 'Item can not be found...' msg.

Perplexing.

 

[gmmastros]

Please post more code. Specifically, code that includes the SQL string sent to the database and also the code that retrieves the information from the recordset. Hopefully, by looking at the code, I can see where the problem is.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kimtp]

Used a different alias and this time it worked. Hopefully, this will cure my ills. Will report back.

Many thanx for all of your work and patience.