ADODB Recorsets: Locktypes and Cursour Tyes

Scruff

Does anyone know what the best Lock and Cursour types to use when creating an ADOB recorset that is going to access an MS SQL db where you want other threads\sercives to be able to access the tables in the database and update rows concurrently?

eg.
new row entered into db table.
2 differnet services access the same row in the db table at the same time but each wants to update a different field.

i've looked at
devguru and think that what i'm looking for is CusrourType adOpenDynamic and LockType adLockOptimistic.

there might still be contention issues if they both try and update at the same time though.

any suggestions on the above or on contention rules?

Talliesin

The pros and cons of each are quite detailed, and I don't have time to go into them now.
Short answer: it depends! RTFM!
Long answer later if I have time.

amen

it all depends on what you want do but doing what u suggest with multiple clients will probally be ok.

You should use MS SQL stored procedures(sps) for updating/inserting/reading into your tables

you may need to change the locking methods within these sps to optimise your code. for instance reading a row from a table can lock the entire table (or page) in sql for the duration of the read depding on what u are doing.

you should have a look at locking methods

bonkey

I usually open forward-scrolling-only, read-only recordsets.

I also perform Inserts, updates and deletes via stored procedures (or occasionally in-line SQL), and never use the ADODB methods such as .addnew, .delete and .update. Horrid things - work of Satan, they are.

This approach ensures that I have no locking problems. Sure, it may not be the simplest approach in terms of the number of lines of code you have to write, but its not actually that much more work when you get used to it.

jc