fill treeview with records from database

[Co]

Hi All,

I'm a newbie at VB.net and I'm having trouble with a treeview and a
database connection.
I have an access database with a table called "Kabinet". I want to use
this table to fill my treeview.
Table Kabinet:
Id (autonum)
folder (text)
parent-id (num)

The records in the table:
1 Kabinet
2 Subfolder 1
3 Subfolder 1
4 Subfolder 1
5 Subsubfolder 2
6 Subsubfolder 3
7 Subsubsubfolder 6

Now I want to fill the treeview with "Kabinet"in the root and
underneath the next levels.

Dim sql = "SELECT * FROM Kabinet"
Dim cmd As New OleDbCommand(sql, conn)
Dim dr As System.Data.OleDb.OleDbDataReader
conn.Open()
dr = cmd.ExecuteReader()

While dr.Read()
tvRoot = Me.TreeView.Nodes.Add(dr.Item("folder"))
End While
dr.Close()

I need something to make sure the records go to the right level in the
treeview so:

Kabinet
|---------- Subfolder
| |------Subsubfolder
|-----------Subfolder
| |------Subsubfolder
| |------Subsubsubfolder
|-----------Subfolder

Also the code should be so that when I add a ....subfolder that it
will go to the right folder.
So the amount of levels should be n -deep.

Regards
Marco

 

[Armin Zingler]

Hi All,

I'm a newbie at VB.net and I'm having trouble with a treeview and a
database connection.
I have an access database with a table called "Kabinet". I want to use
this table to fill my treeview.
Table Kabinet:
Id (autonum)
folder (text)
parent-id (num)

The records in the table:
1 Kabinet
2 Subfolder 1
3 Subfolder 1
4 Subfolder 1
5 Subsubfolder 2
6 Subsubfolder 3
7 Subsubsubfolder 6

Now I want to fill the treeview with "Kabinet"in the root and
underneath the next levels.

Example:

Public Class Form1

Dim dt As New DataTable

Private Sub Form1_Load( _
ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load

dt.Columns.Add("ID", GetType(Integer))
dt.Columns.Add("Name", GetType(String))
dt.Columns.Add("IDParent", GetType(Integer))

dt.Rows.Add(1, "Kabinett", DBNull.Value)
dt.Rows.Add(2, "Subfolder", 1)
dt.Rows.Add(3, "Subfolder", 1)
dt.Rows.Add(4, "Subfolder", 1)
dt.Rows.Add(5, "Subsubfolder", 2)
dt.Rows.Add(6, "Subsubfolder", 3)
dt.Rows.Add(7, "Subsubsubfolder", 6)

AddNodes(TreeView1.Nodes, dt.Select("ISNULL(IDParent, -1) = -1"))

End Sub
Private Sub AddNodes( _
ByVal Nodes As TreeNodeCollection, ByVal rows As DataRow())

For Each row In rows
Dim Node = Nodes.Add(row("Name").ToString)
Dim SubRows = dt.Select("IDParent = " & CInt(row("ID")))
AddNodes(Node.Nodes, SubRows)
Next
End Sub

End Class


An undocumented features is the usage of "IS NULL", so you can replace

dt.Select("ISNULL(IDParent, -1) = -1")
by
dt.Select("IDParent IS NULL")



Armin

 

[Co]

Example:

Public Class Form1

   Dim dt As New DataTable

   Private Sub Form1_Load( _
      ByVal sender As System.Object, ByVal e As System.EventArgs) _
      Handles MyBase.Load

      dt.Columns.Add("ID", GetType(Integer))
      dt.Columns.Add("Name", GetType(String))
      dt.Columns.Add("IDParent", GetType(Integer))

      dt.Rows.Add(1, "Kabinett", DBNull.Value)
      dt.Rows.Add(2, "Subfolder", 1)
      dt.Rows.Add(3, "Subfolder", 1)
      dt.Rows.Add(4, "Subfolder", 1)
      dt.Rows.Add(5, "Subsubfolder", 2)
      dt.Rows.Add(6, "Subsubfolder", 3)
      dt.Rows.Add(7, "Subsubsubfolder", 6)

      AddNodes(TreeView1.Nodes, dt.Select("ISNULL(IDParent, -1) =-1"))

   End Sub
   Private Sub AddNodes( _
      ByVal Nodes As TreeNodeCollection, ByVal rows As DataRow())

      For Each row In rows
         Dim Node = Nodes.Add(row("Name").ToString)
         Dim SubRows = dt.Select("IDParent = " & CInt(row("ID")))
         AddNodes(Node.Nodes, SubRows)
      Next
   End Sub

End Class

An undocumented features is the usage of "IS NULL", so you can replace

    dt.Select("ISNULL(IDParent, -1) = -1")
by
    dt.Select("IDParent IS NULL")

Armin

Thanks Armin,

I tried the code and I almost got it working.
I was thinking of having the dt.Rows.Add(1, "Kabinett", DBNull.Value)
and dt.Rows.Add(2, "Subfolder", 1)
created depending on the table in the database.

How can I do this:

dim i as integer
for i = 1 to recordcount
dt.Rows.Add(dr.Item("Id"), dr.Item("folder"), dr.Item("parent-
id"))
next

problem is also the first record: dt.Rows.Add(1, "Kabinett",
DBNull.Value)

Marco

 

[Armin Zingler]

Thanks Armin,

I tried the code and I almost got it working.
I was thinking of having the dt.Rows.Add(1, "Kabinett", DBNull.Value)
and dt.Rows.Add(2, "Subfolder", 1)
created depending on the table in the database.

How can I do this:

dim i as integer
for i = 1 to recordcount
dt.Rows.Add(dr.Item("Id"), dr.Item("folder"), dr.Item("parent-
id"))
next

problem is also the first record: dt.Rows.Add(1, "Kabinett",
DBNull.Value)

I don't understand the question. After reading the records into the
datatable, you can call passing the top level folder(s) to the function that
recursively adds the sub nodes.


Armin

 

[Co]

I don't understand the question. After reading the records into the
datatable, you can call passing the top level folder(s) to the function that
recursively adds the sub nodes.

Armin

What I mean is that you created these lines:

dt.Rows.Add(1, "Kabinett", DBNull.Value)
dt.Rows.Add(2, "Subfolder", 1)
dt.Rows.Add(3, "Subfolder", 1)
dt.Rows.Add(4, "Subfolder", 1)
dt.Rows.Add(5, "Subsubfolder", 2)
dt.Rows.Add(6, "Subsubfolder", 3)
dt.Rows.Add(7, "Subsubsubfolder", 6)

Knowing that the table consisted of 7 records.
But what if we don't know this and just want to read all the records.

Marco

 

[Armin Zingler]

I don't understand the question. After reading the records into the

I meant "...you can call the function passing..."

What I mean is that you created these lines:

dt.Rows.Add(1, "Kabinett", DBNull.Value)
dt.Rows.Add(2, "Subfolder", 1)
dt.Rows.Add(3, "Subfolder", 1)
dt.Rows.Add(4, "Subfolder", 1)
dt.Rows.Add(5, "Subsubfolder", 2)
dt.Rows.Add(6, "Subsubfolder", 3)
dt.Rows.Add(7, "Subsubsubfolder", 6)

Knowing that the table consisted of 7 records.
But what if we don't know this and just want to read all the records.

You already have "While dr.Read()", right?


If you don't want to read all records into a datatable, an alternative is to
select the top level nodes only ("...where idparent is null"), then perform
one select per node ("...where idparent = ...") inside the recursive
function. However, I wanted to post an example of this version but I didn't
because I can't test it and it's a little bit more complicated because only
one datareader can be open at the same time.


Armin

 

[Co]

You already have "While dr.Read()", right?

If you don't want to read all records into a datatable, an alternative isto
select the top level nodes only ("...where idparent is null"), then perform
one select per node ("...where idparent = ...") inside the recursive
function. However, I wanted to post an example of this version but I didn't
because I can't test it and it's a little bit more complicated because only
one datareader can be open at the same time.

Armin- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Armin,

sorry I made a mistake.
I used the qry WHERE Id = 1
That's why I only got one record.
It's working now.

Marco

 

[Co]

You already have "While dr.Read()", right?

If you don't want to read all records into a datatable, an alternative isto
select the top level nodes only ("...where idparent is null"), then perform
one select per node ("...where idparent = ...") inside the recursive
function. However, I wanted to post an example of this version but I didn't
because I can't test it and it's a little bit more complicated because only
one datareader can be open at the same time.

Armin

Armin,

based on this code when I click a treeview item I would like to know
its dt.Columns.Add("ID", GetType(Integer))
So I can run a query from the database for example
"SELECT * FROM Bestanden WHERE lokatie=1"

Marco

 

[Armin Zingler]

Armin,

based on this code when I click a treeview item I would like to know
its dt.Columns.Add("ID", GetType(Integer))
So I can run a query from the database for example
"SELECT * FROM Bestanden WHERE lokatie=1"

You can derive your own class from Treenode and add information:

Class Node
Inherits TreeNode

Public ReadOnly row As DataRow

Public Sub New(ByVal row As DataRow)
Me.row = row
Me.Text = row("Name").ToString
End Sub
End Class


Replace
Dim Node = Nodes.Add(row("Name").ToString)

by
Dim Node = Nodes.Add(New Node(row))


When selecting a node, cast to your class type:

Private Sub tvw_AfterSelect( _
ByVal sender As Object, _
ByVal e As System.Windows.Forms.TreeViewEventArgs) _
Handles tvw.AfterSelect

Dim Node = DirectCast(e.Node, Node)
Dim ID = DirectCast(Node.row("ID"), Integer)


End Sub


Armin

 

[Co]

You can derive your own class from Treenode and add information:

   Class Node
      Inherits TreeNode

      Public ReadOnly row As DataRow

      Public Sub New(ByVal row As DataRow)
         Me.row = row
         Me.Text = row("Name").ToString
      End Sub
   End Class

Replace
         Dim Node = Nodes.Add(row("Name").ToString)

by
         Dim Node = Nodes.Add(New Node(row))

When selecting a node, cast to your class type:

   Private Sub tvw_AfterSelect( _
      ByVal sender As Object, _
      ByVal e As System.Windows.Forms.TreeViewEventArgs) _
      Handles tvw.AfterSelect

      Dim Node = DirectCast(e.Node, Node)
      Dim ID = DirectCast(Node.row("ID"), Integer)

   End Sub

Armin

When I do that I get an error message saying: Public member 'Nodes' on
type 'Integer' not found.

Private Sub AddNodes( _
ByVal Nodes As TreeNodeCollection, ByVal rows As DataRow())

Dim row As DataRow
For Each row In rows
Dim Node = Nodes.Add(New Node(row))
'Dim Node = Nodes.Add(row("Name").ToString)
Dim SubRows = dt.Select("IDParent = " & CInt(row("ID")))
AddNodes(Node.Nodes, SubRows)
Next
End Sub

Marco

 

[Armin Zingler]

When I do that I get an error message saying: Public member 'Nodes' on
type 'Integer' not found.

Private Sub AddNodes( _
ByVal Nodes As TreeNodeCollection, ByVal rows As DataRow())

Dim row As DataRow
For Each row In rows
Dim Node = Nodes.Add(New Node(row))
'Dim Node = Nodes.Add(row("Name").ToString)
Dim SubRows = dt.Select("IDParent = " & CInt(row("ID")))
AddNodes(Node.Nodes, SubRows)
Next
End Sub

My fault. I had to look twice to find it. If you pass a Node object to the
Add method, the function returns an Integer. Before, you passed a String,
and a new Node has been returned. Therefore, and due to Option Infer On,
Node is an Integer now, so...:

Dim Node As New Node(row)

Nodes.Add(Node)


Armin

 

[Co]

My fault. I had to look twice to find it. If you pass a Node object to the
Add method, the function returns an Integer. Before, you passed a String,
and a new Node has been returned. Therefore, and due to Option Infer On,
Node is an Integer now, so...:

             Dim Node As New Node(row)

             Nodes.Add(Node)

Armin

Thanks a lot man, you're really helping me here

Marco

 

[Co]

My fault. I had to look twice to find it. If you pass a Node object to the
Add method, the function returns an Integer. Before, you passed a String,
and a new Node has been returned. Therefore, and due to Option Infer On,
Node is an Integer now, so...:

             Dim Node As New Node(row)

             Nodes.Add(Node)

Armin

Armin,

In addition I have been trying to get the records from table Bestanden
to show in my Listview.
However I don't seem to know how to select the fields:

Private Sub LoadListView(ByVal tblID As Integer)
' TODO: Add code to add items to the listview based on the
selected item in the treeview

Dim lvItem As ListViewItem
ListView.Items.Clear()

Dim sql As String = "SELECT * FROM Bestanden WHERE lokatie=" &
tblID
Dim cmd As New OleDbCommand(sql, conn)
Dim dr As System.Data.OleDb.OleDbDataReader

dr = cmd.ExecuteReader()
dt.Columns.Add("Id", GetType(Integer))
dt.Columns.Add("extensie", GetType(Integer))
dt.Columns.Add("naam", GetType(String))
dt.Columns.Add("grootte", GetType(String))
dt.Columns.Add("auteur", GetType(String))
dt.Columns.Add("datum_gem", GetType(Date))
dt.Columns.Add("datum_gew", GetType(Date))

While dr.Read()
dt.Rows.Add(dr.Item("Id"), dr.Item("naam"), dr.Item
("grootte"), dr.Item("auteur"), dr.Item("datum_gem"), _
dr.Item("datum_gew"), dr.Item("extensie"))
End While

lvItem = ListView.Items.Add(dt.Select(dr.Item("naam")))
lvItem.SubItems.AddRange(New String() {dr.Item("grootte"),
dr.Item("auteur")})
lvItem.ImageIndex = dr.Item("grootte")

End Sub

MArco

 

[Armin Zingler]

In addition I have been trying to get the records from table Bestanden
to show in my Listview.
However I don't seem to know how to select the fields:

Am I right that this is a completely different problem?

While dr.Read()
dt.Rows.Add(dr.Item("Id"), dr.Item("naam"), dr.Item
("grootte"), dr.Item("auteur"), dr.Item("datum_gem"), _
dr.Item("datum_gew"), dr.Item("extensie"))
End While

lvItem = ListView.Items.Add(dt.Select(dr.Item("naam")))
lvItem.SubItems.AddRange(New String() {dr.Item("grootte"),
dr.Item("auteur")})

What do you want to do with the rows in the DataTable? If you just want to
add them to the Listview, write a loop.

In the long run you should consider using a DataAdapter/TableAdapter or/and
a typed Dataset.


Armin

 

[Co]

Am I right that this is a completely different problem?



What do you want to do with the rows in the DataTable? If you just want to
add them to the Listview, write a loop.

In the long run you should consider using a DataAdapter/TableAdapter or/and
a typed Dataset.

Armin

I read that not all of these can be used when you want to have a
dataaset like in vb a recordset
where you can edit/add and delete records. Is that right?

Marco

 

[Armin Zingler]

I read that not all of these can be used when you want to have a
dataaset like in vb a recordset
where you can edit/add and delete records. Is that right?

I don't know what you mean. A Datatable is a container for records/rows. A
*Adapter is there to synchronize the database with the Dataset.

I don't know where you read it but the 1st place to learn it is the
documentation.

Datasets: http://msdn.microsoft.com/en-us/library/ss7fbaez.aspx
Dataadapters/-readers: http://msdn.microsoft.com/en-us/library/ms254931.aspx

and many others in the ADO.Net documentation
http://msdn.microsoft.com/en-us/library/e80y5yhx.aspx



Armin