程序A: Set Nod = tvw.Nodes.Add Nod.Text = "班组设置" Nod.Key = "T1" Nod.Image = 1SQL = "select * from grouplist" Set Rst = New ADODB.Recordset Rst.CursorLocation = adUseClient Rst.Open SQL, Conn, adOpenStatic, adLockReadOnly, adCmdText If Rst.EOF Then Exit Sub For I = 1 To Rst.RecordCount Set Nod = tvw.Nodes.Add(tvw.Nodes(1).Index, tvwChild, "G" & Rst.Fields(0), Rst.Fields(1), 2, 4) SQL = "select * from emplist where groupid='" & Rst.Fields(0) & "'" Set ChildRst = New ADODB.Recordset ChildRst.CursorLocation = adUseClient ChildRst.Open SQL, Conn, adOpenStatic, adLockReadOnly, adCmdText If Not ChildRst.EOF Then For J = 1 To ChildRst.RecordCount PartStr = IIf(ChildRst.Fields(3) = "", "", " - " & ChildRst.Fields(3)) tvw.Nodes.Add Nod.Index, tvwChild, "P" & ChildRst.Fields(0), ChildRst.Fields(1) & PartStr, 3, 5 ChildRst.MoveNext Next End If Set ChildRst = Nothing Rst.MoveNext Next Set Rst = Nothing Change = False
类似于有四层节点数结构的数据库该如何设计呢? 如果用Access
那么四层节点数结构的数据库又该如何设计呢?如果用Access
多少层都可以: ID Name ParentID -------------------------- 01 Michael 02 Jason 01 03 Tim 02 04 Ben 03
有两种解决的思路,一种是MichaelSoft(MichaelSoft)所说的那样,使用pid的方式,但这种方式有其局限性,即对于溯根的操作非常不好作,即直接通过SQL去检索一个记录的根记录,而不是父记录的时候。而且对于这种结构,因为每一次浏览下一层,就都需要通过sql语句去选择select * from table where pid=xxx,这样,层数一旦多,效率很成问题。但是这种结构的好处是层数不限。而且,对于将这个表装载到treeview当中,也可以利用recordset对象的clone和filter、find来提高效率,但一般情况下,除非真的需要不限定层数,否则不建议去使用这种结构,编码难度较大,而且基本无法做到定位检索某一层的所有数据。 还有另一种结构,就是采用编码叠加的方式,即比如A是B的上级,那么在B的编码(ID)中会包含了A的编码。这种方式下的好处,检索非常简单,而且检索任何一层都非常简单;但缺点也显而易见,就是层数固定,而且每一层所能容纳的元素数有限,两位编码就只能容纳100个元素、3位就只能容纳1000个等。用这种结构,需要把数据添加到treeview中的时候,只需简单的对编码进行排序,然后直接往nodes中添加就行,原理很简单,01肯定排在0101的前面。 具体用哪种,看你的需求吧,但有的时候,将上面两个结构结合在一起也是一个不错的选择。
你问题的关键是:如何取得某父节点下的所有子节点.该例子在MSDN中就有.如果你要对某个父节点的下的所有子节点操作,参考下面函数,也许对你有点帮助. 下面的函数是用来删除所有节点的,你修改一下,可能会适用于你.'/树操作基本参数列表. Type BaseParameter Cnn As ADODB.Connection 'ADODB 连接 TrvName As Object '树名称. TabName As String '树对应的数据表名 ParFld As String '数据表中父节点的字段名. ChildFld As String '数据表中子节点的字段名. TextFld As String '数据表中节点文本名称的字段名. RootIco As String '树中根目录的图标号. Parico As String '树中父节点的图标号. ExpParIco As String '树中展表一个节点时的图标号. ChildIco As String '树中子节点的图标号. RootText As String '树中根节点的文件. End TypeDim TrvBasePar As BaseParameter' '删除某节点下的所有子节点(递归) '函数:KillNodex '参数:SelNode 被删除的节点. '返回值: '说明:该函数既删除树中的节点,亦删除数据库中的节点及其子节点. Public Function KillNodex(SelNode As Object) If SelNode.Key <> SelNode.Root.Key Then
If SelNode.Parent.Children <= 1 And SelNode.Parent.Key <> SelNode.Root.Key Then SelNode.Parent.Image = TrvBasePar.ChildIco SelNode.Parent.ExpandedImage = TrvBasePar.ChildIco End If
Call DelNode(SelNode) TrvBasePar.TrvName.Nodes.Remove (SelNode.Key) End If End FunctionPrivate Function DelNode(NodeX As Node) Dim N As Long Dim StrSql As String Dim MoveNode() As Node Dim AddId As Long Dim TmpNode As Node
With TrvBasePar If Not (TrvBasePar.Cnn Is Nothing) Then StrSql = "DELETE FROM " & .TabName & " WHERE " & .ParFld & "='" & Right$(NodeX.Parent.Key, Len(NodeX.Parent.Key) - 1) & "' AND " & _ .ChildFld & "='" & Right$(NodeX.Key, Len(NodeX.Key) - 1) & "'" TrvBasePar.Cnn.Execute StrSql End If
If NodeX.Children > 0 Then AddId = 0 N = NodeX.Child.Index
If .TrvName.Nodes(N).Children > 0 Then AddId = AddId + 1 ReDim Preserve MoveNode(AddId) Set MoveNode(AddId - 1) = .TrvName.Nodes(N) Else Set TmpNode = .TrvName.Nodes(N) If Not (.Cnn Is Nothing) Then StrSql = "DELETE FROM " & .TabName & " WHERE " & .ParFld & "='" & Right$(TmpNode.Parent.Key, Len(TmpNode.Parent.Key) - 1) & "' AND " & _ .ChildFld & "='" & Right$(TmpNode.Key, Len(TmpNode.Key) - 1) & "'" .Cnn.Execute StrSql End If End If
While N <> NodeX.Child.LastSibling.Index N = .TrvName.Nodes(N).Next.Index If .TrvName.Nodes(N).Children > 0 Then AddId = AddId + 1 ReDim Preserve MoveNode(AddId) Set MoveNode(AddId - 1) = .TrvName.Nodes(N) Else Set TmpNode = .TrvName.Nodes(N) If Not (.Cnn Is Nothing) Then StrSql = "DELETE FROM " & .TabName & " WHERE " & .ParFld & "='" & Right$(TmpNode.Parent.Key, Len(TmpNode.Parent.Key) - 1) & "' AND " & _ .ChildFld & "='" & Right$(TmpNode.Key, Len(TmpNode.Key) - 1) & "'" .Cnn.Execute StrSql End If End If Wend
If AddId > 0 Then For N = 0 To AddId - 1 Call DelNode(MoveNode(N)) Next End If End If
Set Nod = tvw.Nodes.Add
Nod.Text = "班组设置"
Nod.Key = "T1"
Nod.Image = 1SQL = "select * from grouplist"
Set Rst = New ADODB.Recordset
Rst.CursorLocation = adUseClient
Rst.Open SQL, Conn, adOpenStatic, adLockReadOnly, adCmdText
If Rst.EOF Then Exit Sub
For I = 1 To Rst.RecordCount
Set Nod = tvw.Nodes.Add(tvw.Nodes(1).Index, tvwChild, "G" & Rst.Fields(0), Rst.Fields(1), 2, 4)
SQL = "select * from emplist where groupid='" & Rst.Fields(0) & "'"
Set ChildRst = New ADODB.Recordset
ChildRst.CursorLocation = adUseClient
ChildRst.Open SQL, Conn, adOpenStatic, adLockReadOnly, adCmdText
If Not ChildRst.EOF Then
For J = 1 To ChildRst.RecordCount
PartStr = IIf(ChildRst.Fields(3) = "", "", " - " & ChildRst.Fields(3))
tvw.Nodes.Add Nod.Index, tvwChild, "P" & ChildRst.Fields(0), ChildRst.Fields(1) & PartStr, 3, 5
ChildRst.MoveNext
Next
End If
Set ChildRst = Nothing
Rst.MoveNext
Next
Set Rst = Nothing
Change = False
如果用Access
ID Name ParentID
--------------------------
01 Michael
02 Jason 01
03 Tim 02
04 Ben 03
还有另一种结构,就是采用编码叠加的方式,即比如A是B的上级,那么在B的编码(ID)中会包含了A的编码。这种方式下的好处,检索非常简单,而且检索任何一层都非常简单;但缺点也显而易见,就是层数固定,而且每一层所能容纳的元素数有限,两位编码就只能容纳100个元素、3位就只能容纳1000个等。用这种结构,需要把数据添加到treeview中的时候,只需简单的对编码进行排序,然后直接往nodes中添加就行,原理很简单,01肯定排在0101的前面。
具体用哪种,看你的需求吧,但有的时候,将上面两个结构结合在一起也是一个不错的选择。
下面的函数是用来删除所有节点的,你修改一下,可能会适用于你.'/树操作基本参数列表.
Type BaseParameter
Cnn As ADODB.Connection 'ADODB 连接
TrvName As Object '树名称.
TabName As String '树对应的数据表名
ParFld As String '数据表中父节点的字段名.
ChildFld As String '数据表中子节点的字段名.
TextFld As String '数据表中节点文本名称的字段名.
RootIco As String '树中根目录的图标号.
Parico As String '树中父节点的图标号.
ExpParIco As String '树中展表一个节点时的图标号.
ChildIco As String '树中子节点的图标号.
RootText As String '树中根节点的文件.
End TypeDim TrvBasePar As BaseParameter'
'删除某节点下的所有子节点(递归)
'函数:KillNodex
'参数:SelNode 被删除的节点.
'返回值:
'说明:该函数既删除树中的节点,亦删除数据库中的节点及其子节点.
Public Function KillNodex(SelNode As Object)
If SelNode.Key <> SelNode.Root.Key Then
If SelNode.Parent.Children <= 1 And SelNode.Parent.Key <> SelNode.Root.Key Then
SelNode.Parent.Image = TrvBasePar.ChildIco
SelNode.Parent.ExpandedImage = TrvBasePar.ChildIco
End If
Call DelNode(SelNode)
TrvBasePar.TrvName.Nodes.Remove (SelNode.Key)
End If
End FunctionPrivate Function DelNode(NodeX As Node)
Dim N As Long
Dim StrSql As String
Dim MoveNode() As Node
Dim AddId As Long
Dim TmpNode As Node
With TrvBasePar
If Not (TrvBasePar.Cnn Is Nothing) Then
StrSql = "DELETE FROM " & .TabName & " WHERE " & .ParFld & "='" & Right$(NodeX.Parent.Key, Len(NodeX.Parent.Key) - 1) & "' AND " & _
.ChildFld & "='" & Right$(NodeX.Key, Len(NodeX.Key) - 1) & "'"
TrvBasePar.Cnn.Execute StrSql
End If
If NodeX.Children > 0 Then
AddId = 0
N = NodeX.Child.Index
If .TrvName.Nodes(N).Children > 0 Then
AddId = AddId + 1
ReDim Preserve MoveNode(AddId)
Set MoveNode(AddId - 1) = .TrvName.Nodes(N)
Else
Set TmpNode = .TrvName.Nodes(N)
If Not (.Cnn Is Nothing) Then
StrSql = "DELETE FROM " & .TabName & " WHERE " & .ParFld & "='" & Right$(TmpNode.Parent.Key, Len(TmpNode.Parent.Key) - 1) & "' AND " & _
.ChildFld & "='" & Right$(TmpNode.Key, Len(TmpNode.Key) - 1) & "'"
.Cnn.Execute StrSql
End If
End If
While N <> NodeX.Child.LastSibling.Index
N = .TrvName.Nodes(N).Next.Index
If .TrvName.Nodes(N).Children > 0 Then
AddId = AddId + 1
ReDim Preserve MoveNode(AddId)
Set MoveNode(AddId - 1) = .TrvName.Nodes(N)
Else
Set TmpNode = .TrvName.Nodes(N)
If Not (.Cnn Is Nothing) Then
StrSql = "DELETE FROM " & .TabName & " WHERE " & .ParFld & "='" & Right$(TmpNode.Parent.Key, Len(TmpNode.Parent.Key) - 1) & "' AND " & _
.ChildFld & "='" & Right$(TmpNode.Key, Len(TmpNode.Key) - 1) & "'"
.Cnn.Execute StrSql
End If
End If
Wend
If AddId > 0 Then
For N = 0 To AddId - 1
Call DelNode(MoveNode(N))
Next
End If
End If
End With
End Function