使用AdoQuery,sql语句为
Select a.*,b.*
from (
Select a.ReceiveID,a.ProductID,b.ProductModel,d.RepairType,a.ProductLot,a.Number,
a.ApprovalState as App, c.DetectID,c.MalfunctionReason1ID,c.MalfunctionReason2ID,
c.MalfunctionReason3ID,c.TestResultID,c.ChargeState,c.Costs,c.OperateID,
c.OperateTime,c.ApprovalID,c.ApprovalTime,c.ApprovalState,c.Memo
from ReceiveBody a
left outer join ProductModel b on a.ProductModelID=b.ProductModelID
left outer join Detect c on a.ProductID=c.ProductID
left outer join RepairType d on a.RepairTypeID=d.RepairTypeID ) a
left outer join (Select a.DetectID
from Detect a
left outer join TestResult b on a.TestResultID=b.TestResultID
left outer join MalfunctionReason1 c on a.MalfunctionReason1ID=c.MalfunctionReason1ID
left outer join MalfunctionReason2 d on a.MalfunctionReason2ID=d.MalfunctionReason2ID
left outer join MalfunctionReason3 e on a.MalfunctionReason3ID=e.MalfunctionReason3ID) b
on a.DetectID = b.DetectID
where a.App=1执行qry1.edit后,输入内容,再执行qry1.post,就提示无法为更新定位行。一些值可能已经在最后一次读取后已更改。谁能帮帮忙
Select a.*,b.*
from (
Select a.ReceiveID,a.ProductID,b.ProductModel,d.RepairType,a.ProductLot,a.Number,
a.ApprovalState as App, c.DetectID,c.MalfunctionReason1ID,c.MalfunctionReason2ID,
c.MalfunctionReason3ID,c.TestResultID,c.ChargeState,c.Costs,c.OperateID,
c.OperateTime,c.ApprovalID,c.ApprovalTime,c.ApprovalState,c.Memo
from ReceiveBody a
left outer join ProductModel b on a.ProductModelID=b.ProductModelID
left outer join Detect c on a.ProductID=c.ProductID
left outer join RepairType d on a.RepairTypeID=d.RepairTypeID ) a
left outer join (Select a.DetectID
from Detect a
left outer join TestResult b on a.TestResultID=b.TestResultID
left outer join MalfunctionReason1 c on a.MalfunctionReason1ID=c.MalfunctionReason1ID
left outer join MalfunctionReason2 d on a.MalfunctionReason2ID=d.MalfunctionReason2ID
left outer join MalfunctionReason3 e on a.MalfunctionReason3ID=e.MalfunctionReason3ID) b
on a.DetectID = b.DetectID
where a.App=1执行qry1.edit后,输入内容,再执行qry1.post,就提示无法为更新定位行。一些值可能已经在最后一次读取后已更改。谁能帮帮忙
比如a表和b表的主键都是"ID"那么保留一个主键就可以了
with adoquery1 do
begin
close;
sql.text := 'update table set .... where ...';
ExecSql;
end;
重复键名是有DetectID ,可是按你说的保留一个,我不知道这个怎么改,你能帮下忙吗
晕,关系图还是没上去
這麼復雜的一個SQL出來後,你想更新哪個表,哪個表不更新,你直接說出來嗎?如果不能,你想下ADO能不能處理。你有這麼一句:
执行qry1.edit后,输入内容,再执行qry1.post,就提示无法为更新定位行。一些值可能已经在最后一次读取后已更改。谁能帮帮忙那說明這只是UPDATE,這樣的話就直接寫UPDATE語句吧
据个例子说
ReceiveBody(接修登记信息) Detect(检测登记信息)
工作人员接到一个客户返回的维修机器,先进行接修登记,然后检测人员在根据接修登记填写检测记录
例如
第一步,接到机器进行登记
ReceiveBody
ReceiveID ProductModel Number
SZ200906290001 AAA 2
SZ200906290002 BBB 3
SZ200906290003 CCC 4
第二部,检测机器进行登记(此时需要在dbgrid中调出之前的接修信息,然后在每条后面添加检测记录)
ReceiveBody Detect
ReceiveID ProductModel Number DetectID TestResult ChargeState Costs
SZ200906290001 AAA 2
SZ200906290002 BBB 3
SZ200906290003 CCC 4
------------------------
出现这种错误比较常见的情况有两种:
1.更新字段设定了默认值,当ADO在保存后与原来的值不一样就报错。
2.输入的内容长度超出字段设定的长度
------------------------楼主这种多表更新方式不合适,如果万一要这么做话可以设定qry1的属性LockType=ltBatchOptimistic,然后在AfterPost事件下根据结果写UPdate内容的代码 ,在qry1关闭前执行qry1.CancelUpdate()语句
with adoquery1 do
begin
close;
sql.clear;
sql.text := 'update table set .... where ...';
ExecSql;
end;