parkes
Oct 11th, 2000, 02:36 AM
I'm using VB6 and SQL7 with ADO code to connect to the database. What is the best way to check if a record is already in use. So that two people cannot amend (open) the same record is any given table?
666539
Oct 11th, 2000, 10:05 AM
I don't know of a way to see if a record in is use (there may be one, but I don't know of it and I'll track this thread to see if anyone has sugestions) but I'm not sure that would be of much help to you anyway. If 2 people loaded a recordset at the same time and person 2 made a change to record 10 while person 1 was working on record 20, if person 2 has saved record 10 for example then moved on to another record, then person 1 comes on to record 10 there wouldn't be anyone working on the record 10 but the values will have been changed.
The amount of time a person would actually be using a record would be fractions of a second if you are using optimistic locking. If you really don't want people to be able to use records at the same time you could use pessemistic locking, but that is a bad idea. You shouldn't use that locking type unless you have a very good reason for doing so. With pessimistic locking, if a person is editing a record and goes on a coffee break, that record will not be accesable by anyone else until it is released by that user. I've seen applications that do this and they were a mess.
I think what you want to look into is not whether someone is using the record, but whether the values have been chagned.
SQL Server will return a specific error number if you try to save a record which has has changes made to it (I think only if you change a field that has been changed. If you change fields that haven't been changed an error will not be returned.) You could build an error handler into your app and if that error number is returned you could give the user of loading the new data, or you could do a ado.recordset.resync adcurrent (it is resync or requery) which will refresh only the current record.
Ado does have properties to show the current value in the database.
ado.recordset.field.originalvalue (value when you retreived it)
ado.recordset.field.underlyingvalue (current value for that record in the database)
You could use the underlyingvalue property to see if it has changed if you don't want to deal with an error handler but that would add extra overhead to each edit you did.