When it queries the database, it stores a copy of the original data
in, ironically, an originalData node that is not exposed in the UI.
When you submit it diffs the current data vs. the original data and
generates the appropriate insert, update, and delete statements.
I have a form that's created from an SQL Server database connection. The
main connection consists of a purchase_request which is joined (using Shape)
to a purchase_request_item and also a purchase_request_discussion. I can
update existing purchase requests just fine, but when I try to enter a new
purchase request, I get this error:
[0x80040E38][Microsoft Cursor Engine] Row cannot be located for updating.
Some values may have been changed since it was last read.
It's as if Infopath is trying to run an Update when it should be running an
Insert.
Does either table have a trigger?
Yes, there is a trigger. When I remove that trigger, the problem goes away.
Thought I had the logic sorted out so the trigger wouldn't have anything to
insert / update on new inserts, but it appears to not be working. So, back
to trigger debugging.
Thanks for the help!
Don't thank me yet, I didn't help yet.
Here is the code for a trigger I had created on one of my databases:
CREATE TRIGGER transferAssetOnUpdate ON dbo.[df-assets]
AFTER UPDATE
AS
BEGIN
PRINT 'transferAssetOnUpdate - UPDATE'
DELETE from [df-assets]
WHERE asset_pk NOT IN (SELECT asset_pk FROM inserted)
AND asset_num IN (SELECT asset_num FROM inserted)
END
When I would update rows in my database I would sometimes see the
error you got. Running a trace on the server, the top-level RPC call
was
exec sp_executesql N'
UPDATE "df-assets"."dbo"."df-assets" SET "dual_proc"=@P1 WHERE
"asset_pk"=@P2 AND "dual_proc"=@P3;
UPDATE "df-assets"."dbo"."df-assets" SET "dual_proc"=@P4 WHERE
"asset_pk"=@P5 AND "dual_proc"=@P6
', N'@P1 bit,@P2 int,@P3 bit,@P4 bit,@P5 int,@P6 bit', 1, 308, 0, 0,
307, 1
and that translates into the following sequence of SQL statements
(including trigger code firing):
1. UPDATE "df-assets"."dbo"."df-assets" SET "dual_proc"=1 WHERE
"asset_pk"=308 AND "dual_proc"=0;
-- transferAssetOnUpdate
2. DELETE from [df-assets] where asset_pk NOT IN (SELECT asset_pk FROM
inserted) and asset_num IN (SELECT asset_num FROM inserted)
3. UPDATE "df-assets"."dbo"."df-assets" SET "dual_proc"=0 WHERE
"asset_pk"=307 AND "dual_proc"=1;
-- transferAssetOnUpdate
4. DELETE from [df-assets] where asset_pk NOT IN (SELECT asset_pk FROM
inserted) and asset_num IN (SELECT asset_num FROM inserted)
The ADO concurrency model relies on the rowcount to detect when a row
matches given the WHERE clause. When rowcount = 0, ADO assumes that
there was no row match and raises a concurrency error.
The problem here is that the DELETE statement in the trigger ( 2. )
doesn't match any rows (there is no duplicates), which trick ADO into
thinking there was a concurrency conflict when there was none!
The solution is to add "set nocount" statements in the trigger code,
so that the rowcount accurately reflects the effect of the UPDATE
statement.
i.e. the correct solution for my example would be this
BEGIN
SET NOCOUNT ON
PRINT 'transferAssetOnUpdate - UPDATE'
DELETE from [df-assets]
WHERE asset_pk NOT IN (SELECT asset_pk FROM inserted)
AND asset_num IN (SELECT asset_num FROM inserted)
SET NOCOUNT FF
END
If you still have trouble, try posting your trigger code.
Regards,
Steve
