dim sIDs as string dim bChange as boolean dim rs as adodb.recordsetsids="0"rs.open "select * from tblname order by addno",cnwith rs while not .eof if instr(sids ,trim(str(.field!paddno))) <>0 then sids=sids & "," & trim(str(.field!paddno)) endif wend end withrs.closeps: 1.在返回记录集时用了order by 排序,这样省了再查找的麻烦。 2.如果记录很多的话,instr函数会影响查询速度,就是说适用于记录集比较小的情况下.
写完了,也看到你的要求了,上面的不能用在填treeview. 下面的可以: Dim i As Long Dim x As Node tvwDepartment.Nodes.Clear Call tvwDepartment.Nodes.Add(, , "ID0", CorporationName) With rs .Open "select * from tblname", cn
While Not .EOF Set x = tvwDepartment.Nodes.Add(, , "ID" & .Field!addno, Field!Text) x.Tag = .Field!paddno x.Expanded = True Wend
.Close End With
For i = 2 To tvwDepartment.Nodes.Count Set tvwDepartment.Nodes(i).Parent = tvwDepartment.Nodes("ID" & x.Tag) Next i
SQL Server UDF 可解决:select depid,dbo.GetLevel(depid) from departmentcreate FUNCTION dbo.GetLevel (@Node VARCHAR(10)) RETURNS VARCHAR(250) AS BEGIN DECLARE @Temp VARCHAR(250) DECLARE @vGetLevel VARCHAR(250) SELECT TOP 1 @Temp = AdminID FROM Department WHERE depid=@Node IF @Temp <> @Node OR LEN(LTRIM(RTRIM(@Temp)))<=0 OR LEN(LTRIM(RTRIM(@Node)))<=0 BEGIN SELECT @vGetLevel = ISNULL(dbo.GetLevel(@Temp),'') + '-' + ISNULL(@Temp,'') END ELSE SET @vGetLevel = @Temp RETURN @vGetLevel END
select AddNo,dbo.GetLevel(AddNo) from departmentcreate FUNCTION dbo.GetLevel (@Node VARCHAR(10)) RETURNS VARCHAR(250) AS BEGIN DECLARE @Temp VARCHAR(250) DECLARE @vGetLevel VARCHAR(250) SELECT TOP 1 @Temp = PAddNo FROM Department WHERE AddNo=@Node IF @Temp <> @Node OR LEN(LTRIM(RTRIM(@Temp)))<=0 OR LEN(LTRIM(RTRIM(@Node)))<=0 BEGIN SELECT @vGetLevel = ISNULL(dbo.GetLevel(@Temp),'') + '-' + ISNULL(@Temp,'') END ELSE SET @vGetLevel = @Temp RETURN @vGetLevel END
dim bChange as boolean
dim rs as adodb.recordsetsids="0"rs.open "select * from tblname order by addno",cnwith rs
while not .eof
if instr(sids ,trim(str(.field!paddno))) <>0 then
sids=sids & "," & trim(str(.field!paddno))
endif
wend
end withrs.closeps:
1.在返回记录集时用了order by 排序,这样省了再查找的麻烦。
2.如果记录很多的话,instr函数会影响查询速度,就是说适用于记录集比较小的情况下.
下面的可以:
Dim i As Long
Dim x As Node tvwDepartment.Nodes.Clear
Call tvwDepartment.Nodes.Add(, , "ID0", CorporationName)
With rs
.Open "select * from tblname", cn
While Not .EOF
Set x = tvwDepartment.Nodes.Add(, , "ID" & .Field!addno, Field!Text)
x.Tag = .Field!paddno
x.Expanded = True
Wend
.Close
End With
For i = 2 To tvwDepartment.Nodes.Count
Set tvwDepartment.Nodes(i).Parent = tvwDepartment.Nodes("ID" & x.Tag)
Next i
AddNo:代表地址号(也就是族号的意思)
PAddNo:代表上一级的目录的地址的意思你的算法我明白。
谢谢。。继续:)
from departmentcreate FUNCTION dbo.GetLevel
(@Node VARCHAR(10))
RETURNS VARCHAR(250)
AS
BEGIN
DECLARE @Temp VARCHAR(250)
DECLARE @vGetLevel VARCHAR(250)
SELECT TOP 1 @Temp = AdminID
FROM Department
WHERE depid=@Node
IF @Temp <> @Node OR LEN(LTRIM(RTRIM(@Temp)))<=0 OR LEN(LTRIM(RTRIM(@Node)))<=0
BEGIN
SELECT @vGetLevel = ISNULL(dbo.GetLevel(@Temp),'') + '-' + ISNULL(@Temp,'')
END
ELSE SET @vGetLevel = @Temp
RETURN @vGetLevel
END
from departmentcreate FUNCTION dbo.GetLevel
(@Node VARCHAR(10))
RETURNS VARCHAR(250)
AS
BEGIN
DECLARE @Temp VARCHAR(250)
DECLARE @vGetLevel VARCHAR(250)
SELECT TOP 1 @Temp = PAddNo
FROM Department
WHERE AddNo=@Node
IF @Temp <> @Node OR LEN(LTRIM(RTRIM(@Temp)))<=0 OR LEN(LTRIM(RTRIM(@Node)))<=0
BEGIN
SELECT @vGetLevel = ISNULL(dbo.GetLevel(@Temp),'') + '-' + ISNULL(@Temp,'')
END
ELSE SET @vGetLevel = @Temp
RETURN @vGetLevel
END
我写的第二个是用了treeview根据node之间的父子关系自动排序的特点。