有条件分页加载代码出错 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

出错提示：

.NET Framework 版本：2.0.50727.8800

Foxtable 版本：2018.10.9.1

错误所在事件：加载[招标信息]失败!

详细错误信息：

'Null归属部门' 附近有语法错误。

弹出msgbox(sql)的值看看，拷贝sql语句测试看报什么错。

把sql语句发上来看看

'========按归属部门进行数据加载==========

Dim fy As String = Args(0)

Dim fyjzfit As String ' = "1=2" '分页加载

Dim fdr As DataRow = DataTables("Users").Find("Name = '" & User.Name & "'")

If User.Type = UserTypeEnum.User Then

If fdr IsNot Nothing Then

If fdr("归属部门").Contains("总公司") Then

fyjzfit = "[_Identify] Is Not Null"

Else

fyjzfit = "归属部门 = '" & fdr("归属部门") & "'"

End If

Else

fyjzfit = "[_Identify] Is Not Null"

End If

fyjzfit = fyjzfit & fy

Dim r1 As Integer = (Page - 1) * 20 + 1

Dim r2 As Integer = Page * 20

Dim sql As String

Dim str As String = ""

For Each dc As DataCol In DataTables("招标信息").DataCols

str &= dc.name.Tostring & ","

sql = "Select " & str.trim(",") & " Fro m (Select Row_Number() Over(Order by 开标日期) As RowNum, * fro m 招标信息 where " & fyjzfit & ") As a "

sql = sql & " Where RowNum >= " & r1 & " And RowNum <= " & r2

msgbox(sql)

DataTables("招标信息").SQLLoad(sql)

dvubb 此主题相关图片如下：777.png

fyjzfit = fyjzfit & fy

改成

fyjzfit = fyjzfit & " and " & fy

还是出错：

此主题相关图片如下：888.png

此主题相关图片如下：999.png

复制，粘贴过去

fyjzfit = fyjzfit & " and " & fy

这个and前后没有加空格，折腾了一个星期。。。。。。。

fyjzfit = fyjzfit & " and " & fy

老师又有问题了

1、自定义函数Functions.Execute("gzbmflt")

'========按归属部门进行数据加载==========

Dim gzbmflt As String '分页加载

Dim fdr As DataRow = DataTables("Users").SQLFind("Name = '" & User.Name & "'")

If User.Type = UserTypeEnum.User Then

If fdr IsNot Nothing Then

If fdr("归属部门").Contains("总公司") Then

gzbmflt = "[_Identify] Is Not Null"

Else

gzbmflt = "归属部门 = '" & fdr("归属部门") & "' And 咨询类型 Like '%" & fdr("咨询类型") & "%'"

End If

Else

gzbmflt = "[_Identify] Is Not Null"

End If

Return gzbmflt

2、窗口AfterLoad

'生成从后台提取数据生成一个临时的DataTable

Dim cmd As New SQLCommand

Dim dt As DataTable

cmd.ConnectionName = V

cmd.CommandText = "Select DISTINCT 招标审核,咨询类型,归属部门,项目名称,业务性质,投标地区,评标方式,报价方式,项目状态,工程类别,开标日期,系统编号 Fro m {招标信息} Where " & Functions.Execute("gzbmflt") & " ORDER BY 开标日期 DESC"

dt = cmd.ExecuteReader()

Vars("zbxxdt") = dt

'''设置目录树

Dim trv As WinForm.TreeView = e.Form.Controls("Trv_sxs")

trv.BuildTree(dt,"归属部门|咨询类型|投标地区") ' ,"","招标审核 DESC") ',"","开标日期 DESC")

trv.Nodes.Insert("显示所有项目",0)

trv.Nodes(1).Expand()

'加载第一页数据

With DataTables("招标信息")

.LoadFilter = "" '清除加载条件

.LoadOrder = "开标日期 DESC"

.LoadReverse = True

.LoadPage = 0 '加载第一页

.LoadTop = 15 '每页5行

.Load()

e.Form.Controls("Page").Value = 1 & "/" & .TotalPages

End With

[此贴子已经被作者于2019/5/14 14:42:15编辑过]

3、在筛选树AfterCheckNode

Dim s As String = e.Form.Controls("Chebox_sxs").Text

Dim tb1 As Table = Tables(e.Form.Name & "_Table1")

Dim dt As DataTable = Vars("zbxxdt")

Dim Colstr() As String

Dim qts As String() = {"'","#",""}

Dim trv As WinForm.TreeView = e.Sender

Dim flt As String = ""

Dim nd As WinForm.TreeNode

Dim pd As WinForm.TreeNode

Dim notchecked As Integer = 0

Colstr = s.split("|")

If e.Node.Name = "显示所有项目" Then

If e.Node.Checked = True Then

For Each nd In trv.AllNodes

If nd.Name <> "显示所有项目" Then

nd.Checked = False

End If

tb1.Filter = ""

Else

For Each nd In trv.AllNodes

nd.Checked = False

tb1.Filter = (1 = 2)

End If

trv.CollapseAll

Return

Else

trv.AllNodes(0).Checked = False

End If

For Each nd In e.node.allNodes

nd.Checked = False

pd = e.Node.ParentNode

Do While pd IsNot Nothing

pd.Checked = False

pd = pd.ParentNode

Loop

For Each nd In trv.AllNodes

If nd.Checked Then

Dim rts() As String = nd.FullPath.Split("\")

Dim val As String = ""

For i As Integer = 0 To rts.length - 1

If dt.DataCols(Colstr(i)).IsString Then

If rts(i) <> "空" Then

If val > "" Then

val = val & " And "

val = val & Colstr(i) & " = " & qts(0) & rts(i) & qts(0)

Else

val = val & Colstr(i) & " = " & qts(0) & rts(i) & qts(0)

End If

ElseIf rts(i) = "空" Then

If val > "" Then

val = val & " And "

val = val & Colstr(i) & " Is Null"

Else

val = val & Colstr(i) & " Is Null"

End If

ElseIf dt.DataCols(Colstr(i)).IsDate Then

If rts(i) <> #0001-1-1 0:00:00# Then

If val > "" Then

val = val & " And "

val = val & Colstr(i) & " = " & qts(1) & rts(i) & qts(1)

Else

val = val & Colstr(i) & " = " & qts(1) & rts(i) & qts(1)

End If

ElseIf rts(i) = #0001-1-1 0:00:00# Then

If val > "" Then

val = val & " And "

val = val & Colstr(i) & " Is Null"

Else

val = val & Colstr(i) & " Is Null"

End If

ElseIf dt.DataCols(Colstr(i)).IsNumeric Then

If rts(i) <> 0 Then

If val > "" Then

val = val & " And "

val = val & Colstr(i) & " = " & qts(2) & rts(i) & qts(2)

Else

val = val & Colstr(i) & " = " & qts(2) & rts(i) & qts(2)

End If

ElseIf rts(i) = 0 Then

If val > "" Then

val = val & " And "

val = val & Colstr(i) & " Is Null"

Else

val = val & Colstr(i) & " Is Null"

End If

ElseIf dt.DataCols(Colstr(i)).IsBoolean Then

If rts(i) <> False Then

If val > "" Then

val = val & " And "

val = val & Colstr(i) & " = " & qts(0) & True & qts(0)

Else

val = val & Colstr(i) & " = " & qts(0) & True & qts(0)

End If

ElseIf rts(i) = False Then

If val > "" Then

val = val & " And "

val = val & Colstr(i) & " = " & qts(0) & False & qts(0)

Else

val = val & Colstr(i) & " = " & qts(0) & False & qts(0)

End If

If flt > "" Then

flt = flt & " Or (" & val & ")"

Else

flt = val

End If

notchecked + = 1

End If

Dim nids As String = dt.GetComboListString("系统编号",flt)

msgbox(nids)

If flt > "" AndAlso notchecked > 0 Then

msgbox(flt)

flt = "系统编号 In ('" & nids.Trim("|").Replace("|","','") & "')"

Else

flt = (1=2)

End If

'加载第一页数据

With DataTables("招标信息")

.LoadFilter = Functions.Execute("gzbmflt") & " And " & flt '清除加载条件

.LoadOrder = "开标日期 DESC"

.LoadReverse = True

.LoadPage = 0 '加载第一页

.LoadTop = 15 '每页5行

.Load()

e.Form.Controls("Page").Value = 1 & "/" & .TotalPages

End With

打勾时出现下面提示错误：

此主题相关图片如下：nids.png

取消打勾时出现下面提示：

图片点击可在新窗口打开查看

此主题相关图片如下：取消打勾.png

[此贴子已经被作者于2019/5/14 14:50:25编辑过]

主题：有条件分页加载代码出错