client side cursor and row locking 2

[violetstarlight]

Bare with me because I am somewhat new to VB and ADO.

Is there a way to lock a record in SQL Server using a client side cursor?

I am trying to lock a record so that another user doesn't have access if someone else has it opened. I am currently using ADO with a server side cursor, with pessimistic locking and a forward only cursor type. This accomplishes what I'm trying to do BUT only by accident. I am getting a timeout error instead of a record locked error. HELP?

I have a connection and recordset variable declared at the module level because the function is in a class. The recordset results are stored in class variables.

sp = "GetSelectedThreadResponse '" & vData & "'"

Set m_cnResp = New ADODB.Connection

If m_cnResp.State = adStateClosed Then
' class method that i created to open
' the connection.
g_objADOConnection.OpenDBConnection m_cnResp
End If

m_cnResp.CursorLocation = adUseServer

Set m_rsResp = New ADODB.Recordset
m_rsResp.Open sp, m_cnResp, adOpenForwardOnly, adLockPessimistic

Let me know if any further details are needed.

Thank you so much!!!
Shaun

 

[dvannoy]

here are the types below

i would use client side cursor

rs.CursorLocation = adUseClient

rs.CursorType = adOpenDynamic
rs.CursorType = adOpenForwardOnly
rs.CursorType = adOpenKeyset
rs.CursorType = adOpenStatic

rs.LockType = adLockBatchOptimistic
rs.LockType = adLockOptimistic
rs.LockType = adLockPessimistic
rs.LockType = adLockReadOnly

 

[violetstarlight]

I am familiar with the different kinds of lock types and cursor types. But how would I implement a client side cursor to lock a record? Wouldn't a client side cursor create a disconnected recordset and therefore, be unable to handle locking the record for all users of an app? it would only lock it for that one client?

Perhaps that is where I am confused. Does a client side cursor ALWAYS create a disconnected recordset?

 

[dvannoy]

OK...you are right sorry..
The ADO client cursor engine does not support pessimistic locking. if you choose pessimistic locking with client side cursor it changes it to optimistic.

check out this link...it shows you how to do this.

http://search.microsoft.com/default...=36&i=37&i=38&i=39&i=40&siteid=us/itresources

 

[violetstarlight]

This definately helps! Thank you!!

one more question...is there a way to see who has the record locked?

 

[dvannoy]

I believe you can see that in Enterprise manager. but depending on how your letting your users login to sql ...you may only see sa several times as being logged in.

 

[SonOfEmidec1100]

If you run this query in Enterprise Manager you can get a lot of info about user connections

SELECT * FROM Master..SysProcesses
WHERE Master..SysProcesses.dbid = DB_ID('YouDBName')
AND Master..SysProcesses.HOSTNAME <> ''

 

[violetstarlight]

then my last question is....

is there a way to get the spid or other identifying info from the record that was initially locked to use they query that SonOfEmidec1100 presented?