VB Forum / Database Access / December 2005

Paul Clement wrote:

I have tried keyset, but the result is the same. Here is the sample
source:

SQL part:

create table periods (
 id   bigint not null,
 ts   timestamp,
 code varchar(50),
 name varchar(100)
);

alter table periods add constraint PK_PERIOD primary key (id);

insert into periods(id, code, name) values(1, '111', '222');

VB part:

Dim conn As New ADODB.Connection
conn.CursorLocation = adUseClient   ' i have also tried server
conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=ACC;Data Source=ENDY"

Dim rs1 As New ADODB.Recordset, rs2 As New ADODB.Recordset
rs1.Open "select * from periods", conn, _
   adOpenStatic, adLockBatchOptimistic    ' i have also tried keyset
rs2.Open "select * from periods", conn, _
   adOpenStatic, adLockBatchOptimistic

rs1.Fields("code") = "abc"   ' old: 111  new: abc
rs2.Fields("name") = "def"   ' old: 222  new: def

rs1.UpdateBatch     ' code: abc  name: 222
rs2.UpdateBatch     ' this should fail, but it succeeded
                   ' code: abc  name: def

If timestamp doesn't work, what do you suggest I do to provide
optimistic locking?
]

-------------------------------------------------

I am a bit confused. What did you expect to happen?

The behavior you show is exactly what I would expect. Calling 'UpdateBatch'
forces ADO to do an Update on all pending changes and then sends the changes
on to be reflected in store. There is no conflict.

It should be noted that no RDBMS supports 'concurrency'. They will go out of
their way to insure 'data integrity', but to manage concurrency you have to
implement a 'gatekeeper' of some kind, either within a SP, specific locks,
triggers, or outside code.

-ralph