| 以文本方式查看主题 - Foxtable(狐表) (http://foxtable.net/bbs/index.asp) -- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2) ---- 甜总 这句SQL有啥问题 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=56176) |
| -- 作者：jianjingmaoyi -- 发布时间：2014/8/31 14:21:00 -- 甜总 这句SQL有啥问题 Tables(e.Form.Name & "_Table1").Fill("select* from{企业所得税税收优惠备案管理台账} Where " & Filter ,"服务器",True) Filter 是筛选字符串 这个是没有的. --------------------------- 提示 --------------------------- Select语句执行错误，请检查语法及数据源设置。 --------------------------- 确定 --------------------------- |
| -- 作者：有点甜 -- 发布时间：2014/8/31 14:24:00 -- Filter必须赋初值 = "1=1" |
| -- 作者：jianjingmaoyi -- 发布时间：2014/8/31 14:28:00 -- 不应该这个问题呀 Dim y As Integer = Date.Today.Year Dim dt1 As New Date(y, 1, 1) Dim dt2 As New Date(y, 12, 31) Dim Filter As String = "1=1" If Vars("开始日期") IsNot Nothing And Vars("结束日期") IsNot Nothing Then Filter = "录入时间 >= \'" & Vars("开始日期") & "\' And 录入时间 <= \'" & Vars("结束日期") & "\'" Else Filter = "录入时间 >= \'" & dt1 & "\' And 录入时间 <= \'" & dt2 & "\'" End If Dim nms As String Dim nmsx As String Dim drs As List(of DataRow) = DataTables("查询权限").SQLSelect("管理员ID = \'" & _UserID & "\'") If drs.Count > 0 Then For Each dr As DataRow In drs nms = nms & ",\'" & dr("查询单位") & "\'" Next For Each dr As DataRow In drs nmsx = nmsx & ",\'" & dr("查询部门") & "\'" Next End If If nmsx > "" And nms > "" Then nms = nms.Trim(",") nmsx = nmsx.Trim(",") Filter = Filter & " And " & "报表名称 Like \'%" & e.node.Name & "%\' And 纳税人识别号=\'" & e.Node.ParentNode.Name & "\'And 主管税务机关 In (" & nms & ") And 主管部门 In (" & nmsx & ")" End If Tables(e.Form.Name & "_Table1").Fill("select* from{企业所得税税收优惠备案管理台账} Where " & Filter ,"服务器",True)
|
| -- 作者：有点甜 -- 发布时间：2014/8/31 14:31:00 -- 你明明filter有内容，自己msgbox(filter)看看，拷贝sql语句到sql测试窗口测试，看提示什么错。 |
| -- 作者：逛逛 -- 发布时间：2014/8/31 18:41:00 -- 以下是引用jianjingmaoyi在2014-8-31 14:28:00的发言：
不应该这个问题呀 Dim nms As String
Dim nmsx As String
Dim drs As List(of DataRow) = DataTables("查询权限").SQLSelect("管理员ID = \'" & _UserID & "\'")
If drs.Count > 0 Then
For Each dr As DataRow In drs
nms = nms & ",\'" & dr("查询单位") & "\'"
Next
For Each dr As DataRow In drs
nmsx = nmsx & ",\'" & dr("查询部门") & "\'" 这里和上面，似乎加上空值判断 更为合理
Next
End If
If nmsx > "" And nms > "" Then 这里分开判断比较好。并且直接把拼接变量做完 nmsx = " And 主管部门 In (" & nmsx & ")" ）
nms = nms.Trim(",")
nmsx = nmsx.Trim(",")
Filter = Filter & " And " & "报表名称 Like \'%" & e.node.Name & "%\' And 纳税人识别号=\'" & e.Node.ParentNode.Name & "\'And 主管税务机关 In (" & nms & ") And 主管部门 In (" & nmsx & ")"
End If Tables(e.Form.Name & "_Table1").Fill("select* from{企业所得税税收优惠备案管理台账} Where " & Filter ,"服务器",True) |