Re: 'Row cannot be located for updating...' error.
From: shadow123 (shadow123_at_discussions.microsoft.com)
Date: 12/13/04
- Next message: Wart: "Re: cancel ADO2.8 asynchron queries over WAN, takes long time"
- Previous message: shadow123: ""Unique Table" and "Resync Command" problems with self-ref query"
- Next in thread: Stephen Howe: "Re: 'Row cannot be located for updating...' error."
- Reply: Stephen Howe: "Re: 'Row cannot be located for updating...' error."
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 13 Dec 2004 14:27:04 -0800
I have also hit this problem, but I am unable to get this to work with views
as the problem persists. Does anyone have an example of how this is supposed
to work with views?
Thanks a lto.
"Warren" wrote:
> What I ended up doing was replacing the self join table (TableA_1) with a
> view and setting the "Update using view rules" View property on. This allowed
> me to update the primary table (TableA). I also set the "Unique Table"
> property to "TableA".
>
> Update using view rules
>
> Indicates that all updates and insertions to the view will be translated by
> Microsoft Data Access Components (MDAC) into SQL statements that refer to the
> view, rather than into SQL statements that refer directly to the view’s base
> tables. (In some cases, MDAC manifests view update and view insert operations
> as updates and inserts against the view’s underlying base tables. By
> selecting Update using view rules, you can ensure that MDAC generates update
> and insert operations against the view itself.)
>
>
>
> "Warren" wrote:
>
> > Val
> >
> > Thanks again for your reply. I've added the PK for the aliased table but
> > still get the error when attempting to update.
> >
> > I created a simple table to use for this test and to eliminate relationships
> > etc. from the equation.
> >
> > Structure for Table1 is as follows:
> > fldPK (int - identity)
> > fldFK (int)
> > fldText (nvarchar(50))
> >
> > strsql = "SELECT dbo.Table1.fldPK, dbo.Table1.fldFK, dbo.Table1.fldText,
> > Table1_1.fldPK AS Expr1, Table1_1.fldFK AS Expr2 "
> > strsql = strsql & "FROM dbo.Table1 LEFT OUTER JOIN dbo.Table1 Table1_1
> > ON dbo.Table1.fldFK = Table1_1.fldPK "
> > strsql = strsql & "WHERE (dbo.Table1.fldPK = 1)"
> >
> > mrstTest.Open strsql, mconDB, adOpenStatic, adLockBatchOptimistic
> >
> > mrstTest.Properties("Unique Schema") = "dbo"
> > mrstTest.Properties("Unique Catalog") = "AMPRO"
> > mrstTest.Properties("Unique Table") = "Table1"
> > ' mrstTest.Properties("Update Criteria") = mrstTest.Fields("fldPK")
> >
> > mrstTest.Fields("fldText").Value = "AAAAA"
> > mrstTest.UpdateBatch
> >
> > Thanks again for your help.
> >
> > "Val Mazur" wrote:
> >
> > > Hi,
> > >
> > > Try to include primary key field(s), for the second table (I know it is the
> > > same, but alias is different) into selection. It is possible that provider
> > > cannot find PK information, which it requires to update table.
> > >
> > > --
> > > Val Mazur
> > > Microsoft MVP
> > >
> > >
> > > "Warren" <Warren@discussions.microsoft.com> wrote in message
> > > news:9D40591E-9B56-4004-8E65-57A7C501B480@microsoft.com...
> > > > SELECT dbo.tblJrcJob.*, tblJrcJob_1.fldRecurringJobDescription AS Expr1
> > > > FROM
> > > > dbo.tblJrcJob LEFT OUTER JOIN dbo.tblJrcJob tblJrcJob_1 ON
> > > > dbo.tblJrcJob.fldLinkedRecurringJobID = tblJrcJob_1.fldRecurringJobID
> > > >
> > > > mrstTest.Open strsql, mconDB, adOpenStatic, adLockBatchOptimistic
> > > > mrstTest.Fields("fldRecurringJobComments").Value = "AAAAA"
> > > > mrstTest.UpdateBatch ' Error occurs here
> > > >
> > > > Have tried with adLockOptimistic and mrstTest.Update with the same result.
> > > > If I remove the self join it works fine.
> > > >
> > > > Thanks
> > > >
> > > > Warren
> > > >
> > > > "Val Mazur" wrote:
> > > >
> > > >> hi,
> > > >>
> > > >> Could you show an example of the SP?
> > > >> --
> > > >> Val Mazur
> > > >> Microsoft MVP
> > > >>
> > > >>
> > > >> "Warren" <Warren@discussions.microsoft.com> wrote in message
> > > >> news:953B4334-88AE-4647-8EC2-914905096F9C@microsoft.com...
> > > >> > Val
> > > >> >
> > > >> > Thanks for your reply.
> > > >> >
> > > >> > All my other stored procedures are updating fine, it's just the stored
> > > >> > procedures that have a table with a self join on a foreign key that get
> > > >> > the
> > > >> > error when trying to update.
> > > >> >
> > > >> > Thanks
> > > >> >
> > > >> > Warren
> > > >> >
> > > >> >
> > > >> > "Val Mazur" wrote:
> > > >> >
> > > >> >> Hi,
> > > >> >>
> > > >> >> Could be something as described in a next KB
> > > >> >>
> > > >> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;193515&Product=ado
> > > >> >>
> > > >> >> --
> > > >> >> Val Mazur
> > > >> >> Microsoft MVP
> > > >> >>
> > > >> >>
> > > >> >> "Warren" <Warren@discussions.microsoft.com> wrote in message
> > > >> >> news:3E2CBBE7-757C-4037-BD6A-5DF2C8B26C2F@microsoft.com...
> > > >> >> > I'm getting the following error when trying to perform an
> > > >> >> > UpdateBatch
> > > >> >> > on
> > > >> >> > on a
> > > >> >> > disconnected, client-side ADO recordset:
> > > >> >> >
> > > >> >> > -2147217864
> > > >> >> > Row cannot be located for updating. Some values may have been
> > > >> >> > changed
> > > >> >> > since
> > > >> >> > it was last read.
> > > >> >> >
> > > >> >> > ADO 2.8
> > > >> >> > MSDE Database
> > > >> >> > sqloledb provider
> > > >> >> >
> > > >> >> > The problem below doesn't occur when connected to an access
> > > >> >> > database.
> > > >> >> >
> > > >> >> > The Stored Procedures that are failing have a join to a table and a
> > > >> >> > copy
> > > >> >> > of
> > > >> >> > this table using the primary key in TableA and a foreign key in
> > > >> >> > TableA_1.
> > > >> >> > The
> > > >> >> > self join is the common thread.
> > > >> >> >
> > > >> >> > The BaseTable and BaseColumnName are the same for both tables and
> > > >> >> > their
> > > >> >> > fields even if the tables/fields are given an alias.
> > > >> >> >
> > > >> >> > I've compared the recordset properties from access and MSDE and only
> > > >> >> > 2
> > > >> >> > are
> > > >> >> > different - 'Unique Rows' (False for access and True for MSDE) and
> > > >> >> > 'Hidden
> > > >> >> > Values' (0 for access and 6 for MSDE.
> > > >> >> >
> > > >> >> > One of the provider properties caught my eye 'Multi-table Update' -
> > > >> >> > True
> > > >> >> > for
> > > >> >> > access and False for MSDE.
> > > >> >> >
> > > >> >> > Any help would be greatly appreciated.
> > > >> >>
> > > >> >>
> > > >> >>
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
- Next message: Wart: "Re: cancel ADO2.8 asynchron queries over WAN, takes long time"
- Previous message: shadow123: ""Unique Table" and "Resync Command" problems with self-ref query"
- Next in thread: Stephen Howe: "Re: 'Row cannot be located for updating...' error."
- Reply: Stephen Howe: "Re: 'Row cannot be located for updating...' error."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
