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
樓主,你的問題已經解決.留下你的mail,我發源代碼給你,在這上面我一下子也說不清.
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
.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
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