Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Update on an ADO Dataset that is linked to a view Expand / Collapse
Posted Sunday, November 18, 2012 11:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 3, 2015 2:06 AM
Points: 7, Visits: 29

I have a table in my DB that has my Product information. this table has a tree structure (each record has a ParentId field to refer to its upper level record, it has 5 levels).
I have a view on this table. in this view I have joined my Product table to itself several times to have my product and its grouping information on higher level all in one row.
now I want to update row on the grid connected to my view (no Insert/Delete). I get this error:
"row cannot be located for updating. some values may have been changed since it was last read"
I think this is because SQL cant find the primary field in my view. I tried to use Instead Of Update trigger but
it seems the error occurs before the execution of the trigger. does anyone have any suggestion?
this is my view:
SELECT _Class.PartId AS ClassId,
_Class.ParentId AS ClassParentId,
_Class.PartCode AS ClassCode,
_Class.PartName AS ClassName,

_Group.PartId AS GroupId,
_Group.ParentId AS GroupParentId,
_Group.PartCode AS GroupCode,
_Group.PartName AS GroupName,

_Category.PartId AS CategoryId,
_Category.ParentId AS CategoryParentId,
_Category.PartCode AS CategoryCode,
_Category.PartName AS CategoryName,

_Part.PartId AS PartId,
_Part.ParentId AS PartParentId,
_Part.PartCode AS PartCode,
_Part.PartName AS PartName,

_Brand.PartId AS BrandId,
_Brand.ParentId AS BrandParentId,
_Brand.PartCode AS BrandCode,
_Brand.PartName AS BrandName,

_Brand.[P/N] AS [P/N],
_Brand.KindId AS KindId,
FROM prt_Part AS _Class INNER JOIN
prt_Part AS _Group ON _Class.PartId = _Group.ParentId INNER JOIN
prt_Part AS _Category ON _Group.PartId = _Category.ParentId INNER JOIN
prt_Part AS _Part ON _Category.PartId = _Part.ParentId INNER JOIN
prt_Part AS _Brand ON _Part.PartId = _Brand.ParentId
Post #1386153
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse