- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- [求助]lsy老师在不，帮看下代码 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=49616)
-- 发布时间：2014/4/21 14:27:00
-- [求助]lsy老师在不，帮看下代码
查询有点小问题，帮看下。如图所示，筛选性别时重复出现男、女：
此主题相关图片如下：111.jpg
代码如下：
Dim cxlb As WinForm.ComboBox = e.Form.Controls("查询类别")
Dim cxny As WinForm.ComboBox = e.Form.Controls("查询内容")
Dim xsxm As WinForm.CheckedComboBox = e.Form.Controls("显示项目")
Dim zym As WinForm.TabControl = e.Form.Controls("总页面")
Dim sjcxdh As WinForm.NavBar = e.Form.Controls("数据查询导航")
\'Dim sjcxdh As WinForm.NavBar = e.Form.Controls("数据维护导航")
Dim sjcxym As WinForm.TabControl = e.Form.Controls("数据查询页面")
Dim sjwhym As WinForm.TabControl = e.Form.Controls("数据维护页面")
Dim zyglym As WinForm.TabControl = e.Form.Controls("执业管理页面")
Dim tv As WinForm.TreeView = e.Form.Controls("TreeView1")
Dim s As String
Dim t As Table
Dim filt As String
Dim flt As String
Dim dr As DataRow
Dim dt As DataTable
Select cxlb.Value
Case "人员信息"
Select cxny.Value
Case "基层法律服务工作者"
\'sjcxym.TabPages("基层法律服务工作者查询").Visible = True
\'sjcxym.SelectedPage = zym.TabPages("基层法律服务工作者查询")
sjcxym.SelectedIndex=3
s = e.Form.Controls("显示项目").Text
If s = "" OrElse s = "选择显示项目" Then
Return
End If
t = Tables("基层法律服务人员信息表")
\'dr = DataTables("司法行政机关表").SQLFind("市级机关 = \'" & User.Group & "\'")
\'If dr IsNot Nothing Then
If User.Group="遵义市司法局" Then
filt = "1 = 1"
Else
filt = "主管司法局 = \'" & User.Group & "\'"
End If
Dim cmd As New SQLCommand
cmd.C
cmd.CommandText = "Select 主管司法局,法律服务所,性别,姓名,序号,IIF(年龄 Between 20 And 25,\'20-25\',IIF(年龄 Between 26 And 30, \'26-30\',IIF(年龄 Between 31 And 35, \'31-35\'," & _
"IIF(年龄 Between 36 And 40,\'36-40\',IIF(年龄 Between 41 And 45,\'41-45\',IIF(年龄 Between 46 And 50,\'46-50\',\'小于20大于50\')))))) As 年龄" & _
" From {基层法律服务人员信息表} Where " & filt
dt = cmd.ExecuteReader()
If s.StartsWith("年龄") Then
e.Form.Controls("显示项目").Text = "年龄|姓名"
tv.BuildTree(dt,"年龄|姓名")
ElseIf s.StartsWith("性别") Then
e.Form.Controls("显示项目").Text = "性别|姓名"
tv.BuildTree(dt,"性别|姓名")
Else
e.Form.Controls("显示项目").Text = s.Replace("年龄|","").Replace("|年龄","") \'加一句,才会与目录树的节点对应
tv.BuildTree(dt,s.Replace("年龄|","").Replace("|年龄","") ,"","序号")
End If
s = e.Form.Controls("显示项目").Text
If tv.Nodes.Count > 0 Then
tv.Nodes.Insert("全部","全部(" & dt.Compute("Count(姓名)") & "人)",0)
End If
\'------------给每个节点统计人数----------
For Each nd As WinForm.TreeNode In tv.AllNodes
If nd.Name <> "全部" Then
If s.StartsWith("年龄") Then
If nd.Name = "小于20大于50" Then
flt = "年龄 < \'20\' Or 年龄 > \'50\'"
Else
If nd.Level = 0 Then
flt = "年龄 >= \'" & nd.Name.Split("-")(0) & "\' And 年龄 <= \'" & nd.Name.Split("-")(1) & "\'"
End If
End If
nd.Text + = "岁(" & dt.Compute("Count(姓名)",flt) & "人)"
Else
flt = ""
For i As Integer = 0 To s.Split("|").Length - 1
For j As Integer = 0 To nd.FullName.Split("\\").Length - 1
If i = j Then
flt + = " And " & s.Split("|")(i) & " = \'" & nd.FullName.Split("\\")(j) & "\'"
End If
Next
Next
nd.Text + = "(" & dt.Compute("Count(姓名)",flt.SubString(5)) & "人)"
End If
If nd.Name = nd.DataRow("姓名") Then
nd.Text = nd.Name
End If
End If
Next
sjcxdh.SelectedPage.HeaderText="基层法律服务工作者信息查询"
End Select
Case "机构信息"
Select cxny.Value
Case "基层法律服务所"
tv.Nodes.Clear
sjcxym.TabPages("基层法律服务所查询").Visible = True
\'sjcxym.SelectedPage = zym.TabPages("基层法律服务所查询")
sjcxym.SelectedIndex=0
s = e.Form.Controls("显示项目").Text
If s = "" OrElse s = "选择显示项目" Then
Return
End If
dt = DataTables("基层法律服务机构信息表")
If s.StartsWith("主管司法局") Then
e.Form.Controls("显示项目").Text = "主管司法局|法律服务所"
tv.BuildTree(dt,"主管司法局|法律服务所","","序号")
ElseIf s.StartsWith("法律服务所") Then
e.Form.Controls("显示项目").Text = "法律服务所"
tv.BuildTree(dt,"法律服务所")
\'Else
\'tv.BuildTree(q,"主管司法局|服务所名称","序号")
\'tv.BuildTree(dt,s,"","序号")
End If
s = e.Form.Controls("显示项目").Text
If tv.Nodes.Count > 0 Then
tv.Nodes.Insert("全部","全部(" & dt.Compute("Count(法律服务所)") & "个)",0)
End If
\'
\'\'给每一个节点统计机构数---------
For Each nd As WinForm.TreeNode In tv.AllNodes
If nd.Name <> "全部" Then
flt = ""
For i As Integer = 0 To s.Split("|").Length - 1
For j As Integer = 0 To nd.FullName.Split("\\").Length - 1
If i = j Then
flt + = " And " & s.Split("|")(i) & " = \'" & nd.FullName.Split("\\")(j) & "\'"
End If
Next
Next
nd.Text + = "(" & dt.Compute("Count(法律服务所)",flt.SubString(5)) & "个)"
If nd.Name = nd.DataRow("法律服务所") Then
nd.Text = nd.Name
End If
End If
Next
sjcxdh.SelectedPage.HeaderText="基层法律服务所查询"
End Select
End Select
For Each c As Winform.Control In sjcxym.SelectedPage.Children
c.Enabled = True
Next
-- 发布时间：2014/4/21 14:29:00
--
这个,能否上个例子呢?
-- 发布时间：2014/4/21 14:38:00
--
好像比原来的例子多了新内容。
复杂的查询，没例子，干瞪眼。
-- 发布时间：2014/4/21 14:59:00
--
好复杂的目录树生成。
-- 发布时间：2014/4/21 15:13:00
--
看了一下，代码应该是不会有错了，数据的问题，试试这样
cmd.CommandText = "Select 主管司法局,法律服务所,Trim(性别) As 性别,姓名,序号,IIF(年龄 Between 20 And 25,\'20-25\',IIF(年龄 Between 26 And 30, \'26-30\',IIF(年龄 Between 31 And 35, \'31-35\'," & _
"IIF(年龄 Between 36 And 40,\'36-40\',IIF(年龄 Between 41 And 45,\'41-45\',IIF(年龄 Between 46 And 50,\'46-50\',\'小于20大于50\')))))) As 年龄" & _
" From {基层法律服务人员信息表} Where " & filt
-- 发布时间：2014/4/21 15:54:00
--
谢谢楼上各位老师。按照有点甜老师的代码，还是有问题呢。如图：
此主题相关图片如下：444.jpg
-- 发布时间：2014/4/21 15:58:00
--
subString(性别,1,1) As 性别
-- 发布时间：2014/4/21 16:05:00
--
|
access数据源或内部表的话：
|
-- 发布时间：2014/4/21 16:06:00
--
狐爸，能否把代码写全点，我把上面的代码替换了，报错呢。
-- 发布时间：2014/4/21 16:07:00
--
楼主的问题，不一定出在已贴出的代码里面。
中心法律服务所，只有一种性别，就没有重复。
旺草法律服务所，女性就没有重复。