Lock record with disconnected recordsets - avoid lost updates

Lock record with disconnected recordsets - avoid lost updates

Post by Harald Mai » Thu, 23 Jan 2003 01:48:10



Hi Folks,

I really need your help. One thing first:
I am searching for a solution since month and I have read some hundred
articles about it but I did NOT find the solution. Lots of Postings in
Newsgroups handle it but I have NOT seen the solution!!!

So here we go:
I use Visual Basic 6 ServicePack 5
Access 97 (Jet with SP3?); although I specify Jet 4.0 to use I think
I've read that Jet 3.6 is used with Access 97?!
ADO 2.7

I use disconnected ADO recordsets in my Prog for a Multiuser
environment.
Settings for connection:
Acces*issions: no change
IsolationLevel: no change
CursorLocation: adUseClient
ShareDenyNone + ReadWrite

Settings for recordsets:
adUseClient
therefore: adOpenStatic
           adLockBatchOptimistic

I know that a recordset (or each field to be more precise) has an
"UnderlyingValue", "OriginalValue" and "Value". What I expect is, that
ADO (or the Provider) uses "OriginalValue" and "Value" to determine,
if there are any changes since fetch. What I ALSO EXPECT is that ADO
uses "UnderlyingValue" and "OriginalValue" to determine, if the
underlying table was changed. So I would expect that the provider
performs this when/before I do an update of a value!?!

But I get absolutely no warning and the users overwrite their values
without noticing.
I tried a lot of things like transactions but the values will be
overwritten anyway...

I've read a lot about all these things but I can't find my error and
I've seen nowhere the solution.

The solution btw should be quite simple because this should be handled
by the provider automatically...

I just want to a signal that says: the original values have been
changed; do you want to cancel?

If there IS a post providing a solution for EXACTLY this problem,
please tell me where!

BIG thnx
Harald

 
 
 

Lock record with disconnected recordsets - avoid lost updates

Post by Bill » Thu, 23 Jan 2003 05:13:22


Ah no. These Recordset "versions" are local to your client, and don't
reflect the state of the database.

The only way to prevent others from changing data while you're working on it
is to lock it using a Pessimistic lock.

WARNING: Pessimistic locks prevent updates to the entire rowset. That is, if
you execute SELECT * FROM AUTHORS, all of the rows in the AUTHORS table are
locked until you close your recordset.

No, you can't use client-side disconnected cursors if you want to use
pessimistic locking. I drone on and on about this in my books. There are
lots of examples there.

hth

____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


Quote:> Hi Folks,

> I really need your help. One thing first:
> I am searching for a solution since month and I have read some hundred
> articles about it but I did NOT find the solution. Lots of Postings in
> Newsgroups handle it but I have NOT seen the solution!!!

> So here we go:
> I use Visual Basic 6 ServicePack 5
> Access 97 (Jet with SP3?); although I specify Jet 4.0 to use I think
> I've read that Jet 3.6 is used with Access 97?!
> ADO 2.7

> I use disconnected ADO recordsets in my Prog for a Multiuser
> environment.
> Settings for connection:
> Acces*issions: no change
> IsolationLevel: no change
> CursorLocation: adUseClient
> ShareDenyNone + ReadWrite

> Settings for recordsets:
> adUseClient
> therefore: adOpenStatic
>    adLockBatchOptimistic

> I know that a recordset (or each field to be more precise) has an
> "UnderlyingValue", "OriginalValue" and "Value". What I expect is, that
> ADO (or the Provider) uses "OriginalValue" and "Value" to determine,
> if there are any changes since fetch. What I ALSO EXPECT is that ADO
> uses "UnderlyingValue" and "OriginalValue" to determine, if the
> underlying table was changed. So I would expect that the provider
> performs this when/before I do an update of a value!?!

> But I get absolutely no warning and the users overwrite their values
> without noticing.
> I tried a lot of things like transactions but the values will be
> overwritten anyway...

> I've read a lot about all these things but I can't find my error and
> I've seen nowhere the solution.

> The solution btw should be quite simple because this should be handled
> by the provider automatically...

> I just want to a signal that says: the original values have been
> changed; do you want to cancel?

> If there IS a post providing a solution for EXACTLY this problem,
> please tell me where!

> BIG thnx
> Harald


 
 
 

Lock record with disconnected recordsets - avoid lost updates

Post by Werner S?lke » Sun, 26 Jan 2003 01:49:34


William, when I use adLockBatchOptimistic and SELECT * FROM AUTHORS what's
locked then ??

Thanks...Werner



> Ah no. These Recordset "versions" are local to your client, and don't
> reflect the state of the database.

> The only way to prevent others from changing data while you're working on
it
> is to lock it using a Pessimistic lock.

> WARNING: Pessimistic locks prevent updates to the entire rowset. That is,
if
> you execute SELECT * FROM AUTHORS, all of the rows in the AUTHORS table
are
> locked until you close your recordset.

> No, you can't use client-side disconnected cursors if you want to use
> pessimistic locking. I drone on and on about this in my books. There are
> lots of examples there.

> hth

> ____________________________________
> Bill Vaughn
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> __________________________________



> > Hi Folks,

> > I really need your help. One thing first:
> > I am searching for a solution since month and I have read some hundred
> > articles about it but I did NOT find the solution. Lots of Postings in
> > Newsgroups handle it but I have NOT seen the solution!!!

> > So here we go:
> > I use Visual Basic 6 ServicePack 5
> > Access 97 (Jet with SP3?); although I specify Jet 4.0 to use I think
> > I've read that Jet 3.6 is used with Access 97?!
> > ADO 2.7

> > I use disconnected ADO recordsets in my Prog for a Multiuser
> > environment.
> > Settings for connection:
> > Acces*issions: no change
> > IsolationLevel: no change
> > CursorLocation: adUseClient
> > ShareDenyNone + ReadWrite

> > Settings for recordsets:
> > adUseClient
> > therefore: adOpenStatic
> >    adLockBatchOptimistic

> > I know that a recordset (or each field to be more precise) has an
> > "UnderlyingValue", "OriginalValue" and "Value". What I expect is, that
> > ADO (or the Provider) uses "OriginalValue" and "Value" to determine,
> > if there are any changes since fetch. What I ALSO EXPECT is that ADO
> > uses "UnderlyingValue" and "OriginalValue" to determine, if the
> > underlying table was changed. So I would expect that the provider
> > performs this when/before I do an update of a value!?!

> > But I get absolutely no warning and the users overwrite their values
> > without noticing.
> > I tried a lot of things like transactions but the values will be
> > overwritten anyway...

> > I've read a lot about all these things but I can't find my error and
> > I've seen nowhere the solution.

> > The solution btw should be quite simple because this should be handled
> > by the provider automatically...

> > I just want to a signal that says: the original values have been
> > changed; do you want to cancel?

> > If there IS a post providing a solution for EXACTLY this problem,
> > please tell me where!

> > BIG thnx
> > Harald

 
 
 

Lock record with disconnected recordsets - avoid lost updates

Post by Bill » Sun, 26 Jan 2003 05:52:35


Well, nothing is locked at all (after population is complete). That is, once
ADO finishes pulling all of the rows out of the authors table and copying
them to your client (a bad idea), the locks are released--unless you use a
Pessimistic lock. Optimistic (or BatchOptimisitic) cursors don't hold locks
on the rows post population.

--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


> William, when I use adLockBatchOptimistic and SELECT * FROM AUTHORS what's
> locked then ??

> Thanks...Werner



> > Ah no. These Recordset "versions" are local to your client, and don't
> > reflect the state of the database.

> > The only way to prevent others from changing data while you're working
on
> it
> > is to lock it using a Pessimistic lock.

> > WARNING: Pessimistic locks prevent updates to the entire rowset. That
is,
> if
> > you execute SELECT * FROM AUTHORS, all of the rows in the AUTHORS table
> are
> > locked until you close your recordset.

> > No, you can't use client-side disconnected cursors if you want to use
> > pessimistic locking. I drone on and on about this in my books. There are
> > lots of examples there.

> > hth

> > ____________________________________
> > Bill Vaughn
> > www.betav.com
> > Please reply only to the newsgroup so that others can benefit.
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > __________________________________



> > > Hi Folks,

> > > I really need your help. One thing first:
> > > I am searching for a solution since month and I have read some hundred
> > > articles about it but I did NOT find the solution. Lots of Postings in
> > > Newsgroups handle it but I have NOT seen the solution!!!

> > > So here we go:
> > > I use Visual Basic 6 ServicePack 5
> > > Access 97 (Jet with SP3?); although I specify Jet 4.0 to use I think
> > > I've read that Jet 3.6 is used with Access 97?!
> > > ADO 2.7

> > > I use disconnected ADO recordsets in my Prog for a Multiuser
> > > environment.
> > > Settings for connection:
> > > Acces*issions: no change
> > > IsolationLevel: no change
> > > CursorLocation: adUseClient
> > > ShareDenyNone + ReadWrite

> > > Settings for recordsets:
> > > adUseClient
> > > therefore: adOpenStatic
> > >    adLockBatchOptimistic

> > > I know that a recordset (or each field to be more precise) has an
> > > "UnderlyingValue", "OriginalValue" and "Value". What I expect is, that
> > > ADO (or the Provider) uses "OriginalValue" and "Value" to determine,
> > > if there are any changes since fetch. What I ALSO EXPECT is that ADO
> > > uses "UnderlyingValue" and "OriginalValue" to determine, if the
> > > underlying table was changed. So I would expect that the provider
> > > performs this when/before I do an update of a value!?!

> > > But I get absolutely no warning and the users overwrite their values
> > > without noticing.
> > > I tried a lot of things like transactions but the values will be
> > > overwritten anyway...

> > > I've read a lot about all these things but I can't find my error and
> > > I've seen nowhere the solution.

> > > The solution btw should be quite simple because this should be handled
> > > by the provider automatically...

> > > I just want to a signal that says: the original values have been
> > > changed; do you want to cancel?

> > > If there IS a post providing a solution for EXACTLY this problem,
> > > please tell me where!

> > > BIG thnx
> > > Harald

 
 
 

1. Lost updated record after Update!

Hello All,

I have a problem when using the Addnew function with RDO. After issuing
the Update method, the record updated successfully to the MS SQL server.
However, I cannot find the updated record within the result set. I need
the auto-number of the SQL server displayed back to the user.

Please Help

Thank you!

Peter

Public Sub AddNewRequest()

Dim qy1 As New rdoQuery
Dim rs As rdoResultset

ErrMsg = ""
OK = False

With qy1
    .Name = "Query"
    .SQL = "Select * from request Order By requestID"
    '.RowsetSize = 1
    Set .ActiveConnection = cn
    Set rs = .OpenResultset(rdOpenDynamic, rdConcurValues)
End With

With rs

            .AddNew
            !UserID = Trim(UserID)
            !Description = Trim(Description)
            !Purpose = Trim(Purpose)
            !Location = Trim(Location)
            !ExpectedDate = ExpectedDate
            .Update

            OK = True

            RequestID = !RequestID

End With

Set qy1 = Nothing
Set rs = Nothing

End Sub

2. help on ORA-02085 - NT

3. Value / row level locking with disconnected recordsets

4. *Oracle DBA Needed in Sumter, South Carolina- Great Opportunity

5. How to avoid multiple record locking?

6. msde on Win98

7. Table remains locked after disconnecting recordset

8. DATABASE EXAMPLES?

9. Value / row level locking with disconnected recordsets

10. How to avoid multiple record locking?

11. Can I lock a recordset record by record?

12. update disconnected recordset

13. Can't update an ADO Disconnected recordset