如何更新 Join 表？ ( 积分: 80 )

如何更新 Join 表？

這個只能是用存儲過程來更新吧，然后Refresh吧

还有其他方法吗？
我想知道怎样拆分clientdataset里的字段.....然后分别更新

给你一段源码，相信对你应该有所帮助，用这种方法，不仅能更新JOIN的多个表，还可以把DELETE变成UPDATE，把Insert变成DELETE等，方便着吧
控制，ADOQuery+ DataSetProvider + ClientDataSet + DataSource
procedure TfrSaleDetails.DataSetProviderAms_CostsBeforeUpdateRecord(
Sender: TObject;
SourceDS: TDataSet;
DeltaDS: TCustomClientDataSet;
UpdateKind: TUpdateKind;
var Applied: Boolean);
const
SQLupdate ='Update Ams_Costs set ID=:ID,BookNo=:BookNo,Real_No=:Real_No,Po_No=o_No,'
+'Factory=:Factory,BillDays=:BillDays,GoodsCode=:GoodsCode,Hs_Code=:Hs_Code,'
+'GoodsName=:GoodsName,Quantity=:Quantity,Unit=:Unit,CostCurr=:CostCurr,'
+'CostAmt=:CostAmt,FrozenAmt=:FrozenAmt,AddedTax=:AddedTax,RefundTax=:RefundTax,'
+'RefundsAmt=:RefundsAmt,NotifyInv=:NotifyInv,RcvdInv=:RcvdInv,'
+'SaleCurr=:SaleCurr,SaleAmt=:SaleAmt,RealGoodsCode=:RealGoodsCode,'
+'RealGoodsName=:RealGoodsName,Remark=:Remark'
+' Where Seque=:Seque';
SQlInsert ='IF (Select Count(ID) From Ams_Costs Where ID=:ID and Real_No=:Real_no'
+' and Factory=:Factory and GoodsName=:GoodsName)=0'+#13
+'Insert Into Ams_Costs (ID,BookNo,Real_No,Po_No,Factory,BillDays,'+#13
+'GoodsCode,Hs_Code,GoodsName,Quantity,Unit,CostCurr,CostAmt,'+#13
+'FrozenAmt,AddedTax,RefundTax,RefundsAmt,NotifyInv,RcvdInv,'+#13
+'SaleCurr,SaleAmt,RealGoodsCode,RealGoodsName,Remark)'+#13
+'ValuesID,''%S'',:Real_No,o_No,:Factory,:BillDays,'+#13
+':GoodsCode,:Hs_Code,:GoodsName,:Quantity,:Unit,:CostCurr,:CostAmt,'+#13
+':FrozenAmt,:AddedTax,:RefundTax,:RefundsAmt,:NotifyInv,:RcvdInv,'+#13
+':SaleCurr,:SaleAmt,:RealGoodsCode,:RealGoodsName,:Remark)'+#13
+'else
'+#13
+'Update Ams_Costs Set BookNo=IsNull(BookNo,'''')+''%S'''+#13
+' Where ID=:ID and Real_No=:Real_no and GoodsName=:GoodsName and'
+' Factory=:Factory and CharIndex(''%S'',BookNo)=0';
//注意，使用前要Format传递三个帐套号}
SQlDelete ='IF (Select Top 1 RTrim(BookNo) From Ams_Costs Where Seque=:Seque)=''%S'''+#13
+' Delete From Ams_Costs Where Seque=:Seque'+#13
+'else
'+#13
+'begin
'+#13
+' Update Ams_Costs Set BookNo=Replace(IsNull(BookNo,''''),''%S'','''') '
+' Where Seque=:Seque and CharIndex(''%S'',BookNo)>0'+#13
+' IF Exists(Select ID From Ams_Costs Where Seque=:Seque and RTrim(BookNo)='''')'+#13
+' Delete From Ams_Costs Where Seque=:Seque'
+#13+'End';
//注意，使用前要Format传递三个帐套号
var i:integer;
DelStr,tmpStr:string;
begin
if UpdateKind = ukModify then
//因为添加与删除产生的记录信息相同，故不重得记录
AMSAutoLogs(utConn, ClientDataSetAms_Costs.FieldByName('ID').AsString,
'NotifyInv', Deltads.FieldByName('NotifyInv'),
[Deltads.FieldByName('Factory'),Deltads.FieldByName('GoodsName')],UpdateKind);
AMSAutoLogs(utConn, ClientDataSetAms_Costs.FieldByName('ID').AsString,
'RcvdInv', Deltads.FieldByName('RcvdInv'),
[Deltads.FieldByName('Factory'),Deltads.FieldByName('GoodsName')],UpdateKind);
with ADOQuerySharedo
begin
case UpdateKind of
ukModify:
begin
{$ifdef USERUPDATE}
showmessage('更改');
{$Endif}
SQL.Clear;
Sql.Text:=Trim(SQLUpdate);
//删除语句中未涉及更改的字段
//一方面是加快更新速度,另一方面是不这样做,当前Blob,Memo字段容要被清除
//同时要注意,表达式中字段与参数之间是field=:Field的形式,
//中间无空格等,字段名与参数要一致
//前后要紧跟,不能有空格
For i:=0 to DeltaDS.FieldCount - 1do
begin
TmpStr:=Trim(DeltaDS.Fields.FieldName);
if (Assigned(Parameters.FindParam(TmpStr))) then
if VarIsEmpty(DeltaDS.Fields.NewValue) then
begin
DelStr:=UpperCase(TmpStr+'=:'+TmpStr);
TmpStr:=UpperCase(Sql.Text);
if Pos(DelStr,TmpStr)<Pos('WHERE',TmpStr) then
begin
//不更改Where以后的字段
if Pos(','+DelStr,TmpStr)>0 then
//不是第一个
System.Delete(tmpstr,pos(','+DelStr,TmpStr),Length(DelStr)+1)
else
if Pos(DelStr+',',TmpStr)>0 then
System.Delete(TmpStr,Pos(DelStr+',',TmpStr),Length(DelStr)+1) //位于第一个
else
if Pos(DelStr,TmpStr)>0 then
System.Delete(TmpStr,pos(DelStr,TmpStr),Length(DelStr)+1);
//仅剩一个了
end;
Sql.Text:=TmpStr;
end;
end;
//For
end;
ukInsert:
begin
{$ifdef USERUPDATE}
showmessage('插入');
{$Endif}
SQL.Clear;
Sql.Text:=Format(SqlInsert,[utBookNo,utBookNo,utBookNo]);
end;
ukDelete:
begin
{$ifdef USERUPDATE}
showmessage('删除');
{$Endif}
SQL.Clear;
Sql.Text:=Format(SqlDelete,[utBookNo,utBookNo,utBookNo]);
end;
end;
//first case
For I:=0 to Parameters.Count - 1do
if Not VarisEmpty(Parameters.Value) then
begin
Parameters.Attributes:=[paNullable];
Parameters.Value:=Null;
TmpStr:=Trim(Parameters.Name);
if Assigned(DeltaDS.FindField(TmpStr)) then
with DeltaDS.FindField(tmpStr)do
begin
if Parameters.DataType<>DataType then
Parameters.DataType:=DataType;
if Parameters.Size <> Size then
Parameters.Size:=Size;
Parameters.Value:=NewValue;
if VarIsEmpty(NewValue) then
if VarIsEmpty(OldValue) then
Parameters.Value:=Value
else
Parameters.Value:=OldValue;
end;
end;
{$ifdef USERUPDATE}
showmessage(Text);
{$Endif}
ExecSQL;
Applied:=True;
end;
end;

事件写在DataSetProvider的BeforeUpdate中

if UpdateKind = ukModify then
//因为添加与删除产生的记录信息相同，故不重得记录
AMSAutoLogs(utConn, ClientDataSetAms_Costs.FieldByName('ID').AsString,
'NotifyInv', Deltads.FieldByName('NotifyInv'),
[Deltads.FieldByName('Factory'),Deltads.FieldByName('GoodsName')],UpdateKind);
AMSAutoLogs(utConn, ClientDataSetAms_Costs.FieldByName('ID').AsString,
'RcvdInv', Deltads.FieldByName('RcvdInv'),
[Deltads.FieldByName('Factory'),Deltads.FieldByName('GoodsName')],UpdateKind);
以上代码你用不着，我是用来记录用户操作内容用的。

thank you levi!
慢慢学习一下