AdoQuery在Post时出现错误无法为更新行集定位，一些值可能已在最后读取后改变(200分)

AdoQuery在Post时出现错误：“无法为更新行集定位，一些值可能已在最后读取后改变”<br>使用环境:<br>1.delphi7<br>2.数据控件:dbgridEh 3.6<br>3.sql 2000数据库<br>说明:<br>1.用dbgridEh对一个有触发器的表进POS时,会出现上述错误.<br>2.通过跟踪,主要是该触发器有一段对其他表进行更新操作的语句引起,如果删除该段语句不会出错.<br>3.而在SQL2000查询分析中进行同样的操作时,又不会出现错误. 通过DBGRIDEH进行提交就会.<br>4.触发器如下(主要对b_CSELL进行pos操作,同步更新一个表PUB_STOCK):

ALTER &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TRIGGER [Tu_Csell] ON dbo.B_Csell<br>--with encryption<br>&nbsp; FOR &nbsp;UPDATE <br>AS<br>--//销售发货单<br>begin<br>&nbsp; declare<br>&nbsp; &nbsp; @numrows &nbsp;int,<br>&nbsp; &nbsp; @numnull &nbsp;int,<br>&nbsp; &nbsp; @errno &nbsp; &nbsp;int,<br>&nbsp; &nbsp; @errmsg &nbsp; varchar(255),<br>&nbsp; &nbsp; @costtype char(10),<br>&nbsp; &nbsp; @updatecnt int<br>&nbsp; &nbsp; &nbsp; <br>&nbsp; set @updatecnt=@numrows<br><br>&nbsp; --记录为空返回-- <br>&nbsp; select &nbsp;@numrows = @@rowcount<br>&nbsp; if @numrows = 0<br>&nbsp; &nbsp; &nbsp;return<br>&nbsp;<br>&nbsp; --结案--<br>&nbsp; if update(F_stateID)<br>&nbsp; begin<br>&nbsp; &nbsp; set @numrows=(select count(*) from inserted<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; where isnull(F_stateID,'0')='6')<br>&nbsp; &nbsp; if @numrows&gt;0<br>&nbsp; &nbsp; begin<br>&nbsp; &nbsp; if update(F_CsellID) or update(F_Csell) or update(F_note) or update(F_lock)<br>&nbsp; &nbsp; &nbsp; &nbsp;or update(F_create) or update(F_crdate) or update(F_firm)<br>&nbsp; &nbsp; &nbsp; &nbsp;or update(F_fdate) or update(F_Audit) or update(F_ADate) <br>&nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; raiserror 130100 '不可以同时修改结案和其他内容!'<br>&nbsp; &nbsp; &nbsp; rollback &nbsp;transaction &nbsp;<br>&nbsp; &nbsp; &nbsp; return<br>&nbsp; &nbsp; end<br>&nbsp; &nbsp; end<br>&nbsp; end<br>&nbsp; &nbsp; &nbsp; <br><br>&nbsp; ----------------------发货单(加上下段语句时有出错提示)----------------------- &nbsp; &nbsp;<br>&nbsp; if update(F_stateID)<br>&nbsp; &nbsp; &nbsp;and exists (select 1 from deleted &nbsp; --非反结案<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;where isnull(F_stateID,'0')&lt;&gt;'6')<br>&nbsp; &nbsp; &nbsp;and exists (select 1 from inserted &nbsp; --非结案<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;where isnull(F_stateID,'0')&lt;&gt;'6')<br>&nbsp; begin<br>&nbsp; &nbsp; --审核--<br>&nbsp; &nbsp; if exists (select 1 from inserted<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; where isnull(F_stateID,'0')='3')<br>&nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; --审核日期--<br>&nbsp; &nbsp; &nbsp; if exists (select 1 from inserted<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;where isnull(F_ADate,'')=''<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and isnull(F_stateID,'0')='3')<br>&nbsp; &nbsp; &nbsp; begin &nbsp; <br>&nbsp; &nbsp; &nbsp; &nbsp; raiserror 130100 '审核日期不可以为空值！'<br>&nbsp; &nbsp; &nbsp; &nbsp; rollback &nbsp;transaction<br>&nbsp; &nbsp; &nbsp; &nbsp; return<br>&nbsp; &nbsp; &nbsp; end<br><br>&nbsp; &nbsp; &nbsp;--计算库存-----------<br>&nbsp; &nbsp; &nbsp;--添加表中没有的商品记录<br>&nbsp; &nbsp; &nbsp; insert into pub_stock (F_shopID,F_goodsID,F_goods,F_goitem,F_gostyle,F_unit)<br>&nbsp; &nbsp; &nbsp; select DISTINCT i1.F_wareID,t1.F_goodsID,t1.F_goods,t1.F_goitem,t1.F_gostyle,t1.F_unit<br>&nbsp; &nbsp; &nbsp; &nbsp; from B_Csellsub t1,inserted i1<br>&nbsp; &nbsp; &nbsp; &nbsp; where t1.F_CsellID=i1.F_CsellID<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; and not exists (select 1 from pub_stock c<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; where i1.F_wareID=c.F_shopID<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; and t1.F_goodsID=c.F_goodsID)<br><br>&nbsp; &nbsp; &nbsp; --审核<br>&nbsp; &nbsp; &nbsp; update u1 set F_kqty=isnull(F_kqty,0) - i1.F_qty,F_pqty=isnull(F_pqty,0) - i1.F_qty <br>&nbsp; &nbsp; &nbsp; &nbsp; from pub_stock u1,(select i1.F_wareID as F_shopID,t1.F_goodsID,sum(isnull(t1.F_qty,0)) as F_qty<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from B_Csellsub t1,inserted i1,deleted d1<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;where t1.F_CsellID=i1.F_CsellID<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; and t1.F_CsellID=d1.F_CsellID<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and isnull(i1.F_stateID,'')&lt;&gt;isnull(d1.F_stateID,'')<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and isnull(d1.F_stateID,'')&lt;&gt;'6'<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and isnull(i1.F_stateID,'')='3'<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;group by i1.F_wareID,t1.F_goodsID) i1 <br>&nbsp; &nbsp; &nbsp; &nbsp; where u1.F_goodsID=i1.F_goodsID <br> &nbsp;and u1.F_shopID=i1.F_shopID<br>&nbsp; &nbsp; end<br>&nbsp; &nbsp; else<br>&nbsp; &nbsp; begin <br>&nbsp; &nbsp; &nbsp; --反审核<br>&nbsp; &nbsp; &nbsp; update u1 set F_kqty=isnull(F_kqty,0) + i1.F_qty,F_pqty=isnull(F_pqty,0) + i1.F_qty <br>&nbsp; &nbsp; &nbsp; &nbsp; from pub_stock u1,(select i1.F_wareID as F_shopID,t1.F_goodsID,sum(isnull(t1.F_qty,0)) as F_qty<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from B_Csellsub t1,inserted i1,deleted d1<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;where t1.F_CsellID=i1.F_CsellID<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; and t1.F_CsellID=d1.F_CsellID<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and isnull(i1.F_stateID,'')&lt;&gt;isnull(d1.F_stateID,'')<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and isnull(d1.F_stateID,'')='3'<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and isnull(i1.F_stateID,'')&lt;&gt;'6'<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;group by i1.F_wareID,t1.F_goodsID) i1 <br>&nbsp; &nbsp; &nbsp; &nbsp; where u1.F_goodsID=i1.F_goodsID <br> &nbsp;and u1.F_shopID=i1.F_shopID<br>&nbsp; &nbsp; <br>&nbsp; &nbsp; end<br>&nbsp; end<br><br>end

数据还处于编辑状态,你执行了提交操作,所以报这个错,<br>可以在企业管理器中做这个实验:<br>1.打开一个表<br>2.编辑某一条记录<br>3.使记录的某个字段值处于编辑状态<br>4.点击感叹号执行<br>结果:你就可以看到这个错了

3.而在SQL2000查询分析中进行同样的操作时,又不会出现错误. 通过DBGRIDEH进行提交就会.

这个问题我知道为什么，你的表加一个自增的主键字段就行了。

b_CSELL该表为主表,本身设有主键.

有自增字段吗？引起的原因就是因为有多行数据是一样的，数据库不知道去更新那一行。

你可以再加一个自增字段。100%能解决问题的。这个问题我遇到N回了。

这个问题我也碰到过N次，解决了，但是没找到根本原因<br>&nbsp;顶下

还有一种情况就是某个字段不允许为空，但却给这个字段赋空值。

增加字段不行(在表没有主键时该方法才有效),请看说明:<br><br>说明:<br>1.用dbgridEh对一个有触发器的表进POS时,会出现上述错误.<br>2.通过跟踪,主要是该触发器有一段对其他表进行更新操作的语句引起,如果删除该段语句不会出错.<br>3.而在SQL2000查询分析中进行同样的操作时,又不会出现错误. 通过DBGRIDEH进行提交就会.<br>4.触发器如下(主要对b_CSELL进行pos操作,同步更新一个表PUB_STOCK):

也就是说,触发器中没有关于对其他表操作的代码时,操作是没有问题的.

原因已经说明了，举个例子<br>create table test (<br>a varchar(10),<br>b varchar(10),<br>c varchar(10))<br><br>insert into test values ('a','b','c')<br>insert into test values ('a','b','c')<br>insert into test values ('a','b','c')<br>insert into test values ('a','b','c')<br>insert into test values ('a','b','c')<br><br>然后你到企业管理器中找以这个表并打开，你选中一行数据删除试试，肯定报类似的错误。

你出现的原因是表没有主键,而我的表是有主键的.且没有触发器相关代码时,操作不会出错.

太简单了！<br>在触发器的第一行加：<br>set nocount=on<br>在最后一行加<br>set oncount=off<br>就解决了你的问题。

照楼上办法,还是不行:<br>在触发器第一行加了:<br>set nocount on<br>go<br>最后一行加了:<br>set nocount off<br>go

1.去掉字段默认值<br>2.set nocount on<br>&nbsp; set nocount off

问题解决了.设: ADOQUER.CursorLocation =adUseServer 就行了.<br>注: 1.因是AQDOQUER设置问题,所以在SQL查询分析器执行不会有错.<br>&nbsp; &nbsp; 2.去掉字段默认值,只针对没有主键的表有用,而且我们也极不希望失去这个功能.

虽然是自已找到了解决方法.也感谢各位的热情参与.钱就平分给各位了.