ADODB Record Lock 1

[aliinal]

Hi,

I get some records to a recordset and do some changes.
I want my connection or recordset or whatelse lock records that i've retrieved until i update them.
During editing no other user will be able to change values. Pessimistic option is not working (i don't know why).
Using SQL Server and ADODB

Hope i've explained well.
Thanks
@li

 

[zemp]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/pg_ado_cursors_5.asp

zemp

 

[ArtieChoke]

What is it not doing? Can others update those records while you have the lock, etc.?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[aliinal]

First of all thanks for your replies.

1. Zemp, that url is broken
2. ArtieChoke: Yes, others can still get and modify data during lock time (so it's not a real lock)

I'm waiting for your further replies
Thanks
@li

 

[ArtieChoke]

Please post your connection and recordset code so we can see what you're doing. I've used it before and it works fine. Are you using a server or client-side cursor? Note - other users can still see the data, but they shouldn't be able to modify it.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[zemp]

Sorry, It worked last month. Try these

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcontypesoflocks.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconwhatisalock.asp

zemp

 

[aliinal]

Hi all,

ArtieChoke, the code that i've told you about is below.
First look at code than read below for description.

Dim rs As ADODB.Recordset
Dim conn As ADODB.Connection

Private Sub Command1_Click()
Set conn = New ADODB.Connection
conn.ConnectionString = "uid=adv_server;pwd=123456;driver={SQL Server}; server=ALI_COMPAQ;database=adk;"
conn.Open
Set rs = New ADODB.Recordset
rs.LockType = adLockPessimistic
rs.CursorType = adOpenDynamic
rs.Open "SELECT * FROM ANKETLER", conn

Do While Not rs.EOF
rs("AKTIF") = 13
rs.MoveNext
Loop

End Sub

Private Sub Command2_Click()
rs.Close
Set rs = Nothing

conn.Close
Set conn = Nothing
End Sub

Private Sub Command3_Click()
Dim rs2 As ADODB.Recordset
Dim conn2 As ADODB.Connection

Set conn2 = New ADODB.Connection
conn2.ConnectionString = "uid=adv_server;pwd=123456;driver={SQL Server};server=ALI_COMPAQ;database=adk;"
conn2.Open

Set rs2 = New ADODB.Recordset
rs2.LockType = adLockPessimistic
rs2.CursorType = adOpenDynamic
rs2.Open "SELECT * FROM ANKETLER", conn2
rs2("OLUSTURAN") = 13
rs2.MoveLast
rs2.Close
Set rs2 = Nothing

conn2.Close
Set conn2 = Nothing
End Sub


In above code, assume that command buttons click order is Command1-Command3-Command2 so that i open a connection and do some changes in command1_click, than get same recordset again in command2_click and do some changes too. Finally in command3_click i close the collection opened in command1_click.
As a result both of the changes are done without any limitation.
What i want to do is restrict the code in command2_click.

Your urgent helps are appreciated.

Thanks again
@li

 

[ArtieChoke]

Aha- I see the problem. As soon as you do the movenext, ADO performs an update on the record and releases the lock, since you've moved to the next record. The lock is by record only, not table. It's possible the database will upgrade it to a table lock if you're doing a lot of changes, but it should start off with a single record lock. You can see this in enterprise manager under the current activity section. Pause your program before the movenext and check the lock. Then execute the movenext and refresh the activity section and you should see the lock disappear.

Can you explain more about what you're trying to accomplish? Is this code just a test of the locking mechanism? Or do you really want two processes in the same program trying to update the table simultaneously?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[aliinal]

ArtieChoke you're great!
Thanks for your explanation. And you're correct record is in lock while it's open.

Thanks again.
@li

 

[ArtieChoke]

Glad I could help you "see the light"!

"I think we're all Bozos on this bus!" - Firesign Theatre