[RESOLVED] Loading from DB into array / treeview

[aikidokid]

I am completely new to Access and Databases (and limited ability with VB6 as well) but don't let that put you off.

I am using VB6 and Access 2002

I have downloaded the tutorial suggested in the FAQ's and run it in a practise project and got it all to work as should be.

I have now tried to put this code into my project, which is making a Code Library for myself.

The first thing I am trying to accomplish is to load the list in the attached image under the title tvwNodes into an array, to then use to name the Nodes in my treeview (tvwCode)

I have the following code where I get an error after all the 6 records have been added to the array

"Run-time error 94"
"Invalid use of Null"

My code is:

'declare it as a ADO-DB
Set cn = New ADODB.Connection
 
'set the connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Andrew\My Documents\VB6\New Code Library\DBCL.mdb"
cn.Open
Set rs = New ADODB.Recordset
rs.Open "tbl_Nodes", cn, adOpenKeyset, adLockPessimistic, adCmdTable
 
'move to first record
rs.MoveFirst
'loop to add items to array
ReDim tvw_Nodes(0)
Do Until rs.EOF = True
    tvw_Nodes(UBound(tvw_Nodes)) = rs.Fields("tvwNodes")
    Debug.Print tvw_Nodes(UBound(tvw_Nodes))
    rs.MoveNext
    ReDim Preserve tvw_Nodes(UBound(tvw_Nodes) = 1)
Loop

Could this be because the list in the second field is longer?
I wouldn't have thought so, but as I said in my first line of this thread ...

I am completely new to Access and Databases

[si_the_geek]

Could this be because the list in the second field is longer?

Yes it is.

For several rows tvwNodes is Null (not just an empty string, but no value at all), which causes problems if not used properly.

The simplest way to fix this is to append an empty string to the value (other methods are discussed in the FAQ article about it), eg:

tvw_Nodes(UBound(tvw_Nodes)) = rs.Fields("tvwNodes") & ""

..however in this case (based on my interpretation of your data) you don't need that, you need to do something else instead.

What you need to do is store the value for tvwNodes in every row, as currently VBA Excel etc do not have "parent" nodes.

You seem to have made a common error, which is to assume that the order or records actually means something (or will be returned in that order next time), which is not the case - a table is a group (or Set) of records, not an ordered list. If the database decides to, it can give you those records in a completely random order each time.

[aikidokid]

Yes it is.

For several rows tvwNodes is Null (not just an empty string, but no value at all), which causes problems if not used properly.

Ok, this I follow, thanks.

The simplest way to fix this is to append an empty string to the value (other methods are discussed in the FAQ article about it), eg:
[code]tvw_Nodes(UBound(tvw_Nodes)) = rs.Fields("tvwNodes") & ""

Again, I think I follow this, but just to understand what you have said here,
when loading the array elements from the database, on each loop add an empty string to the row contents.
Surley this would increase the size of the array elements? Or do you intend this, and then when using the array use something like If Not = "" then ....

however in this case (based on my interpretation of your data) you don't need that, you need to do something else instead.

What you need to do is store the value for tvwNodes in every row, as currently VBA Excel etc do not have "parent" nodes.

I have attached a picture in the document below to show what I am tring to achieve. This has been coded, but with a fixed number of nodes. I am trying to make it dynamic, so the user (me) can add a new node at any time.

You seem to have made a common error, which is to assume that the order or records actually means something (or will be returned in that order next time), which is not the case - a table is a group (or Set) of records, not an ordered list. If the database decides to, it can give you those records in a completely random order each time.

An interesting point, which obviously I didnt know. So If I wanted the nodes to be loaded A-Z then I would have to sort the array elements first would I?

[si_the_geek]

Again, I think I follow this, but just to understand what you have said here, when loading the array elements from the database, on each loop add an empty string to the row contents.
Surley this would increase the size of the array elements? Or do you intend this, and then when using the array use something like If Not = "" then ....

It doesn't increase the size, as you what you are appending doesn't have any length - it's just a cunning trick to convert a Null into an empty string ("") which VB is happy with, while not having any effect on non-Null values.

You could use the IsNull function instead as also shown in the article in the Database FAQs, but this method is simpler and quicker.

So If I wanted the nodes to be loaded A-Z then I would have to sort the array elements first would I?

You could, but it is easier (and faster) to ask the database to sort it for you.. to do that, you need to use an SQL statement instead of just a table name. To do that, replace line 9 of your code above with this:

Dim strSQL as String
  strSQL = "SELECT * FROM tbl_Nodes ORDER BY tvwNodes, tvwChildNodes"
  rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText

(while the strSQL variable isn't strictly necessary, it is useful for finding bugs!)

[aikidokid]

Thanks again si

It doesn't increase the size, as you what you are appending doesn't have any length - it's just a cunning trick to convert a Null into an empty string ("") which VB is happy with, while not having any effect on non-Null values.

You could use the IsNull function instead as also shown in the article in the Database FAQs, but this method is simpler and quicker.

I have changed the two parts of the code you suggested, adding the empty string to the end and the sql statement to sort the array.

When I now do this, I get 13 empty elements in the array before I get to the actual elements I am after.

have I missed something, or done it incorrectly?

'declare it as a ADO-DB
Set cn = New ADODB.Connection
 
'set the connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Andrew\My Documents\VB6\New Code Library\DBCL.mdb"
cn.Open
Set rs = New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tbl_Nodes ORDER BY tvwNodes, tvwChildNodes"
rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
''''rs.Open "tbl_Nodes", cn, adOpenKeyset, adLockPessimistic, adCmdTable
 
'move to first record
rs.MoveFirst
'loop to add items to array
ReDim tvw_Nodes(0)
Do Until rs.EOF = True
    tvw_Nodes(UBound(tvw_Nodes)) = rs.Fields("tvwNodes") & ""
    Debug.Print tvw_Nodes(UBound(tvw_Nodes))
    rs.MoveNext
    ReDim Preserve tvw_Nodes(UBound(tvw_Nodes) = 1)
Loop

[si_the_geek]

Have you added values to all rows the tvwNodes field?

[aikidokid]

Have you added values to all rows the tvwNodes field?

No, the entries in the tvwNodes field are as before, where the second field is longer.

Before, did you mean to add "" to the database?
I thought you meant to add it to the array - I think - getting confused now

[si_the_geek]

You should have values in every row of tvwNodes, as explained in the second half of post 2 - otherwise the child nodes do not have a parent.

Every row should contain all of the information that it needs to be 'complete' (or a method of linking to that information).

Before, did you mean to add "" to the database?

Nope, just add it to the value when you are reading it from the recordset - your code is fine, and will stop the original error from occurring again.

[aikidokid]

So, should my database look something like this

Field 1 Field 2 Field 3
Parent tvwChild tvwgrandchild
Parent tvwchild tvwgrandchild
Parent tvwchild
Parent tvwchild tvwgrandchild


So, in each row is stored the whole lot for the one main node, ie,

Source Code > VB Code > Functions
Source Code > VB Code > Modules
Source Code > VBA Code > Access
Source Code > Notes

[si_the_geek]

That's right.

You could go for a different database design to stop the duplication of data (eg: having one table for nodes, one for child nodes, and one for grand-child nodes)), but it would add more complexity to the database work - so you may want to avoid that!

[aikidokid]

Thanks alot si.

So if I change my database to this

Source Code > VB Code > Functions

and then would I do something like this

tvw_Nodes(UBound(tvw_Nodes)) = rs.Fields("tvwNodes") & rs.Fields("tvwChildNodes") & ""

Sorry if this is obvious but I did say it was all new to me

[si_the_geek]

That's not the way I'm afraid, you need to add each child node to the listview separately. Is there a reason for using the tvw_Nodes array, rather than adding items directly to the listview?


Probably the easiest way is to store the "previous" text of tvwNodes and tvwChildNodes in strings, and store the added nodes as separate variables. Then within the loop:

If the tvwNodes value for the current record is different, and a new "parent" node which you store to your variable (and reset the value of the tvwChildNodes "previous" string).

Then the same for the tvwChildNodes field (except you need to add the node to the current parent), and finally (if it isn't blank) add the tvwgrandchild node to the child node.

[aikidokid]

Is there a reason for using the tvw_Nodes array, rather than adding items directly to the listview?

No, not really. It's just because I don't know any other way to do it, so I assumed it was this way.
From what you are saying, it would be easier to add directly to the listview.

I don't usually ask this, but could you rustle up a quick demo sometime please, as I have been struggling with this for quite some time.

This would be from a database set out as above:

Source Code > VB Code > Functions
Source Code > VB Code > Modules
Source Code > VBA Code > Access
Source Code > Notes

[si_the_geek]

Ok.. this is a very "quick and dirty" demo tho, so there may be mistakes:

'declare & set up the connection
Dim cn as ADODB.Connection
  Set cn = New ADODB.Connection
  cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Andrew\My Documents\VB6\New Code Library\DBCL.mdb"
  cn.Open
 
'declare and set up the recordset
Dim rs as ADODB.Recordset
  Set rs = New ADODB.Recordset
Dim strSQL As String
  strSQL = "SELECT * FROM tbl_Nodes ORDER BY tvwNodes, tvwChildNodes, tvwGrandChild"
  rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
  '(note that MoveFirst is not needed after opening a recordset - it is just likely to cause problems!)
 
'add items to the TreeView
Dim lngNodeParent As Long, strNodeParent As String
Dim lngNodeChild As Long, strNodeChild As String  
  With TreeView1
    Do While Not rs.EOF  '(basically the same as you had before, I just prefer this style!)
        'if needed, add the parent
      If strNodeParent <> rs.Fields("tvwNodes").Value Then
        strNodeParent = rs.Fields("tvwNodes").Value
        lngNodeParent = lngNodeParent + 1
        .Nodes.Add , , CStr(lngNodeParent), strNodeParent
      End If
 
        'if needed, add the child
      If strNodeChild <> rs.Fields("tvwChildNodes").Value & "" Then
        strNodeChild = rs.Fields("tvwChildNodes").Value & ""
        If strNodeChild <> "" Then  '(dont add Null/blank child nodes)
          lngNodeChild = lngNodeChild + 1
          .Nodes.Add CStr(lngNodeParent), tvwChild, CStr(lngNodeChild), strNodeChild
        End If
      End If
 
      '(grand-child is similar to child, but you dont need to store the text/key!)
 
      rs.MoveNext
    Loop
  End With
    'close the recordset
  rs.Close
  Set rs = Nothing
 
'if not needed after this, close the connection here too

[aikidokid]

Thanks si,

I will try it out in a practise project.

[aikidokid]

OK, I have put this code into a form_load event.

I am getting the error "Type mismatch" on this line:

lngNodeParent = rs.Fields("tvwNodes").Value

Which is in the Do While Loop.

Another thing that may help, is the first field in the table is the ID table, so maybe this would help when identifying the keys?

Would this

strSQL = "SELECT * FROM tbl_Nodes ORDER BY  tvwNodes, tvwChildNodes"rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText

then become this

strSQL = "SELECT * FROM tbl_Nodes ORDER BY ID, tvwNodes, tvwChildNodes"rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText

Private Sub Form_Load()
'declare & set up the connection
Dim cn As ADODB.Connection
 
Set cn = New ADODB.Connection
 
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Andrew\My Documents\VB6\New Code Library\DBCL.mdb"
 
cn.Open
 
'declare and set up the recordset
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim strSQL As String
 
strSQL = "SELECT * FROM tbl_Nodes ORDER BY tvwNodes, tvwChildNodes"
rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
 
'(note that MoveFirst is not needed after opening a recordset - it is just likely to cause problems!)
 
'add items to the TreeView
Dim lngNodeParent As Long, strNodeParent As String
Dim lngNodeChild As Long, strNodeChild As String
 
With tvwCode
    Do While Not rs.EOF
        '(basically the same as you had before, I just prefer this style!)
        'if needed, add the parent
        If lngNodeParent <> rs.Fields("tvwNodes").Value Then
            lngNodeParent = rs.Fields("tvwNodes").Value
            lngNodeParent = lngNodeParent + 1
            .Nodes.Add , , CStr(lngNodeParent), strNodeParent
        End If
        
        'if needed, add the child
        If strNodeChild <> rs.Fields("tvwChildNodes").Value & "" Then
            strNodeChild = rs.Fields("tvwChildNodes").Value & ""
                If strNodeChild <> "" Then  '(dont add Null/blank child nodes)
                    lngNodeChild = lngNodeChild + 1
                    .Nodes.Add CStr(lngNodeParent), tvwChild, CStr(lngNodeChild), strNodeChild
                End If
        End If       '(grand-child is similar to child, but you dont need to store the text/key!)
        
        rs.MoveNext
    Loop
End With
'close the recordset
rs.Close
Set rs = Nothing
'if not needed after this, close the connection here too
cn.Close
End Sub

The values in the DB are as below:

Database View.gif

[si_the_geek]

That's understandable - the variable lngNodeParent is declared with a data type of Long, and you are trying to put a String value into it.

You've made a little mistake in copying the code - check the difference between lines 22 & 23 of my code, and lines 30 & 31 of yours.

[aikidokid]

I did actually look at that, thought it might have been this, but then left it as I had it

I am now getting the error "Invalid Key" on this line (line number 25 of your code):

.Nodes.Add , , CStr(lngNodeParent), strNodeParent

Would this be easier here to use the ID field here?
Or is this nothing to do with it?

Been here most of the day and getting confused here now

[si_the_geek]

I think that will be my fault!

I'm guessing that the Key (the value of CStr(lngNodeParent)) is already in use, as the Child version does similar things & doesn't use different numbers. Keys need to be unique (as they can be used to return items), so this is not allowed.

We need to make sure that the numbers are unique, and to do that I think your can just add the following after line 24 of my code (untested I'm afraid!):

   If lngNodeParent <= lngNodeChild Then lngNodeParent = lngNodeChild + 1

..and this after line 32:

   If lngNodeChild <= lngNodeParent Then lngNodeChild = lngNodeParent + 1

[aikidokid]

I am still getting the same error, on the same line as before!

.Nodes.Add , , CStr(lngNodeParent), strNodeParent

My code now is:

Do While Not rs.EOF
        '(basically the same as you had before, I just prefer this style!)
        'if needed, add the parent
        If strNodeParent <> rs.Fields("tvwNodes").Value Then
            strNodeParent = rs.Fields("tvwNodes").Value
            lngNodeParent = lngNodeParent + 1
            If lngNodeParent <= lngNodeChild Then lngNodeParent = lngNodeChild + 1
            .Nodes.Add , , CStr(lngNodeParent), strNodeParent
        End If
        
        'if needed, add the child
        If strNodeChild <> rs.Fields("tvwChildNodes").Value & "" Then
            strNodeChild = rs.Fields("tvwChildNodes").Value & ""
                If strNodeChild <> "" Then  '(dont add Null/blank child nodes)
                    lngNodeChild = lngNodeChild + 1
                    If lngNodeChild <= lngNodeParent Then lngNodeChild = lngNodeParent + 1
                    .Nodes.Add CStr(lngNodeParent), tvwChild, CStr(lngNodeChild), strNodeChild
                End If
        End If       '(grand-child is similar to child, but you dont need to store the text/key!)
        
        rs.MoveNext
    Loop

[si_the_geek]

I just looked at the help for that error and it clearly says that numeric-only keys (even if they are converted to strings) are not valid - you need to append an character too, eg:

.Nodes.Add , , "P" & CStr(lngNodeParent), strNodeParent
...
.Nodes.Add "P" & CStr(lngNodeParent), tvwChild, "C" & CStr(lngNodeChild), strNodeChild

[aikidokid]

I just looked at the help for that error and it clearly says that numeric-only keys (even if they are converted to strings) are not valid - you need to append an character too, eg:

.Nodes.Add , , "P" & CStr(lngNodeParent), strNodeParent
...
.Nodes.Add "P" & CStr(lngNodeParent), tvwChild, "C" & CStr(lngNodeChild), strNodeChild

I was SO close to sorting this then.

I appended a character to the parent node - but I used the same for the child node as well

Thanks alot for this - again.

[aikidokid]

You are a star si_the_geek

It now all works as I was after

[aikidokid]

If it is a help to anybody, the attached project is what I ended up with.

Obviously you would have to change the path for the Database, and you can see how my DB-Table is layed out in previous posts. (#16)

I hope it helps

[aikidokid]

Si_the_geek

Help me out here please

I have cut and paste this code and put it into my Code Library project.

The treeview is called the same, and the database is the same, but for some reason I am getting the error:

Element not found

On this line inside the Do While Loop

.Nodes.Add , , "P" & CStr(lngNodeParent), strNodeParent, "Close"

I have looked through this and cannot see any reason why it won't work - everything is the same in both the practise project and my actual project!

It gives the correct strNodeParent, which is "API" and the lngNodeParent is returning "P1", which is also expected!

EDIT
I have tried moving the code to the form_Activate, from Form_Load, but that isn't it either!

To save you looking back, here is the final code I had:

'declare & set up the connection
Dim cn As ADODB.Connection
 
Set cn = New ADODB.Connection
 
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Andrew\My Documents\VB6\New Code Library\DBCL.mdb"
 
cn.Open
 
'declare and set up the recordset
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim strSQL As String
 
strSQL = "SELECT * FROM tbl_Nodes ORDER BY tvwNodes, tvwChildNodes"
rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
 
'(note that MoveFirst is not needed after opening a recordset - it is just likely to cause problems!)
 
'add items to the TreeView
Dim lngNodeParent As Long, strNodeParent As String
Dim lngNodeChild As Long, strNodeChild As String
 
With tvwCode
    Do While Not rs.EOF
        '(basically the same as you had before, I just prefer this style!)
        'if needed, add the parent
        If strNodeParent <> rs.Fields("tvwNodes").Value Then
            strNodeParent = rs.Fields("tvwNodes").Value
            lngNodeParent = lngNodeParent + 1
            If lngNodeParent <= lngNodeChild Then lngNodeParent = lngNodeChild + 1
            .Nodes.Add , , "P" & CStr(lngNodeParent), strNodeParent, "Close"
        End If
        
        'if needed, add the child
        If strNodeChild <> rs.Fields("tvwChildNodes").Value & "" Then
            strNodeChild = rs.Fields("tvwChildNodes").Value & ""
                If strNodeChild <> "" Then  '(dont add Null/blank child nodes)
                    lngNodeChild = lngNodeChild + 1
                    If lngNodeChild <= lngNodeParent Then lngNodeChild = lngNodeParent + 1
                    .Nodes.Add "P" & CStr(lngNodeParent), tvwChild, "C" & CStr(lngNodeChild), strNodeChild, "Close"
                End If
        End If       '(grand-child is similar to child, but you dont need to store the text/key!)
        
        rs.MoveNext
    Loop
End With
'close the recordset
rs.Close
Set rs = Nothing
'if not needed after this, close the connection here too
cn.Close

[si_the_geek]

The "Close" part is the image parameter, which (from the help) specifies "The index of an image in an associated ImageList control".

I presume you either haven't associated the ImageList, or you don't have an image in it which can be referenced using "Close".


ps: there's no need for a PM on the same day as the latest reply in a thread, as I tend to leave a couple of threads open if I'm not sure of the answer & need to check them out in more depth.

[aikidokid]

Right again.

I had given the image the name "Close" in the practise project, whereas in my main project it was "Folder Closed".

All working again now, thanks