- Foxtable(狐表) (http://foxtable.net/bbs/index.asp)
-- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2)
---- 如何做一个多表通用筛选查询呢？根据不用的表名，自动匹配对应的表字段去进行模糊字符查询！ (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=133895)
-- 发布时间：2019/4/23 11:51:00
-- 如何做一个多表通用筛选查询呢？根据不用的表名，自动匹配对应的表字段去进行模糊字符查询！
如何做一个多表通用筛选查询呢？
根据不用的表名，自动匹配对应的表字段去进行模糊字符查询！
【下面红色部分代码怎么完善呢？】
此主题相关图片如下：1.png
选择表组合框enter事件代码
Dim cmb As WinForm.ComboBox
cmb = Forms("通用查询").Controls("选择表")
cmb.ComboList = DataTables("表名").sqlGetComboListString("表名")
选择表组合框值改变后代码
Dim tb1 As WinForm.Table = e.Form.Controls("Table1")
Dim c1 As WinForm.ComboBox = e.Form.Controls("选择表")
Dim cmd1 As New SQLCommand
Dim dt1 As DataTable
cmd1.ConnectionName = _data
If c1.value="参数表" Then
cmd1.CommandText = "S ELECT * From {" & c1.Value & "} where 使用单位= \'" & _usergroup & "\'"
Else If c1.value="法律法规" Or c1.value="行政区域" Or c1.value="列项设置" Or c1.value="信息模板" Then
cmd1.CommandText = "S ELECT * From {" & c1.Value & "}"
Else If c1.value="职务" Or c1.value="人员关联表" Or c1.value="告知内容" Or c1.value="案件基本信息" Then
cmd1.CommandText = "S ELECT * From {" & c1.Value & "} where 操作单位= \'" & _usergroup & "\'"
Else If c1.value="systemuser" Or c1.value="干警信息" Then
cmd1.CommandText = "S ELECT * From {" & c1.Value & "} where 所在单位= \'" & _usergroup & "\'"
End If
dt1 = cmd1.ExecuteReader()
tb1.Table.DataSource =dt1
筛选按钮代码：
Dim c1 As WinForm.ComboBox = e.Form.Controls("选择表")
Dim Filter As String
With e.Form.Controls("输入内容")
If .Value IsNot Nothing Then
Filter = "涉嫌案由 Like \'%" & .Value & "%\' or 嫌疑人姓名 Like \'%" & .Value & "%\' or 承办人 Like \'%" & .Value & "%\' or 部门受案号 Like \'%" & .Value & "%\'"
End If
End With
If Filter > "" Then
Tables("通用查询_table1").Filter = Filter
End If
-- 发布时间：2019/4/23 12:03:00
--
参考
Dim filter As String = "1=2"
For Each c As Col In Tables("表A").Cols
filter &= " or convert(" & c.name & ",\'System.String\') like \'%" & 123 & "%\'"
Next
msgbox(filter)