- 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=109864)
-- 发布时间：2017/11/21 20:56:00
-- 用本地数据更新未加载sql数据表
项通过按钮click用本地数据更新未加载sql数据表
Dim cmd As New SQLCo mmand
cmd.C
cmd.Comman dText = "UP DATE {SUGGESTION_PLAN} SET [QTY] = A.[需求量] from [数据库].[物料需求计划] As A WHERE [SUGGESTION_PLAN].[APS_DOC_NO]= [A].[APS单号] And [SUGGESTION_PLAN].[MDS_VERSION_TIMES]=\'2301-170928001-18-1-cs\'"
cmd.ExecuteNonQuery()
Dim dt As Tables = Tables("物料需求计划")
Dim cmd As New SQLC ommand
cmd.C
cmd.CommandText = "UP DATE {SUGGESTION_PLAN} SET [QTY] = dt（"需求量"） WHERE [SUGGESTION_PLAN].[APS_DOC_NO]=dt（"APS单号"）And [SUGGESTION_PLAN].[MDS_VERSION_TIMES]=\'2301-170928001-18-1-cs\'"
cmd.ExecuteNonQuery()
-- 发布时间：2017/11/21 21:08:00
--
cmd.CommandText = "UP DATE {SUGGESTION_PLAN} SET [QTY] = \'" & dt（"需求量"） & "\' WHERE [SUGGESTION_PLAN].[APS_DOC_NO]= \'" & dt（"APS单号"） & "\' And [SUGGESTION_PLAN].[MDS_VERSION_TIMES]=\'2301-170928001-18-1-cs\'"
-- 发布时间：2017/11/22 15:18:00
--
方案一：
Dim str As String = Tables("物料需求计划").Current("计划批号")
Dim dt As DataRow In Tables("物料需求计划").dataRows
Dim cmd As New SQLCommand
cmd.C
cmd.CommandText = "UP DATE {SUGGESTION_PLAN} SET [QTY] = \'" & dt("需求量") & "\' WHERE {SUGGESTION_PLAN}.[APS_DOC_NO]= \'" & dt("APS单号") & "\' And {SUGGESTION_PLAN}.[MDS_VERSION_TIMES]=\'" & str & "\'"
cmd.ExecuteNonQuery()
此主题相关图片如下：qq截图20171122103338.png
方案二：
Dim str As String = Tables("物料需求计划").Current("计划批号")
Dim drs As List(of DataRow) = DataTables("物料需求计划").SQLSelect("计划批号 = \'" & str & "\'")
For Each dr As DataRow In drs
Dim cmd As New SQLCommand
cmd.C
cmd.CommandText = "UP DATE {SUGGESTION_PLAN} SET [QTY] = \'" & dr("本次发放数量") & "\', [CURRENT_RELEASE_QTY] = \'" & dr("本次发放数量") & "\' WHERE {SUGGESTION_PLAN}.[APS_DOC_NO]= \'" & dr("APS单号") & "\'"
cmd.ExecuteNonQuery()
Next
如果改成Dim drs As List(of DataRow) = DataTables("物料需求计划").SQLSelect("计划批号 = \'固定值\' ")测试过是行得通批量更新的！但是我把他改成str代号后好像就只能update第一行了,求助一下
[此贴子已经被作者于2017/11/22 15:18:48编辑过]
-- 发布时间：2017/11/22 15:42:00
--
Dim str As String = Tables("物料需求计划").Current("计划批号")
Dim cmd As New SQLCommand
For Each dr As DataRow In DataTables("物料需求计划").Select("计划批号 = \'" & str & "\'")
cmd.CommandText = "UP DATE {SUGGESTION_PLAN} SET [QTY] = \'" & dr("需求量") & "\' WHERE {SUGGESTION_PLAN}.[APS_DOC_NO]= \'" & dr("APS单号") & "\' And {SUGGESTION_PLAN}.[MDS_VERSION_TIMES]=\'" & str & "\'"
cmd.ExecuteNonQuery()
Next
-- 发布时间：2017/11/22 18:35:00
--
谢谢版主~试验成功了~
-- 发布时间：2017/11/23 19:48:00
--
新的问题。。。以下按钮代码执行时报错：关键字 \'WHERE\' 附近有语法错误。
ITEMID原来是个GUID字段，从sql导入的时候我把他转换为string，是不是因为从本地导到sql中时GUID字段和string字段冲突？求助...
然后我请教了其他人说新增行需要用newid()人为地增加GUID主键，是这样子么？
Dim str As String = Tables("物料需求计划").Current("APS单号")
Dim cmd As New SQLCommand
cmd.c
Dim dr As DataRow
dr = DataTables("物料需求计划").find("APS单号 = \'" & str & "\'")
cmd.CommandText = "Insert Into {SUGGESTION_PLAN} (APS_DOC_NO,MDS_VERSION_TIMES,REMARK,PLAN_TYPE,ITEM_ID,QTY,CURRENT_RELEASE_QTY) Values(\'" & dr("APS单号") & "\'," & dr("计划批号") & ",\'" & dr("备注") & "\',\'" & dr("计划类型") & "\',\'" & dr("ITEMID") & "\',\'" & dr("需求量") & "\',\'" & dr("需求量") & "\') WHERE {SUGGESTION_PLAN}.[APS_DOC_NO]= \'" & str & "\'"
cmd.ExecuteNonQuery()
messagebox.Show("已更新", "提示")
[此贴子已经被作者于2017/11/23 19:56:57编辑过]
-- 发布时间：2017/11/23 21:05:00
--
insert into 是插入一行数据，根本就没有where的语法。把where 和后面的内容删除。
如果你要更新某一条件下的某些数据，你应该用update语句