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

Dim cwrb As New SQLCommand '可复核的销售单号

Dim rb As DataTable

cwrb.CommandText = " 从内部表查询按条件过滤出可复核的销售单号"

rb = cwrb.ExecuteReader()

Dim dt As DataTable

Dim cmd As New SQLCommand

cmd.C '数据库为SQL2008

cmd.CommandText = " 查询 smartID,checked From {销售单} Where smartID = (" & rb & ")" '注意要包括主键列

dt = cmd.ExecuteReader(True) '注意可选参数设置为True

For Each dr As DataRow In dt.DataRows

dr("checked") = True

dt.Save()

以上代码执行错误

需求为：根据内部表查询出来可复核的销售单号，更新后台数据中同单号的数据行的checked字段

请问老师是那里错了或者有其他方法实现根据内部表的查询结果更新后台数据

[此贴子已经被作者于2019/8/14 11:03:47编辑过]

老师改后报错如下

.NET Framework 版本：2.0.50727.8806

Foxtable 版本：2018.10.9.1

错误所在事件：

详细错误信息：

',' 附近有语法错误。

以下是代码

Dim cwrb As New SQLCommand '可复核的销售单号

Dim rb As DataTable

cwrb.CommandText = "查询本单单号 From {销售主表} where 是否检查=True and 本单单号 NOT in (查询本单单号 From {单据检查异常情况})"

rb = cwrb.ExecuteReader()

Dim dt As DataTable

Dim cmd As New SQLCommand

cmd.C '数据库为SQL2008

cmd.CommandText = "查询 smartID,checked From {销售单} Where smartID = (" & rb.GetComboListString("本单单号").Replace("|",",") & ")" '注意要包括主键列

dt = cmd.ExecuteReader(True) '注意可选参数设置为True

For Each dr As DataRow In dt.DataRows

dr("checked") = True

dt.Save()

代码替换后，不报错，但后台数据库的数据没有更新

没有符合的数据出来,用内部表试了，也没数据出来

[此贴子已经被作者于2019/8/14 14:30:38编辑过]

查询 smartID,checked From {销售单} Where smartID ='XS1811-0068' （这个可以查询到）

查询 smartID,checked From {销售单} Where smartID =('XS1811-0068,XS1905-1406,XS1906-0048') （这个查询不到）

查询 smartID,checked From {销售单} Where smartID =('XS1811-0068','XS1905-1406','XS1906-0048') （这个查询不到）

查询 smartID,checked From {销售单} Where smartID in ('XS1811-0068','XS1905-1406','XS1906-0048') （这个能查询到）

下面对应的要怎么改，自己改成这样还是不行

cmd.CommandText = "查询 smartID,checked From {销售单} Where smartID in ('" & rb.GetComboListString("本单单号").Replace("|","','") & "')"

[此贴子已经被作者于2019/8/14 17:43:26编辑过]

Jb18.AddExp("系统单号", "R04_MaterialsAllocation.smartID", True)

Jb18.AddExp("单内序号", "1")

Jb18.AddExp("工费单号", "R04_MaterialsAllocation.smartID", True)

Jb18.AddExp("领料单号", "R04_MaterialsAllocation.smartID", True)

'Jb18.AddExp("产品成色", "''")

Jb18.AddExp("比较成色", "R02_SilverStyle.materialName", True)

Jb18.AddExp("比较银重", "-sum(isnull(weight,0))")

Jb18.AddExp("产品件数", "0")

Jb18.AddExp("产品净银重", "0 * 0.00001")

Jb18.AddExp("备注", "R04_MaterialsAllocation.outboundWarehouseName", True)

'Jb18.AddExp("实领料成色", "R02_SilverStyle.materialName ", True)

Jb18.AddExp("实领料银重", "-sum(isnull(weight,0))")

Jb18.AddExp("实工费", "0 * 0.00001")

If e.Sender.Name = "Button3" Then

Jb18.Filter = "convert(nvarchar(10),R04_MaterialsAllocation.smartDate,121)>='" & StartDate & "' And convert(nvarchar(10), R04_MaterialsAllocation.smartDate,121)<='" & LastDay & "' and R02_Customer. customerCode='" & cmdCustomerCode & "' And R04_MaterialsAllocation.audited = 'true'" '加载数据时用

Else

If e.Sender.Name = "Button06" Then

Jb18.Filter = "R02_Customer. customerCode='" & cmdCustomerCode & "' and R04_MaterialsAllocation.smartID in " & s & " and R04_MaterialsAllocation.audited='true'" '审核时用

End If

'组合jb1 '采购入库的产品净银重

jb1.Merge(jb2) '组合jb2 '领料出库领料重

jb1.Merge(jb3) '组合jb3 '半成品入库的产品净银重

jb1.Merge(jb4) '组合jb4 '报废退货单的产品净银重

jb1.Merge(jb5) '组合jb5 '报废退货单的领料重

jb1.Merge(jb6) '组合jb6 '半成品入库的实工费

jb1.Merge(jb7) '组合jb7 '委外入库的实工费

jb1.Merge(jb8) '组合jb8 '返修入库的实工费

jb1.Merge(jb9) '组合jb9 '报废退货的实工费

jb1.Merge(jb10) '组合jb10 '付款单的实工费

jb1.Merge(jb11) '组合jb11 '往来费用单的实工费

jb1.Merge(jb12) '组合jb12 '材料入库的实工费

jb1.Merge(jb13) '组合jb13 '采购成品入库的实工费

jb1.Merge(jb14) '组合jb14 '材料入库的领料重

jb1.Merge(jb15) '组合jb15 '调拨出库领料重

jb1.Merge(jb16) '组合jb16 '返修退厂的实工费

jb1.Merge(jb17) '组合jb17 '旧料回收的领料重

jb1.Merge(jb18) '组合jb18 ''调拨入库领料重

Dim dt As DataTable

dt = jb1.Build(True)

Dim sCols() As String = {"系统单号", "单内序号", "类型", "单据类型", "比较成色", "比较银重", "比较工费", "供应商代码", "供应商名称", "本单日期", "工费单号", "领料单号", "实工费", "产品件数", "产品净银重", "实领料银重", "备注"}

Dim dCols() As String = {"ID", "number", "kind", "tableName", "contrastmaterialName" , "contrastweight", "contrastAmount", "customerCode", "customerName", "smartDate", "factoryID", "sourceID",

For Each dr As DataRow In dt.DataRows

'填充数据

If DataTables("R03_payable_Detail").Find("customerCode= '" & dr("供应商代码") & "' and kind = '" & dr("类型") & "' and ID = '" & dr("系统单号") & "'And number = '" & dr("单内序号") & "' And c And c And c ") Is Nothing Then

Dim nr As DataRow = DataTables("应付副表本地表").AddNew()

For i As Integer = 0 To sCols.Length - 1

nr(dCols(i)) = dr(sCols(i))

End If

老师，以上代码，在填充数据代码段耗时很长，导致界面闪退，有什么方法优化吗？上面查询结果也只有100多行

[此贴子已经被作者于2023/6/6 18:21:06编辑过]

关联18个表，执行查询时，用计时器测过，时间很短，1秒左右，查询出来的数据最多100多行

用MessageBox调试时，都是在把数据填到应付副表本地表时崩溃的

表事件代码如下

表事件

应付副表本地表_DataColChanged

Dim dr As DataRow = e.DataRow

Select Case e.DataCol.Name

Case "contrastmaterialName"

Dim pr1 As DataRow = e.DataRow.GetParentRow("R02_supplier")

If pr1 IsNot Nothing Then

'当单据类型=产品净银重时,对比色=应付科表中相同成色列对应的转换后成色

If e.DataRow("kind") = "产品净银重" Then

If e.DataRow("contrastmaterialName") = "S925银" Then

e.DataRow("materialName1") = pr1("PickingColour925")

Else

If e.DataRow("contrastmaterialName") = "足银990" Then

e.DataRow("materialName1") = pr1("PickingColour990")

Else

If e.DataRow("contrastmaterialName") = "足银999" Then

e.DataRow("materialName1") = pr1("PickingColour999")

Else

If e.DataRow("contrastmaterialName") = "足银999.9" Then

e.DataRow("materialName1") = pr1("PickingColour9999")

End If

Else

'当单据类型 = 实工费 Or = 期初 - 工费时, 对比色 =应付科目表中相同供应商代码对应的主成色

If e.DataRow("kind") = "实工费" Or e.DataRow("kind") = "期初-工费" Then

e.DataRow("materialName1") = pr1("materialName")

Else

'当单据类型=领料重时,对比色=比较成色

If e.DataRow("kind") = "领料重" Then

e.DataRow("materialName1") = e.DataRow("contrastmaterialName")

End If

'依据对比成色得出折点率

Case "materialName1"

Dim pr3 As DataRow = e.DataRow.GetParentRow("R02_supplier")

If pr3 IsNot Nothing Then

If e.DataRow("materialName1") = "S925银" Then

e.DataRow("weightRate") = pr3("ConversionRate925")

Else

If e.DataRow("materialName1") = "足银990" Then

e.DataRow("weightRate") = pr3("ConversionRate990")

Else

If e.DataRow("materialName1") = "足银999" Then

e.DataRow("weightRate") = pr3("ConversionRate999")

Else

If e.DataRow("materialName1") = "足银999.9" Then

e.DataRow("weightRate") = pr3("ConversionRate9999")

Else

If e.DataRow("materialName1") = "黄铜" Then

e.DataRow("weightRate") = 0

End If

End Select

应付副表本地表_AfterCheckRow

'获得相关参数

Dim smartID As String = e.Form.Controls("smartID").Value

For Each r As Row In Tables("应付副表本地表").Rows '遍历表所有行

Dim dr As Row = e.Row '定义当前行

If dr.IsNull("factoryID") = False Then

If r("factoryID") = dr("factoryID") Then '如果遍历到某行ID列与当前行ID列值相同 ‘r("ID")=dr("ID") Or Or r("sourceID") = dr("sourceID")

r.Checked = e.Row.Checked '则某行复选框状态与当前行复选框状态相同

r("checked") = e.Row.Checked

r("smartID") = "" & smartID & ""

If e.Row.Checked = False Then 'And dr.locked = False ‘当复选框为否

r("smartID") = Nothing

r("checked") = e.Row.Checked

End If

DataTables("应付副表本地表").Save()

[此贴子已经被作者于2023/6/7 13:59:26编辑过]

前面生成统计表才要0.5秒左右，在后面的循环给"应付副表本地表"表赋值慢要6秒多

注释后，循环给"应付副表本地表"表赋值慢还是要6秒多

没有用到表达式列

主题：更新数据