fill treeview with records from database

  • Thread starter Thread starter Co
  • Start date Start date
C

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
 
Co said:
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.
Click to expand...

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
 
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
Click to expand...

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
 
Co said:
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)
Click to expand...

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
 
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
Click to expand...

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
 
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.
Click to expand...


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
 
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 -
Click to expand...

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
 
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
Click to expand...

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
 
Co said:
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"
Click to expand...


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
 
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
Click to expand...

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
 
Co said:
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
Click to expand...

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
 
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
Click to expand...

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

Marco
 
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
Click to expand...

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
 
Co said:
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:
Click to expand...

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")})
Click to expand...

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
 
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
Click to expand...

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
 
Co said:
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?
Click to expand...


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
 
Back
Top