专家坐堂-表属性代码问题Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  表属性代码问题  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=149880)

--  作者：爱相随
--  发布时间：2020/5/14 13:52:00
--  表属性代码问题

老师，我的项目中的一个表，以前一直用都没有问题，现在突然出现问题了，问题如下：
表A主要信息是通过菜单命令从表B填充过来的，菜单命令的代码如下：
For Each r As Row In CurrentTable.Rows
DataTables("ndhbfxjhb").datarows.clear
Dim f As New Filler
f.SourceTable = DataTables("xzxymxb") \'指定数据来源
f.SourceCols = "zwlb,zwlx,zwbm,zwmc,zqr,zqrqc" \'指定数据来源列
f.DataTable = DataTables("ndhbfxjhb") \'指定数据接收表
f.DataCols = "zwlb,zwlx,zwbm,zwmc,zqr,zqrqc" \'指定数据接收列
f.ExcludeExistValue = True
f.Fill() \'填充数据
Next
MainTable = Tables("ndhbfxjhb")
表A的表属性DataColChanged事件代码如下：
Dim dt1 As Date = e.DataRow("rq") .Addyears(-1) \'获取1年前的日期
If e.DataCol.Name = "zwbm" Then
Dim filter4 As String = "zwbm = \'" & e.DataRow("zwbm") & "\'"
e.DataRow("snye") = DataTables("xzfkmxb").Compute("sum(byfk)",filter4 & " And [rq] <= \'" & dt1 & "\'")/10000 - DataTables("hbmxb").Compute("sum(hkje)",filter4 & " And [rq] <= \'" & dt1 & "\'")/10000

End If
If e.DataCol.Name = "zwbm" Then
Dim filter2 As String = "zwbm = \'" & e.DataRow("zwbm") & "\' And nd = \'" & e.DataRow("nd") & "\'"
e.DataRow("b11") = DataTables("hbfxjhb").Compute("sum(hbje)",filter2 & " And [yf] = \'1\'")
e.DataRow("l11") = DataTables("hbfxjhb").Compute("sum(fxje)",filter2 & " And [yf] = \'1\'")
e.DataRow("b12") = DataTables("hbfxjhb").Compute("sum(hbje)",filter2 & " And [yf] = \'2\'")
e.DataRow("l12") = DataTables("hbfxjhb").Compute("sum(fxje)",filter2 & " And [yf] = \'2\'")
e.DataRow("b13") = DataTables("hbfxjhb").Compute("sum(hbje)",filter2 & " And [yf] = \'3\'")
e.DataRow("l13") = DataTables("hbfxjhb").Compute("sum(fxje)",filter2 & " And [yf] = \'3\'")
e.DataRow("b14") = DataTables("hbfxjhb").Compute("sum(hbje)",filter2 & " And [yf] = \'4\'")
e.DataRow("l14") = DataTables("hbfxjhb").Compute("sum(fxje)",filter2 & " And [yf] = \'4\'")
e.DataRow("b15") = DataTables("hbfxjhb").Compute("sum(hbje)",filter2 & " And [yf] = \'5\'")
e.DataRow("l15") = DataTables("hbfxjhb").Compute("sum(fxje)",filter2 & " And [yf] = \'5\'")
e.DataRow("b16") = DataTables("hbfxjhb").Compute("sum(hbje)",filter2 & " And [yf] = \'6\'")
e.DataRow("l16") = DataTables("hbfxjhb").Compute("sum(fxje)",filter2 & " And [yf] = \'6\'")
e.DataRow("b17") = DataTables("hbfxjhb").Compute("sum(hbje)",filter2 & " And [yf] = \'7\'")
e.DataRow("l17") = DataTables("hbfxjhb").Compute("sum(fxje)",filter2 & " And [yf] = \'7\'")
e.DataRow("b18") = DataTables("hbfxjhb").Compute("sum(hbje)",filter2 & " And [yf] = \'8\'")
e.DataRow("l18") = DataTables("hbfxjhb").Compute("sum(fxje)",filter2 & " And [yf] = \'8\'")
e.DataRow("b19") = DataTables("hbfxjhb").Compute("sum(hbje)",filter2 & " And [yf] = \'9\'")
e.DataRow("l19") = DataTables("hbfxjhb").Compute("sum(fxje)",filter2 & " And [yf] = \'9\'")
e.DataRow("b110") = DataTables("hbfxjhb").Compute("sum(hbje)",filter2 & " And [yf] = \'10\'")
e.DataRow("l110") = DataTables("hbfxjhb").Compute("sum(fxje)",filter2 & " And [yf] = \'10\'")
e.DataRow("b111") = DataTables("hbfxjhb").Compute("sum(hbje)",filter2 & " And [yf] = \'11\'")
e.DataRow("l111") = DataTables("hbfxjhb").Compute("sum(fxje)",filter2 & " And [yf] = \'11\'")
e.DataRow("b112") = DataTables("hbfxjhb").Compute("sum(hbje)",filter2 & " And [yf] = \'12\'")
e.DataRow("l112") = DataTables("hbfxjhb").Compute("sum(fxje)",filter2 & " And [yf] = \'12\'")
e.DataRow("本金1月") = DataTables("hbmxb").Compute("sum(hkje)",filter2 & " And [yf] = \'1\'")/10000
e.DataRow("利息1月") = DataTables("lxmxb").Compute("sum(zfje)",filter2 & " And [yf] = \'1\'")/10000
e.DataRow("本金2月") = DataTables("hbmxb").Compute("sum(hkje)",filter2 & " And [yf] = \'2\'")/10000
e.DataRow("利息2月") = DataTables("lxmxb").Compute("sum(zfje)",filter2 & " And [yf] = \'2\'")/10000
e.DataRow("本金3月") = DataTables("hbmxb").Compute("sum(hkje)",filter2 & " And [yf] = \'3\'")/10000
e.DataRow("利息3月") = DataTables("lxmxb").Compute("sum(zfje)",filter2 & " And [yf] = \'3\'")/10000
e.DataRow("本金4月") = DataTables("hbmxb").Compute("sum(hkje)",filter2 & " And [yf] = \'4\'")/10000
e.DataRow("利息4月") = DataTables("lxmxb").Compute("sum(zfje)",filter2 & " And [yf] = \'4\'")/10000
e.DataRow("本金5月") = DataTables("hbmxb").Compute("sum(hkje)",filter2 & " And [yf] = \'5\'")/10000
e.DataRow("利息5月") = DataTables("lxmxb").Compute("sum(zfje)",filter2 & " And [yf] = \'5\'")/10000
e.DataRow("本金6月") = DataTables("hbmxb").Compute("sum(hkje)",filter2 & " And [yf] = \'6\'")/10000
e.DataRow("利息6月") = DataTables("lxmxb").Compute("sum(zfje)",filter2 & " And [yf] = \'6\'")/10000
e.DataRow("本金7月") = DataTables("hbmxb").Compute("sum(hkje)",filter2 & " And [yf] = \'7\'")/10000
e.DataRow("利息7月") = DataTables("lxmxb").Compute("sum(zfje)",filter2 & " And [yf] = \'7\'")/10000
e.DataRow("本金8月") = DataTables("hbmxb").Compute("sum(hkje)",filter2 & " And [yf] = \'8\'")/10000
e.DataRow("利息8月") = DataTables("lxmxb").Compute("sum(zfje)",filter2 & " And [yf] = \'8\'")/10000
e.DataRow("本金9月") = DataTables("hbmxb").Compute("sum(hkje)",filter2 & " And [yf] = \'9\'")/10000
e.DataRow("利息9月") = DataTables("lxmxb").Compute("sum(zfje)",filter2 & " And [yf] = \'9\'")/10000
e.DataRow("本金10月") = DataTables("hbmxb").Compute("sum(hkje)",filter2 & " And [yf] = \'10\'")/10000
e.DataRow("利息10月") = DataTables("lxmxb").Compute("sum(zfje)",filter2 & " And [yf] = \'10\'")/10000
e.DataRow("本金11月") = DataTables("hbmxb").Compute("sum(hkje)",filter2 & " And [yf] = \'11\'")/10000
e.DataRow("利息11月") = DataTables("lxmxb").Compute("sum(zfje)",filter2 & " And [yf] = \'11\'")/10000
e.DataRow("本金12月") = DataTables("hbmxb").Compute("sum(hkje)",filter2 & " And [yf] = \'12\'")/10000
e.DataRow("利息12月") = DataTables("lxmxb").Compute("sum(zfje)",filter2 & " And [yf] = \'12\'")/10000

End If

然后执行菜单命令后提示的错误信息如下：
.NET Framework 版本：2.0.50727.8806
Foxtable 版本：2017.6.12.1
错误所在事件：表，ndhbfxjhb，DataColChanged
详细错误信息：
调用的目标发生了异常。
添加或减去的值产生无法表示的 DateTime。
参数名: months

真不知道这些错误怎么冒出来的。

--  作者：爱相随
--  发布时间：2020/5/14 13:58:00
--
其他与表A有关联的代码就是在窗口的TopicBar中引用了表A的数据。相关代码如下：
Dim y As Integer = Date.Today.Year
Dim w As Integer = Date.Today.DayOfWeek \'算出今天是星期几
Dim m As Integer = Date.Today.Month
\'----------------------------------------------------------------------------
Dim Page1 As WinForm.TopicPage
Dim dt1 As Date = Date.Today.AddDays(-w) \'获取本周的第一天
Dim dt2 As Date = Date.Today.AddDays(6 - w) \'获取本周的最后一天
Dim Filter501 As String
Filter501 = "rq >= \'" & dt1 & "\' And rq <= \'" & dt2 & "\'"

Dim dt3 As New Date(y, m, 1)
Dim dt4 As New Date(y, m, Date.DaysInMonth(y, m)) \'获取本月的最后一天
Dim Filter12 As String
Filter12 = "rq >= \'" & dt3 & "\' And rq <= \'" & dt4 & "\'"

Dim q As Integer = (Date.Today.Month - 1) \\ 3 + 1 \'计算现在是第几个季度
Dim dt5 As New Date(y, 3 * (q - 1) + 1, 1) \'获取本季度的第一天
Dim dt6 As New Date(y, 3 * q, Date.DaysInMonth(y,3 * q)) \'获取本季度的最后一天
Dim Filter As String
Filter = "rq >= #" & dt5 & "# And rq <= #" & dt6 & "#"

Dim dt7 As New Date(y, 1, 1)
Dim dt8 As New Date(y, 12, 31)
Dim Filter3 As String
Dim Filter4 As String
Filter3 = "rq >= \'" & dt7 & "\' And rq <= \'" & dt8 & "\'"
Filter4 = "rq < \'" & dt7 & "\'"

Dim cnt33 As Integer = DataTables("ndhbfxjhb").sqlCompute("sum(snye)")
Dim cnt34 As Integer = DataTables("ndhbfxjhb").sqlCompute("sum(snye)" , "zwlx = \'公益性项目债务\'")
Dim cnt35 As Integer = DataTables("ndhbfxjhb").sqlCompute("sum(snye)" , "zwlx = \'公益性项目债务\' And zwlb = \'贷款融资\'")
Dim cnt36 As Integer = DataTables("ndhbfxjhb").sqlCompute("sum(snye)" , "zwlx = \'公益性项目债务\' And zwlb = \'应付工程款\'")
Dim cnt37 As Integer = DataTables("ndhbfxjhb").sqlCompute("sum(snye)" , "zwlx = \'非项目债务\'") + DataTables("ndhbfxjhb").sqlCompute("sum(snye)" , "zwlx = \'经营性项目债务\'")
Dim cnt38 As Integer = DataTables("ndhbfxjhb").sqlCompute("sum(snye)" , "zwlx = \'非项目债务\' And zwlb = \'贷款融资\'") + DataTables("ndhbfxjhb").sqlCompute("sum(snye)" , "zwlx = \'经营性项目债务\' And zwlb = \'贷款融资\'")
Dim cnt39 As Integer = DataTables("ndhbfxjhb").sqlCompute("sum(snye)" , "zwlx = \'非项目债务\' And zwlb = \'应付工程款\'") + DataTables("ndhbfxjhb").sqlCompute("sum(snye)" , "zwlx = \'经营性项目债务\' And zwlb = \'应付工程款\'")
Dim cnt40 As Integer = DataTables("ndhbfxjhb").sqlCompute("sum(snye)" , "zwlx = \'棚改项目\'")

--  作者：爱相随
--  发布时间：2020/5/14 14:06:00
--
在窗口代码引用前菜单命命令都可以用的，没有提示错误。

--  作者：有点蓝
--  发布时间：2020/5/14 14:23:00
--
是不是某一行的rq列没有值？

表A的表属性DataColChanged事件代码如下：

If e.DataCol.Name = "zwbm" Then
Dim dt1 As Date
if e.DataRow.isnull("rq") = false
dt1 = e.DataRow("rq") .Addyears(-1)
endif
Dim filter4 As String = "zwbm = \'" & e.DataRow("zwbm") & "\'"
e.DataRow("snye") = DataTables("xzfkmxb").Compute("sum(byfk)",filter4 & " And [rq] <= \'" & dt1 & "\'")/10000 - DataTables("hbmxb").Compute("sum(hkje)",filter4 & " And [rq] <= \'" & dt1 & "\'")/10000

End If
If e.DataCol.Name = "zwbm" Then
……

--  作者：爱相随
--  发布时间：2020/5/14 14:37:00
--

以上错误是提示后，表A不能填充数据，且关闭不了消息框。按老师的代码处理后，仍提示以下错误，且消息框关闭不了。
.NET Framework 版本：2.0.50727.8806
Foxtable 版本：2017.6.12.1
错误所在事件：表，ndhbfxjhb，DataColChanged
详细错误信息：
调用的目标发生了异常。
无法在 System.Int32 和 System.String 上执行“=”操作。

--  作者：有点蓝
--  发布时间：2020/5/14 14:43:00
--
做筛选条件的列是不是改过类型？如果是整数型，不需要加单引号

e.DataRow("b11") = DataTables("hbfxjhb").Compute("sum(hbje)",filter2 & " And [yf] = 1")

--  作者：爱相随
--  发布时间：2020/5/14 14:45:00
--
主要意思是，表A（ndhbfxjhb）的信息通过菜单命令从表B(xzxymxb)按照条件填充，在点击菜单命令时出现下列错误提示且无法填充表A数据。而在以前都没有问题的，窗口引用表A数据也不影响
.NET Framework 版本：2.0.50727.8806
Foxtable 版本：2017.6.12.1
错误所在事件：表，ndhbfxjhb，DataColChanged
详细错误信息：
调用的目标发生了异常。
添加或减去的值产生无法表示的 DateTime。
参数名: months

[此贴子已经被作者于2020/5/14 14:46:01编辑过]

--  作者：爱相随
--  发布时间：2020/5/14 14:48:00
--
表A没有改变类型，nd和yf都是字符型，不过表B的是整数类型，但与表B无关啊
[此贴子已经被作者于2020/5/14 14:49:53编辑过]

--  作者：有点蓝
--  发布时间：2020/5/14 14:52:00
--
请上传实例测试

--  作者：爱相随
--  发布时间：2020/5/14 14:56:00
--
表的含议是要先填充表A，然后输入日期，相应的列根据输入的日期条件自动统计数据。
[此贴子已经被作者于2020/5/14 14:57:01编辑过]