Private Sub Form_Load()
Dim rst As New ADODB.Recordset
Dim rst1 As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim ifieldheader As Integer
Dim i As Integer
Dim bol As Boolean
bol = False
i = 0
Dim a(5) As String
cmd.ActiveConnection = cn
With TreeView1
rst.Open "select 父 from 視圖17 where 父 not in (select 子 from 視圖17) " & _
" or 父 not in (select 子1 from 視圖17) group by 父", cn, adOpenDynamic, adLockPessimistic
While Not rst.EOF
cmd.CommandText = "exec 存儲過程 '" & rst("父") & "'"
Set rst1 = cmd.Execute
While Not rst1.EOF
For ifieldheader = 0 To rst1.Fields.Count - 1
If IIf(bol = False, IIf(IsNull(rst1.Fields(fieldheader + 1).Value) = False, a(ifieldheader) <> rst1.Fields(ifieldheader).Value, True), True) _
And Not IsNull(rst1.Fields(ifieldheader).Value) Then
bol = True
a(ifieldheader) = rst1.Fields(ifieldheader).Value
If i = 0 Then
.Nodes.Add , , "" & rst.Fields(0).Value & "", "" & rst.Fields(0).Value & ""
Else
If Not IsNull(rst1.Fields(ifieldheader).Value) Then
.Nodes.Add "" & rst1.Fields(ifieldheader - 1).Value & "", tvwChild, "" & rst1(ifieldheader).Value & "", "" & rst1(ifieldheader).Value & ""
End If
End If
End If
i = i + 1
Next
bol = False
rst1.MoveNext
Wend
i = 0
bol = False
rst.MoveNext
Wend
rst.Close
End With
End Sub
視圖17即查詢,語句如下:
SELECT dbo.TABLE1.父, dbo.TABLE1.子, TABLE1_1.子 AS 子1, TABLE1_2.子 AS 子2,
TABLE1_3.子 AS 子3
FROM dbo.TABLE1 LEFT OUTER JOIN dbo.TABLE1 TABLE1_2 LEFT OUTER JOIN
dbo.TABLE1 TABLE1_3 ON TABLE1_2.子 = TABLE1_3.父 RIGHT OUTER JOIN
dbo.TABLE1 TABLE1_1 ON TABLE1_2.父 = TABLE1_1.子 ON
dbo.TABLE1.子 = TABLE1_1.父
存儲過程:
CREATE PROCEDURE dbo.存儲過程(@pater nvarchar(4))
AS SELECT dbo.TABLE1.父 AS l0, dbo.TABLE1.子 AS l1, TABLE1_1.子 AS l2,
TABLE1_2.子 AS l3, TABLE1_3.子 AS l4
FROM dbo.TABLE1 LEFT OUTER JOIN
dbo.TABLE1 TABLE1_2 LEFT OUTER JOIN
dbo.TABLE1 TABLE1_3 ON TABLE1_2.子 = TABLE1_3.父 RIGHT OUTER JOIN
dbo.TABLE1 TABLE1_1 ON TABLE1_2.父 = TABLE1_1.子 ON
dbo.TABLE1.子 = TABLE1_1.父
WHERE (dbo.TABLE1.父 LIKE @pater)
GO
with treeview
.nodes.add ,,"pater","親屬鏈"
.nodes("pater").expanded=true
.nodes.add "pater",tvwchild,"child1",D
.nodes.add "child1",tvwchild,"child2",C
.nodes.add "child2",tvwchild,"child3",B
.nodes.add "child3",tvwchild,"child4",A
end with