LOCK A TABLE. ONLY ONE USER AT A TIME 3

[jadams0173]

I'm using VB6 and A2K with ADO. I'm trying to lock a table or a record in the table until the current user is done with it. I'm attempting to make an autonumber field. The table in question only has one record in it.

Would I be better off adding a locked check box and checking it that way?

I've read some FAQ's but the one i saw used DAO.

Here is what I've tried.

Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset

rs.Open "Select fixtureid from tblFixtureID", conn, adOpenKeyset, adLockPessimistic, adCmdText
'rs.EditMode
rs.Fields("fixtureid") = 2000


rs2.Open "Select fixtureid from tblFixtureID", conn, adOpenKeyset, adLockPessimistic, adCmdText
rs2.Fields("fixtureid") = 5000


Set rs = Nothing
Set rs2 = Nothing
conn.Close
Set conn = Nothing

Here is my connection string.

  With conn
  .CursorLocation = adUseClient
  
   .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source=C:\MYDB.mdb;" & _
                      "Jet OLEDB:Database"

  .Open
  End With

I tried to have patience but it took to long! -DW

 

[jadams0173]

A well deserved star. I'm trying to somewhat grasp the combinations described in SBerthold's last post. I still can't get the desired results, so I'm going to keep trying. Sorry for being so thick headed on this one...

A star was coming, just hadn't gotten to it yet.



I tried to have patience but it took to long! -DW

 

[jadams0173]

I'm still not getting what I had hoped to. So I've add something like Andy has above, which I hope will not let duplicate values in. Mine is slightly different as I don't record a name, I just set a flag.

Thanks to BobRodes and SBerthold for all there help and information.

I have yet been able to get a pessimestic lock to forbid me from being able to update a record. But with info here I hope to get my curosor/locks straightened out and better understand what's going on. Thanks again!

I tried to have patience but it took to long! -DW

 

[Andrzejek]

I am glad I can help you with my way of locking.

If you want to get user's login name, try this:

Form's code:

MsgBox UCase(WNetGetUser)


Module's code:

Option Explicit

'Function to get the UserName from NT
Private Declare Function w32_WNetGetUser Lib "mpr.dll" Alias "WNetGetUserA" ( _
  ByVal lpszLocalName As String, _
  ByVal lpszUserName As String, _
  lpcchBuffer As Long) As Long
  
  Private Const NO_ERROR = 0

Public Function WNetGetUser() As String
' **********
' Purpose:     Retrieve the network user name
' Paramters:   None
' Returns:     The indicated name
' Notes:
'  A zero-length string is returned if the function fails
' **********

Dim lpUserName As String
Dim lpnLength As Long
Dim lResult As Long

lpnLength = 256
lpUserName = Space(lpnLength)

lResult = w32_WNetGetUser(vbNullString, lpUserName, lpnLength)

If lResult = NO_ERROR Then
    WNetGetUser = CStringToVBString(lpUserName)
Else
    WNetGetUser = ""
End If

End Function

Public Function CStringToVBString(psCString As String) As String
' **********
' Purpose:     Convert a C string to a VB string
' Parameters:  (Input Only)
'  psCString - the C string to convert
' Returns:     The converted VB string
' Notes:
'  Returns everything to the left of the first Null character
' **********

Dim sReturn As String
Dim iNullCharPos As Integer

iNullCharPos = InStr(psCString, vbNullChar)

If iNullCharPos > 0 Then
   ' return everything left of the null
   sReturn = Left(psCString, iNullCharPos - 1)
Else
   ' no null, return the original string
   sReturn = psCString
End If

CStringToVBString = sReturn
   
End Function

I used to have just a flag, and then users were asking me: "Who has this record? I need it now and can not wait until next day." Now they don't ask me anymore - they see who is seatting on it



Have fun.

---- Andy

 

[jadams0173]

Thanks for the code Andy and thanks again for the help.

I tried to have patience but it took to long! -DW

 

[MichaelRed]

way late in reading through this. one issue with the original posted attempt may be that while the code opens two connections, there is only one 'user', so the db side is probably seeing the same user regardless of where the change originates. somewhat defeats the point of having the dual (multiple) connection? no number of connections would seem to prevent the user from editing the record multiple times?




MichaelRed

 

[BobRodes]

Are you sure it is sticking to adOpenStatic?

Well, you tell me. Here's my code:

Private Sub Form_Load()
Dim strCn As String
strCn = "Provider=sqloledb;Data Source=.;Initial Catalog=northwind;Integrated Security=SSPI;"
'strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb;User Id=admin;Password=;"
Set cn = New ADODB.Connection
cn.Open strCn
Set rs = New ADODB.Recordset
With rs
    .CursorLocation = adUseClient
    .Open "select * from customers", cn
    .Fields(1) = "Joes Futterkiste"
    .Update
End With
End Sub

That doesn't work. But, when I add the line

    .LockType = adLockPessimistic

it allows the update. It's hard for me to conclude from this that ADO will coerce both the locktype to batchoptimistic and the update method to the updatebatch method, let alone coercing the cursorlocation to server, all on the strength of setting the locktype to pessimistic. Are you saying that you know that that's what it in fact does?

 

[SBerthold]

>It's hard for me to conclude from this that ADO will coerce both the locktype to batchoptimistic and the update method to the updatebatch method

Yes, that is what it will do, as I (half) mentioned it, and the way I understand it to be.

>let alone coercing the cursorlocation to server
No, this is not happening

Think about it: You have tried to place a Pessimistic lock on a cursor (ignore for a moment the cursor location).

Why? What is the purpose? What does this mean?

Well, the cursor cannot be changed to Server side. That would be too drastic.

But, this is the next best thing, as you are saying to ADO:
"try to lock the record as soon as possible, once gone into the edit mode, until the record is updated, and then release it, because I want to make sure ..."
... of What?

So, an Optimistic locking scheme, with immediate update to the data source, is the next best thing.

 

[SBerthold]

(too fast)
I wanted to say:

"As I mentioned earlier"

>First, the CursorLocation has priority, then the LockType, and lastly the CursorType

The lock type has priority, so the pessimistic will change to Batch Optimistic and BatchUpdate

 

[SBerthold]

Just re-read the posts today.
It may be of interest to mention that there was back in early ADO days (somewhere around MDAC 1.5) a silly error made by the programmers with regard to client side cursors and pessimistic locks. I don't know how long you have been using ADO, but maybe you had experienced this at one time.

It was possible at one time to assign an client cursor a pessimistic lock, and it would stay pessimistic without defaulting to another lock - no matter how stupid something like that may sound, but it did happened, and of course with a client cursor a pessimistic cannot work.
I do not know what happened then when Update was then called - if it was just a local Update, or Batch Update.

Anyways, it was later corrected to default to a batch optimistic lock. Maybe it was then when the Update method defaulted to UpdateBatch, so that the error could somehow quietly slip by. I don't know with out having to install that version on a fresh system and test it.

It does make sense to me to have as a minimum(!), that the cursor defaults to UpdateBatch. However, because a pessimistic lock is really something explicitly and specially desired when requested, I would actually welcome an error from ADO instead.
But then again, if the programmer made the mistake to order a pessimistic lock on a client side cursor, and expect it to still work properly, when of course it cannot, then they could also make a mistake and use a plain optimistic client side lock, and expect the Update method to actually update the data source, which it wouldn't.
However, the latter would be assumed that this cursor lock and location is actually desired, but the former could not be even properly or logically assumed.

 

[BobRodes]

Well, here's some info about optimistic conflict resolution in a disconnected recordset (near the bottom of the page).

http://www.ehartwell.com/Exoware/Tech/ImplementingADOPlanDatabase.htm

Perhaps if you make a client side recordset, locktype is optimistic and has the features and capabilities described here. But I think the real question is the hierarchy of properties, which overrides the next. I'm thinking it's location overrides locktype overrides cursortype.

Bob

 

[jadams0173]

Thanks for the link BobRodes. I haven't forgotten about this and have spent several hours reading and trying to get more familiar and understand what happens with the different combinations of cursor locations, types and lock types.

I tried to have patience but it took to long! -DW

 

[BobRodes]

Perhaps something to be aware of is that ADO.Net uses disconnected recordsets by default, suggesting that they are the means of choice in ADO to access and manipulate data. I guess the idea is that flexibility in determining when and how you transfer data between the client and server is outweighed by the overhead cost of the connection required to do so.

Bob