- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- 回写数据至sql数据库 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=104454)
-- 发布时间：2017/7/29 14:33:00
-- 回写数据至sql数据库
求一段代码或者思路：
此主题相关图片如下：qq截图20170729142819.png
如图，这是一个数据表，通过以下代码（按钮）实现“数据从数据库导入”，现在需要针对光标选中的行和红框内的数据，判断是否更，如果是的话把对应的数据“更新至后台数据库”（按钮）
Dim cmd As New SQLCommand
Dim dt As DataTable
Dim sCols() As String = {"APS_DOC_NO","计划批号","需求单号","计划类型","发放时间","需求时间","品号","品名","图号","材质","规格","计划数量","需求量","已发放","本次发放数量","单位","库存量","备注","供应商","投料尺寸"}
Dim dCols() As String = {"APS单号","计划批号","需求单号","计划类型","发放时间","需求时间","品号","品名","图号","材质","规格","计划数量","需求量","已发放","本次发放数量","单位","库存量","备注","供应商","投料尺寸"}
cmd.C
cmd.CommandText = "Sele ct top 800 APS_DOC_NO,MDS_VERSION_TIMES As [计划批号],DEMAND AS [需求单号],PLAN_TYPE As [计划类型],convert(datetime ,DEMAND_DATE,120) AS [需求时间],DOC_DATE AS [发放时间],PLAN_SUGGESTED_QTY AS[计划数量],QTY AS[需求量],RELEASED_QTY AS [已发放], CURRENT_RELEASE_QTY AS [本次发放数量],{UNIT}.[UNIT_NAME] AS [单位],{SUGGESTION_PLAN}.[REMARK] AS [备注],ITEM_CODE AS [品号],ITEM_NAME AS [品名],SHORTCUT AS [材质],ITEM_SPECIFICATION AS [规格],DRAWING_NO AS [图号],[INVENTORY_QTY] As [库存量], {ITEM}.UDF026 As [投料尺寸],[SUPPLIER_NAME] As [供应商] From ((({SUGGESTION_PLAN} Inner JOIN {ITEM} ON {ITEM}.[ITEM_BUSINESS_ID] = {SUGGESTION_PLAN}.[ITEM_ID]) LEFT JOIN {SUPPLIER} ON {SUPPLIER}.[SUPPLIER_BUSINESS_ID] = {SUGGESTION_PLAN}.[SUGGEST_SUPPLIER_ID])Inner JOIN {UNIT} ON {UNIT}.[UNIT_ID] = {SUGGESTION_PLAN}.[UNIT_ID])Inner JOIN {INV_COST_BAL} ON {INV_COST_BAL}.[ITEM_ID] = {SUGGESTION_PLAN}.[ITEM_ID] ORDER BY [DOC_DATE] Desc"
dt = cmd.ExecuteReader()
For Each dr As DataRow In dt.DataRows
Dim t As DataRow= DataTables("物料需求计划").Find("APS单号 = \'" & dr(sCols(0)) & "\'")
If t Is Nothing Then \'如果不存在同编号的订单
t = DataTables("物料需求计划").AddNew()
End If
For i As Integer =0 To sCols.Length -1
t(dCols(i)) = dr(sCols(i))
Next
Next
dt.save
-- 发布时间：2017/7/29 15:44:00
--
只能对单表查询进行更新，如
cmd.CommandText = "select * from 表A"
dt = cmd.ExecuteReader(true)
或者使用sql进行更新
cmd.CommandText = "update 表A set 第一列=\'xxx\' where 主键=\'xxx\'"
cmd.ExecuteNonQuery
-- 发布时间：2017/7/30 13:05:00
--
这种更新方式没法根据表格里的内容更新吧？需要在代码里写入更新后的值？
-- 发布时间：2017/7/30 15:55:00
--
加入一列标记列，在datacolchanged事件写代码
e.datarow("修改") = true
你写按钮保存的时候，就对应的查找修改各个表的数据
For each dr As DataRow in DataTables("表A").select("修改 = true")
Dim fdr As DataRow = Datatables("表B").find("第一列 = \'" & dr("第一列") & "\'")
If fdr Is Nothing Then
fdr = Datatables("表B").addnew
End If
fdr("第一列") = dr("第一列")
fdr("第二列") = dr("第二列")
Next
[此贴子已经被作者于2017/7/30 15:56:12编辑过]