-
Aug 15th, 2005, 10:03 AM
#1
Thread Starter
Addicted Member
[RESOLVED] Populate TreeView control from SQL Server Database
Hi all,
I've been searching the net and everywhere, but no Cigar.
I want to populate a Treeview Control in vb.net from a sql server database.
This is what my query with the data looks like.
NODEID PARENTNODEID LABEL IMAGEINDEX ARGUMENT
13 0 PRODUCTION A
70 13 WEIGHBRIDGE OPERATIONS B Tbl_Weighbridge_InputSelection
73 13 CHECKLISTS I Tbl_Checklists_InputSelection
9 13 SLAGPOTS: Add New B Tbl_Slagpots
11 13 SLAGPOTS: Edit By Date B Tbl_Slagpots_EditByDate
10 13 SLAGPOTS: Edit By Heat B Tbl_Slagpots_EditByHeat
28 13 SLAGPOTS: Movement Authorisation B Tbl_Slagpots_MoveAuth
51 13 BASKETS: Add New B Tbl_Basket
53 13 BASKETS: Edit Existing B Tbl_Basket_Edit
4 13 MACHINE HOURS: Add / Edit B Tbl_MachineHrs
14 0 MAINTENANCE A
68 14 DIESEL CONSUMPTION B Tbl_DieselConsumption_InputSelection
72 14 JOBCARD MANAGEMENT B Tbl_JOBCARD_InputSelection
58 14 JOBCARDS: New B Tbl_JMS_JobcardsNew
Now, I was thinking on the parent nodes, all those lines' parent nodes is 0, so I was thinking on having an if parentnodeid = 0 then that's the root nodes. etc etc..
Any help will be appreciated, I am desperate... Thanks
Rudi
-
Aug 16th, 2005, 04:51 AM
#2
Thread Starter
Addicted Member
Re: Populate TreeView control from SQL Server Database
Hey guys,
Anyone got any ideas on this? I kinda got a deadline and I kinda need to get this answer
thanks
Rudi
-
Aug 16th, 2005, 10:28 AM
#3
Frenzied Member
Re: Populate TreeView control from SQL Server Database
This is a sub I wrote to populate a treeview containing a set of parent nodes and a set of child nodes from a dataset.
Have a look and ask questions if you want.
VB Code:
Private Sub BindtvwOnWaterRails()
Try
Dim containerNode As TreeNode
Dim unitNode As TreeNode
Dim searchNode As TreeNode
Dim dbRow As DataRow
For Each dbRow In objDatabaseClass.dsSelectContainers.Tables("Containers").Rows
containerNode = tvwOnWaterRails.Nodes.Add(dbRow("ContainerID"))
containerNode.Tag = dbRow("ContainerID") & "C"
containerNode.ImageIndex = 5
Next
For Each dbRow In objDatabaseClass.dsSelectRailsOnWater.Tables("RailsOnWater").Rows
For Each searchNode In tvwOnWaterRails.Nodes
If CStr(searchNode.Tag).EndsWith("C") Then
If Not IsDBNull(dbRow("ContainerID")) Then
If Val(searchNode.Tag) = dbRow("ContainerID") Then
unitNode = searchNode.Nodes.Add(dbRow("RailNo"))
unitNode.Tag = dbRow("RailNo") & "U"
unitNode.ImageIndex = 4
End If
End If
End If
Next
Next
Catch ex As System.Exception
MessageBox.Show("There has been an error: " & vbCrLf & "This occurred in MainInterface Class Sub BindlstOnWaterRails" & vbCrLf & ex.Message & vbCrLf & "If the problem continues contact your IT department or the software vendor", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
-
Aug 17th, 2005, 01:42 AM
#4
Thread Starter
Addicted Member
Re: Populate TreeView control from SQL Server Database
THAAAAANNNKKK YYYOOOOOUUUUU!!!!!
Wow, I battled with that over a week and all I needed was someone to give me some ideas. Thanks Fish Guy. I've still got one question.. Is it possible to add a tooltip message to each node? When the user hovers over the item, I want it to display a short message on that item, this is captured in the table aswell.
Then, In the table There is a field named ARGUMENT, This keeps the name of the form which needs to be opened, How will my code look on an afterSelect event to open a form as a child mdi form?
Thanks alot for all the help thusfar, this is what my code looks like:
VB Code:
Private Sub FillTreeView()
Try
Dim containerNode As TreeNode
Dim unitNode As TreeNode
Dim searchNode As TreeNode
Dim dbRow As DataRow
For Each dbRow In TreeData.Qry_Admin_TreeMenu_VBNET.Select("", "SORTFIELD")
If dbRow("PARENTNODEID") = 0 Then
containerNode = JMSTreeView.Nodes.Add(dbRow("LABEL"))
containerNode.Tag = dbRow("NODEID") & "R"
containerNode.ImageIndex = dbRow("IMAGEINDEX")
End If
Next
For Each dbRow In TreeData.Qry_Admin_TreeMenu_VBNET.Select("", "SORTFIELD")
For Each searchNode In JMSTreeView.Nodes
If CStr(searchNode.Tag).EndsWith("R") Then
If Not IsDBNull(dbRow("NODEID")) Then
If Val(searchNode.Tag) = dbRow("PARENTNODEID") Then
unitNode = searchNode.Nodes.Add(dbRow("LABEL"))
unitNode.Tag = dbRow("NODEID") & "U"
unitNode.ImageIndex = dbRow("IMAGEINDEX")
End If
End If
End If
Next
Next
Catch ex As System.Exception
MessageBox.Show("There has been an error: " & vbCrLf & "This occurred in MainInterface Class Sub BindlstOnWaterRails" & vbCrLf & ex.Message & vbCrLf & "If the problem continues contact your IT department or the software vendor", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
-
Aug 17th, 2005, 03:59 AM
#5
Frenzied Member
Re: Populate TreeView control from SQL Server Database
not so sure what you are wanting..
Dont know much about tool tips im afraid
This will put the selected nodes tag in a string
VB Code:
Private Sub JMSTreeView_AfterSelect(ByVal sender As Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles tvwOnWaterCarriages.AfterSelect
'Get File name
Dim strSQL as string
strSQL ="Select ARGUMENT from tbl_Name where ID = "& CStr(e.Node.Tag) &"
End Sub
might get you started dont forget you can use substring() to get parts of the tag e.Node.Text can also be used.
I would not really be executing another sql on every select though, I would bring back the data with the first data set then on select loop through the dataset like this to get a file name from it
VB Code:
For intLoopIndex As Integer = 0 To (objDatabaseClass.ddsSelectCarriagesOnWater.Tables("CarriagesOnWater").Rows.Count) - 1
dim FileToOpen as string
if objDatabaseClass.dsSelectCarriagesOnWater.Tables("CarriagesOnWater").Rows(intLoopIndex).Item(0) = a.node.tag then
FileToOpen = (objDatabaseClass.ddsSelectCarriagesOnWater.Tables("CarriagesOnWater").Rows(intLoopIndex).Item(1).ToString)
End if
Next intLoopIndex
Item(0) is the ID column
Item(1) is the column with the file name in
Hope its of some help.
-
Aug 17th, 2005, 04:36 AM
#6
Thread Starter
Addicted Member
Re: Populate TreeView control from SQL Server Database
Hi mr Fish,
Hi, Ok, like in your code, I saw I don't need to add the R, or U after the tag, the If PARENTNODEID = 0 statement resolves that for me... yay!
Once I can only get the argument from the field, this data will look something like:
NODEID ARGUMENT
6 FrmEmployees
7 FrmClasses
8 FrmWaterWorks
etc or whatever,
Then I was thinking to have an afterselect event where the application opens the selected form in my application. So I was thinking something like:
VB Code:
Dim strSQL As String
strSQL = "Select ARGUMENT from tbl_Admin_Treemenu_VBNET where NODEID =" & CStr(e.Node.Tag) & ""
dim strSQL as new ChildForm
StrSQL.show()
The only thing that's bothering me at the moment is: When I say summin like
textbox1.text = strSQL
It returns the actual string, and not the value, how do I get the value?
You think something like that will work?
Thanks
Rudi
-
Aug 17th, 2005, 04:53 AM
#7
Frenzied Member
Re: Populate TreeView control from SQL Server Database
No that wont work, to do that you would have to totally rerun the sql and store the data somewhere. i.e new sql command new data adapter new dataset then sqlcommand1.command text = strSQL
sqlcommand1.execute
all the usual.
Thats why I said if you returned the data frmName with the data you had origionally you could just loop throgu your dataset to retrieve it.
-
Aug 17th, 2005, 04:58 AM
#8
Thread Starter
Addicted Member
Re: Populate TreeView control from SQL Server Database
Ok, but where would I store that data? That data is retuned in the dataset, all the columns of the table is returned in the dataset. How can I use that dataset then?
I mean, The argument is included in my original dataset that was used for the tree view.
Last edited by Tjoppie; Aug 17th, 2005 at 05:03 AM.
-
Aug 17th, 2005, 05:04 AM
#9
Frenzied Member
Re: Populate TreeView control from SQL Server Database
This loops through a dataset and when item(0) matches the tag it sets the variable FileToOpen to the value in the dataset in column 2 (item(1)).
VB Code:
For intLoopIndex As Integer = 0 To (DataSet1.Tables("DataTable1").Rows.Count) - 1
dim FileToOpen as string
if DataSet1.Tables("DataTable1").Rows(intLoopIndex).Item(0) = a.node.tag then
FileToOpen = (DataSet1.Tables("DataTable1").Rows(intLoopIndex).Item(1).ToString)
End if
Next intLoopIndex
'Show this file = FileToOpen
-
Aug 17th, 2005, 05:31 AM
#10
Thread Starter
Addicted Member
Re: Populate TreeView control from SQL Server Database
Hey... You are great... That worked awesomely!
Btw... I took quite some planning before starting with this application.. I know I'm going to have to retrieve records from the sql database should I do it the same way, or is there a better way?
For eg:
My application was an ms access project before now recreating it in vb.net... Normally when I wanted to create a value, I used the following code:
Code:
Private Sub ACCOUNTID_AfterUpdate()
bulookup = DLookup("[BUSUNIT]", "Tbl_EquipmentRegister", "[EQUIPMENTID] ='" & Me.ASSETID & "'")
AccountLookup = DLookup("[ACCOUNTID]", "Tbl_JMS_Requisitions_AccountCodes", "[ACCID] ='" & Me.ACCOUNTID & "'")
txtAccountIDMsg.Value = bulookup & "." & AccountLookup
End Sub
What this does, is on the form, the user has got a list box of values, and all the code does is it extracts what's the account code. It's in exactly the same table as the list box, it just gets the value for viewing....
How will I do that in vb.net?
Thanks
Rudi
-
Aug 17th, 2005, 06:16 AM
#11
Frenzied Member
Re: Populate TreeView control from SQL Server Database
This is how I set the values of a combo box in code
VB Code:
Me.cboLocation.DataSource = dataset1
Me.cboLocation.DisplayMember = "TableName1.ColumnName2"
Me.cboLocation.ValueMember = "TableName1.ColumnName1"
Again though I think you are wanting to filter out certain records, perhaps you should agin look at looping through your data to get the reords you need and perhaps store them in a 2d array. Then bind the combo to the array/collection
-
Aug 17th, 2005, 04:18 PM
#12
Fanatic Member
Re: Populate TreeView control from SQL Server Database
You might be interested to visit this link. Subclassing Treeviews
It shows how you can subclass the TreeNode class and then add any custom properties that you can pick up when you click a node.
Using VB.NET 2003/.NET 1.1/C# 2.0
http://del.icio.us/rajoo
Blow your mind, smoke gunpowder
Ashes to ashes, dust to dust
If God won't have you, the devil will. - Author unknown
Don't follow me, I'm lost too ...
-
Aug 17th, 2005, 11:40 PM
#13
Thread Starter
Addicted Member
Re: Populate TreeView control from SQL Server Database
Thanks Mr No,
I'll have a look at it.
Rudi
-
Mar 25th, 2006, 11:51 AM
#14
Addicted Member
Re: Populate TreeView control from SQL Server Database
New to this site but landd on this tool.
Thanks FishGuy. This saved me a great deal. But i have a question.
Suppose I want to put a heading on my first Nodes,how do i go about it?
I am trying to impliment my Qtn but still failing. Below is my code.
I included the snippet that is red and thats where the error comes from.
Private Sub BindtvwMyTreeView()
Dim strSectorsSQL As String = "SELECT * FROM SectorFields"
Dim strSubSectorSQL As String = "Select * FROM SubSectorFields"
Dim dsSectors As New DataSet
Dim dsSubSectors As New DataSet
dsSectors = connMger.ReturnOleDbData(strAccessConn, strSectorsSQL, "SectorFields")
dsSubSectors = connMger.ReturnOleDbData(strAccessConn, strSubSectorSQL, "SubSectorFields")
Try
Dim containerNode As TreeNode
Dim unitNode As TreeNode
Dim searchNode As TreeNode
Dim dbRow As DataRow
Me.tvwSectors.Nodes.Add(New TreeNode("SECTORS"))
For Each dbRow In dsSectors.Tables("SectorFields").Rows
containerNode = tvwSectors.Nodes.Add(dbRow("SECTOR"))
containerNode.Tag = dbRow("SectorID") & "C"
containerNode.ImageIndex = 5
Next
For Each dbRow In dsSubSectors.Tables("SubSectorFields").Rows
For Each searchNode In tvwSectors.Nodes
If CStr(searchNode.Tag).EndsWith("C") Then
If Not IsDBNull(dbRow("SectorID")) Then
If Val(searchNode.Tag) = dbRow("SectorID") Then
unitNode = searchNode.Nodes.Add(dbRow("SUB_SECTOR"))
unitNode.Tag = dbRow("SUB_SECTOR") & "U"
unitNode.ImageIndex = 4
End If
End If
End If
Next
Next
Catch ex As System.Exception
MsgBox("Error!" & ex.Message)
End Try
End Sub
Last edited by skea; Mar 26th, 2006 at 01:18 AM.
-
Mar 26th, 2006, 03:28 AM
#15
Addicted Member
Re: [RESOLVED] Populate TreeView control from SQL Server Database
Ok. This is what i mean. I have two images below TreeView1 and TreeView2
I want my tree to have the sectors protruding from the Header(SECTORS) as in TreeView2 and not as in TreeView1 and the Sub-Sectors protruding from the Sectors as in TreeView1.But if i implement the code below,my subsectors dont show.
Here is the the way i am trying to implement it, but i suppose i am missing something.Please Help.
VB Code:
Private Sub BindtvwMyTreeView()
Dim strSectorsSQL As String = "SELECT * FROM SectorFields"
Dim strSubSectorSQL As String = "Select * FROM SubSectorFields"
Dim dsSectors As New DataSet
Dim dsSubSectors As New DataSet
dsSectors = connMger.ReturnOleDbData(strAccessConn, strSectorsSQL, "SectorFields")
dsSubSectors = connMger.ReturnOleDbData(strAccessConn, strSubSectorSQL, "SubSectorFields")
Try
Dim HeaderNode As TreeNode
Dim parentNode As TreeNode
Dim childNode As TreeNode
Dim searchIDNode As TreeNode
Dim dbRow As DataRow
HeaderNode = tvwSectors.Nodes.Add("SECTORS")
HeaderNode.Tag = "H"
For Each dbRow In dsSectors.Tables("SectorFields").Rows
For Each searchIDNode In tvwSectors.Nodes
If CStr(searchIDNode.Tag) = "H" Then
parentNode = searchIDNode.Nodes.Add(dbRow("SECTOR"))
parentNode.Tag = dbRow("SectorID") '& "C"
parentNode.ImageIndex = 0
End If
Next
Next
For Each dbRow In dsSubSectors.Tables("SubSectorFields").Rows
For Each searchIDNode In tvwSectors.Nodes
'If CStr(searchIDNode.Tag).EndsWith("C") Then
'If Not IsDBNull(parentRow("SectorID")) Then
If Val(searchIDNode.Tag) = dbRow("SectorID") Then
childNode = searchIDNode.Nodes.Add(dbRow("SUB_SECTOR"))
childNode.Tag = dbRow("SUB_SECTOR") ' & "U"
childNode.ImageIndex = 1
End If
'End If
'End If
Next
Next
HeaderNode = tvwSectors.Nodes.Add("POPULATION")
HeaderNode.Tag = "P"
Catch ex As System.Exception
MsgBox("Error!" & ex.Message)
End Try
End Sub
Last edited by skea; Mar 26th, 2006 at 03:34 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|