Mike Broderick
asked on
Access, pessimestic locking, OLEDB, VB.net
How do you do pessimestic locking using OLEDB objects to access a Jet database (Access 2002). I must be dense, cant find documentation on it.
Thats because you're spelling "pessimistic" incorrectly.
ok, assuming u are using ADO here
u have defined your ado connection object already
'adoConnection is your ADODB.Connection object
'adoRecSet is your ADODB.Recordset object
adoRecSet.ActiveConnection = adoConnection
adoRecSet.CursorType = adOpenKeyset
adoRecSet.LockType = adLockPessimistic
adoRecSet.CursorLocation = adUseClient
adoRecSet.Open "SELECT * FROM Num"
u have defined your ado connection object already
'adoConnection is your ADODB.Connection object
'adoRecSet is your ADODB.Recordset object
adoRecSet.ActiveConnection
adoRecSet.CursorType = adOpenKeyset
adoRecSet.LockType = adLockPessimistic
adoRecSet.CursorLocation = adUseClient
adoRecSet.Open "SELECT * FROM Num"
urm, bugger, u said vb.net right?
i gues u need ado.net ?
does my code give u a headstart?
i gues u need ado.net ?
does my code give u a headstart?
ASKER
I'm not sure if it is OLEDB or ADO. Here are my declarations:
Dim jCon1 As New OleDbConnection
Dim jSelCmd1 As New OleDbCommand
Dim jAdpt1 As New OleDbDataAdapter
Dim jParm1 As OleDbParameter
Dim jTx1 As OleDbTransaction
Dim jRdrCmd1 As New OleDbCommand
Dim jDataRdr As OleDbDataReader
Dim jCon1 As New OleDbConnection
Dim jSelCmd1 As New OleDbCommand
Dim jAdpt1 As New OleDbDataAdapter
Dim jParm1 As OleDbParameter
Dim jTx1 As OleDbTransaction
Dim jRdrCmd1 As New OleDbCommand
Dim jDataRdr As OleDbDataReader
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Regarding the SQL statement
SELECT * FROM [Order Details] HOLDLOCK WHERE OrderID = 10503
Do you need a cursor and a FETCH statement? I tried the holdlock option with a transaction and it didnt work. I noticed the cursor/fetch in the SQL manual.
I'm aware of the drawbacks with pess locking. This routine is a tight one. It reads, increments, and updates all in one procedure.
SELECT * FROM [Order Details] HOLDLOCK WHERE OrderID = 10503
Do you need a cursor and a FETCH statement? I tried the holdlock option with a transaction and it didnt work. I noticed the cursor/fetch in the SQL manual.
I'm aware of the drawbacks with pess locking. This routine is a tight one. It reads, increments, and updates all in one procedure.
ASKER
I tried HOLDLOCK's cousin XLOCK and it did what I wanted. Thanks for pointing me in the right direction.
Cool stuff, excellent
Best of luck then with the rest of your project especially the use of pessimistic locking
Funny how that is okay with VB/ADO/Access but not in favour with .Net
Best of luck then with the rest of your project especially the use of pessimistic locking
Funny how that is okay with VB/ADO/Access but not in favour with .Net