Re: "Row cannot be located for updating. Some values may have been changed since it was last read."

From: Brian Bushay TeamB (BBushay_at_Nmpls.com)
Date: 12/11/04

  • Next message: Brian Bushay TeamB: "Re: error updating Tadoquery object with a trigger..." 
    Date: Fri, 10 Dec 2004 19:30:10 -0600

    >When using TADOQuery with Delphi 5 I am hitting the error listed in the subject line and can not determine why?
    > Everything works fine most of the time but breaks down when I try to update any queires where I join a table to itself.
    > e.g. SELECT * FROM Employee LEFT OUTER JOIN EMPLOYEE as e2 on e2.employee = employee.superviser
    >I was under the impression that I undestood the causes for this bug, but this problem seems to be proving me wrong.
    > I have no triggers on the table employee. I have tried using Properties['Search Criteria'] = adCriteriaKey and I am
    >pretty sure my 'Resync Command' works although it never gets to resyncing.
    The property you need to set is "Update Criteria" and Resync won't work with a
    join if you unless you have configured the "Resync Command" Property

    >I ran a profiler on this and ADO is forwarding this command to SQL
    >exec sp_executesql N'UPDATE "ADOTest".."employee" SET "EmployeeName"=@P1 WHERE "Employee"=@P2
    >AND "EmployeeName"=@P3 AND "Employee" IS NULL', N'@P1 varchar(7),@P2 varchar(36),
    >@P3 varchar(7)', '1', 'C0A67307-585E-4617-8124-2E91F116586D', '2'
    >
    >Obviously it will think that a row is deleted since there is no way employee is the guid and is null at the same time.
    >This example does not use adCriteriaKey but if I did the effect would be the same it just
    > would not compare the EmployeeName.
    That SQL looks like it is being generated by a Resync that doesn't have a proper
    "Resync Command" configured. The Join is where you have to Employee fields

    >
    >I am 99 % sure this is a TADODataSet bug because when I use the following straight recordset code everything
    > works fine.
    You will loose that bet

    >
    >rs := CreateComObject(CLASS_RECORDSET) AS _RecordSet;
    > conn := CreateComObject(CLASS_Connection) AS _Connection;
    > conn.ConnectionString := 'Provider=sqloledb;Data Source=name;Initial Catalog=ADOTest;User Id=sa;Password=pass;';
    > conn.CursorLocation := adUseClient;
    > conn.Open(conn.ConnectionString, 'sa', '', 0);
    > rs.Open('SELECT * FROM Employee LEFT OUTER JOIN EMPLOYEE as e2 on e2.employee = employee.superviser',
    > conn,
    > adOpenKeyset,
    > adLockOptimistic,
    > 0);
    > if not rs.EOF then
    > begin
    > MessageBox(0, PChar(VarToStr(rs.Fields[3].Value)), 'Test', mb_OK);
    > rs.Fields[3].Value := '3';
    > rs.Update(EmptyParam, EmptyParam);
    > end;
    >
    >but as soon as I envolve TADOQuery it breaks.
    >
    >Actually that's not entirely true. If I open the ADO recordset and then assign it to the TADOQuery it works as well.
    >
    >rs := CreateComObject(CLASS_RECORDSET) AS _RecordSet;
    > conn := CreateComObject(CLASS_Connection) AS _Connection;
    > conn.ConnectionString := 'Provider=sqloledb;Data Source=name;Initial Catalog=ADOTest;User Id=sa;Password=pass;';
    > conn.CursorLocation := adUseClient;
    > conn.Open(conn.ConnectionString, 'sa', '', 0);
    > rs.Open('SELECT * FROM Employee LEFT OUTER JOIN EMPLOYEE as e2 on e2.employee = employee.superviser',
    > conn,
    > adOpenKeyset,
    > adLockOptimistic,
    > 0);
    >
    > {if not rs.EOF then
    > begin
    > MessageBox(0, PChar(VarToStr(rs.Fields[3].Value)), 'Test', mb_OK);
    > rs.Fields[3].Value :=5';
    > rs.Update(EmptyParam, EmptyParam);
    >
    > end;}
    > ADOQuery1.Recordset := rs;
    > ADOQuery1.Open;
    > if not ADOQuery1.EOF then
    > begin
    > ADOQuery1.Edit;
    > ADOQuery1.FieldByName('EmployeeName').AsString := '6';
    > ADOQuery1.Post;
    > end;
    >
    I believe if you open a recordset directly with ADO it defaults to
    "Update Criteria" = AdcriteriaKey while Borland's Ado components default to
    adCriteriaUpdCols to be compatible with the default for Tquery

    >If I use straight TADOQuery however, with this query SELECT * FROM Employee LEFT OUTER JOIN EMPLOYEE as e2 on e2.employee = employee.superviser
    >
    >ADOQuery1.Open;
    > if not ADOQuery1.EOF then
    > begin
    > ADOQuery1.Edit;
    > ADOQuery1.FieldByName('EmployeeName').AsString := '5';
    > ADOQuery1.Post;
    > end;
    >
    >when I try to update I get the error "Row cannot be located for updating. Some values may have been changed since it was last read.", which I suspect is the result of what I am seing in the profiler. This only happens when I join the same table. If I removed the outer join or joined a different row this error goes away. It seems like this has something to do with the way TADOQuery creates and opens the recordset, but so far I have not been able to find it. Any ideas are very welcome. 

    --
Brian Bushay (TeamB)
Bbushay@NMPLS.com
  • Next message: Brian Bushay TeamB: "Re: error updating Tadoquery object with a trigger..."

    Relevant Pages