Results 1 to 15 of 15

Thread: [RESOLVED] Populate TreeView control from SQL Server Database

  1. #1

    Thread Starter
    Addicted Member Tjoppie's Avatar
    Join Date
    Aug 2005
    Location
    South Africa
    Posts
    241

    Resolved [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

  2. #2

    Thread Starter
    Addicted Member Tjoppie's Avatar
    Join Date
    Aug 2005
    Location
    South Africa
    Posts
    241

    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

  3. #3
    Frenzied Member FishGuy's Avatar
    Join Date
    Mar 2005
    Location
    Bradford UK
    Posts
    1,708

    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:
    1. Private Sub BindtvwOnWaterRails()
    2.         Try
    3.          
    4.  
    5.             Dim containerNode As TreeNode
    6.             Dim unitNode As TreeNode
    7.             Dim searchNode As TreeNode
    8.             Dim dbRow As DataRow
    9.  
    10.             For Each dbRow In objDatabaseClass.dsSelectContainers.Tables("Containers").Rows
    11.                 containerNode = tvwOnWaterRails.Nodes.Add(dbRow("ContainerID"))
    12.                 containerNode.Tag = dbRow("ContainerID") & "C"
    13.                 containerNode.ImageIndex = 5
    14.             Next
    15.  
    16.             For Each dbRow In objDatabaseClass.dsSelectRailsOnWater.Tables("RailsOnWater").Rows
    17.                 For Each searchNode In tvwOnWaterRails.Nodes
    18.                     If CStr(searchNode.Tag).EndsWith("C") Then
    19.  
    20.                         If Not IsDBNull(dbRow("ContainerID")) Then
    21.                             If Val(searchNode.Tag) = dbRow("ContainerID") Then
    22.                                 unitNode = searchNode.Nodes.Add(dbRow("RailNo"))
    23.                                 unitNode.Tag = dbRow("RailNo") & "U"
    24.                                 unitNode.ImageIndex = 4
    25.                             End If
    26.                         End If
    27.                     End If
    28.  
    29.                 Next
    30.             Next
    31.         Catch ex As System.Exception
    32.             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)
    33.         End Try
    34.     End Sub

  4. #4

    Thread Starter
    Addicted Member Tjoppie's Avatar
    Join Date
    Aug 2005
    Location
    South Africa
    Posts
    241

    Resolved 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:
    1. Private Sub FillTreeView()
    2.         Try
    3.             Dim containerNode As TreeNode
    4.             Dim unitNode As TreeNode
    5.             Dim searchNode As TreeNode
    6.             Dim dbRow As DataRow
    7.  
    8.             For Each dbRow In TreeData.Qry_Admin_TreeMenu_VBNET.Select("", "SORTFIELD")
    9.                 If dbRow("PARENTNODEID") = 0 Then
    10.                     containerNode = JMSTreeView.Nodes.Add(dbRow("LABEL"))
    11.                     containerNode.Tag = dbRow("NODEID") & "R"
    12.                     containerNode.ImageIndex = dbRow("IMAGEINDEX")
    13.                 End If
    14.             Next
    15.  
    16.             For Each dbRow In TreeData.Qry_Admin_TreeMenu_VBNET.Select("", "SORTFIELD")
    17.                 For Each searchNode In JMSTreeView.Nodes
    18.                     If CStr(searchNode.Tag).EndsWith("R") Then
    19.  
    20.                         If Not IsDBNull(dbRow("NODEID")) Then
    21.                             If Val(searchNode.Tag) = dbRow("PARENTNODEID") Then
    22.                                 unitNode = searchNode.Nodes.Add(dbRow("LABEL"))
    23.                                 unitNode.Tag = dbRow("NODEID") & "U"
    24.                                 unitNode.ImageIndex = dbRow("IMAGEINDEX")
    25.                             End If
    26.                         End If
    27.                     End If
    28.  
    29.                 Next
    30.             Next
    31.         Catch ex As System.Exception
    32.             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)
    33.         End Try
    34.     End Sub

  5. #5
    Frenzied Member FishGuy's Avatar
    Join Date
    Mar 2005
    Location
    Bradford UK
    Posts
    1,708

    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:
    1. Private Sub JMSTreeView_AfterSelect(ByVal sender As Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles tvwOnWaterCarriages.AfterSelect
    2. 'Get File name
    3. Dim strSQL as string    
    4. strSQL ="Select ARGUMENT from tbl_Name where ID = "& CStr(e.Node.Tag) &"
    5.        
    6.     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:
    1. For intLoopIndex As Integer = 0 To (objDatabaseClass.ddsSelectCarriagesOnWater.Tables("CarriagesOnWater").Rows.Count) - 1
    2.  dim  FileToOpen  as string          
    3. if objDatabaseClass.dsSelectCarriagesOnWater.Tables("CarriagesOnWater").Rows(intLoopIndex).Item(0) = a.node.tag then
    4.   FileToOpen = (objDatabaseClass.ddsSelectCarriagesOnWater.Tables("CarriagesOnWater").Rows(intLoopIndex).Item(1).ToString)
    5. End if
    6.             Next intLoopIndex
    Item(0) is the ID column
    Item(1) is the column with the file name in

    Hope its of some help.

  6. #6

    Thread Starter
    Addicted Member Tjoppie's Avatar
    Join Date
    Aug 2005
    Location
    South Africa
    Posts
    241

    Question 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:
    1. Dim strSQL As String
    2.         strSQL = "Select ARGUMENT from tbl_Admin_Treemenu_VBNET where NODEID =" & CStr(e.Node.Tag) & ""
    3.  
    4. dim strSQL as new ChildForm
    5. 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

  7. #7
    Frenzied Member FishGuy's Avatar
    Join Date
    Mar 2005
    Location
    Bradford UK
    Posts
    1,708

    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.

  8. #8

    Thread Starter
    Addicted Member Tjoppie's Avatar
    Join Date
    Aug 2005
    Location
    South Africa
    Posts
    241

    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.

  9. #9
    Frenzied Member FishGuy's Avatar
    Join Date
    Mar 2005
    Location
    Bradford UK
    Posts
    1,708

    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:
    1. For intLoopIndex As Integer = 0 To (DataSet1.Tables("DataTable1").Rows.Count) - 1
    2.  dim  FileToOpen  as string          
    3. if DataSet1.Tables("DataTable1").Rows(intLoopIndex).Item(0) = a.node.tag then
    4.   FileToOpen = (DataSet1.Tables("DataTable1").Rows(intLoopIndex).Item(1).ToString)
    5. End if
    6.             Next intLoopIndex
    7. 'Show this file = FileToOpen

  10. #10

    Thread Starter
    Addicted Member Tjoppie's Avatar
    Join Date
    Aug 2005
    Location
    South Africa
    Posts
    241

    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

  11. #11
    Frenzied Member FishGuy's Avatar
    Join Date
    Mar 2005
    Location
    Bradford UK
    Posts
    1,708

    Re: Populate TreeView control from SQL Server Database

    This is how I set the values of a combo box in code
    VB Code:
    1. Me.cboLocation.DataSource = dataset1
    2.             Me.cboLocation.DisplayMember = "TableName1.ColumnName2"
    3.             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

  12. #12
    Fanatic Member Mr.No's Avatar
    Join Date
    Sep 2002
    Location
    Mauritius
    Posts
    651

    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 ...

  13. #13

    Thread Starter
    Addicted Member Tjoppie's Avatar
    Join Date
    Aug 2005
    Location
    South Africa
    Posts
    241

    Re: Populate TreeView control from SQL Server Database

    Thanks Mr No,
    I'll have a look at it.

    Rudi

  14. #14
    Addicted Member skea's Avatar
    Join Date
    Mar 2006
    Posts
    187

    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.

  15. #15
    Addicted Member skea's Avatar
    Join Date
    Mar 2006
    Posts
    187

    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:
    1. Private Sub BindtvwMyTreeView()
    2.         Dim strSectorsSQL As String = "SELECT * FROM SectorFields"
    3.         Dim strSubSectorSQL As String = "Select * FROM SubSectorFields"
    4.         Dim dsSectors As New DataSet
    5.         Dim dsSubSectors As New DataSet
    6.         dsSectors = connMger.ReturnOleDbData(strAccessConn, strSectorsSQL, "SectorFields")
    7.         dsSubSectors = connMger.ReturnOleDbData(strAccessConn, strSubSectorSQL, "SubSectorFields")
    8.         Try
    9.             Dim HeaderNode As TreeNode
    10.             Dim parentNode As TreeNode
    11.             Dim childNode As TreeNode
    12.             Dim searchIDNode As TreeNode
    13.             Dim dbRow As DataRow
    14.  
    15.             HeaderNode = tvwSectors.Nodes.Add("SECTORS")
    16.             HeaderNode.Tag = "H"
    17.             For Each dbRow In dsSectors.Tables("SectorFields").Rows
    18.                 For Each searchIDNode In tvwSectors.Nodes
    19.                     If CStr(searchIDNode.Tag) = "H" Then
    20.                         parentNode = searchIDNode.Nodes.Add(dbRow("SECTOR"))
    21.                         parentNode.Tag = dbRow("SectorID") '& "C"
    22.                         parentNode.ImageIndex = 0
    23.                     End If
    24.                 Next
    25.             Next
    26.  
    27.             For Each dbRow In dsSubSectors.Tables("SubSectorFields").Rows
    28.                 For Each searchIDNode In tvwSectors.Nodes
    29.                     'If CStr(searchIDNode.Tag).EndsWith("C") Then
    30.                     'If Not IsDBNull(parentRow("SectorID")) Then
    31.                     If Val(searchIDNode.Tag) = dbRow("SectorID") Then
    32.                         childNode = searchIDNode.Nodes.Add(dbRow("SUB_SECTOR"))
    33.                         childNode.Tag = dbRow("SUB_SECTOR") ' & "U"
    34.                         childNode.ImageIndex = 1
    35.                     End If
    36.                     'End If
    37.                     'End If
    38.                 Next
    39.             Next
    40.  
    41.             HeaderNode = tvwSectors.Nodes.Add("POPULATION")
    42.             HeaderNode.Tag = "P"
    43.         Catch ex As System.Exception
    44.             MsgBox("Error!" & ex.Message)
    45.         End Try
    46.     End Sub
    Attached Images Attached Images   
    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
  •  



Click Here to Expand Forum to Full Width