Multiuser app

[dlpastel]

I am trying to prevent one user from accessing a ADO record on a sql server database if another user is already in that work order. I would like to tell the second user that this record is now being opened in read only mode.

I am opening the record as adlockpessimistic
Then I change a field value manually ie rs.EditDate= date
This changes the Editmode property to 1
How do I use this property to make it so that the second user opens the recordset readonly? Or is there another way to do this?

Thanks,
Dan

 

[NoCoolHandle]

Ok, you are going to have a few issues here.

1. If you open lockpessimistic (with the correct cursor location and recordtype) the other user will never be able to scroll to the record (unless you open the record with nolock)

2. If pessimistic locking is in effect, you will need to experience the block to know it is locked.. (30 to 60 second time out - unresponsive application)

and there are more, I remember having all sorts of issues wiht cursor location and type with this one.

What you might want to do is open the reocrds with an optimizer hint that specifies an UPdate lock, however this will tend to make all records in someone elses records (Thtat match the query of the first) read only..

Another alternative is to use ado to find out if the value has been changed before allowing another update to take place. I try to code all my apps so that the update isn't done via the recordset, it happens via a stored proc, this then requires you add a timestamp col to your tables so you can tell if the record has been opened.

Of course use optimistic and you won't block the record, you will just get the error when your second user trys the update. Then coding becomes similar to doing every update explicitly via a stored proc. Find that there is an error and then tell your user it has been updated with new values and then show them which values are new.


A great situation where you need to weigh up the evils and pick one. My money is to use explicit updates, inserts and deletes via stored procs and not to open a recordset as anything other than readonly.

Hope some or all of this makes sence.

Rob

 

[dlpastel]

Thanks,
I may use a timestamp or just change a field from locked to unlocked so I can warn the user.

 

[NoCoolHandle]

That would be my advice.

You will also find using nothing but readonly/forwardonly recordsets becomes not only faster but as funcional as the more expensive updateable types.

THe ticket is just check in the update to see if the timestamp has changed and then the tough task is how do you want to go about resolving the differences. More often than not I will use this as an option to only rollback the new update if the collision is in the same column of the row being updated, but to bring back the entire row after the update to show the user what new changes have been made to the row with thier change..

Lots of options...

Have fun

Rob