Thread: Lock page/recordset?

try this:

Code:

rs.Open sSql, conn,3,2

the first number is the cursortype and the second is the locktype

What DBMS are you using? SQL Server 2005 would be a great answer.

[Rethink]
OK, here's kinda the problem with this. You've got 3 users that may all to against a particular record in you database doing something like
SELECT record
Display record data in FORM
User makes chagnes
User hits SUBMIT button
SQL UPDATES record with changes

Right? So what happens if user A does only the first 2 steps and goes home for the weekend -- leaving your record (or heavens! the entire Table) locked? I've noodled over this a number of times, but in any environment where humans are involved, there is this problem with them never completing the transaction.

Rather than trying to do pessimistic locking on the record, I'd go with an opportunistic approach and assume that record won't change between the SELECT and the UDPATE. Right! Sooner or later, you'll run into your problem of 3 users whacking the same record and the same time. So what to do? Here are two approaches (the first of which is what Visual Studio generates when it creates an UPDATE query, BTW). Assume your table has 3 fields, (ID, Name, Amount). So, let's select the record and plop it into a form for a user to change the Amount (ID is the PK, you do have PK's, yes?)

Code:

Select ID, Name, Amount 
From PaymentTable
Where ID = 1234

Great -- User A then changes the value of Amount (let's assume that ID, and Name are read-only on the form for now). Usually, the UPDATE gets coded this way

Code:

Update PaymentTable
Set Amount = &Amount
Where ID = 1234

(You do use parameters, don't you?) But, there's the problem, what if User B also is updated user 1234 at the same time. Rather, if you code the UPDATE statement this way

Code:

Update PaymentTable
Set Amount = &Amount
Where ID = 1234
And Amount = &originalAmount 

Now you prevent the second UPDATE from overwritting the first. (The statement won't fail, it will just return 0 records updated). At this point, you can either tell USER A that she is SOL, or re-select the data and try again. Note that the update query can very long if you have lots of fields.

The other way to detect conflicts is to use a HASH or TIMESTAMP field on the record. In this case, SELECT the TIMESTAMP field along with the data you want displayed. When you go to UPDATE the record, include Set AUDIT_TS = gettime().....And AUDIT_TS = &original_TIMESTAMP in your query. If the timestamp doesn't match, you now the record has been updated from somewhere else. This works well with table that have many fields.

OK, I see you have a ChangedDate in your UPDATE query -- I assume it's a datetime. So, if you could put the original changed date into a hidden field in your form, you could use this to ensure that the record has not been updated by another user. Just add another condition to your query AND ChangeDate = originalChangedDate. This will prevent the record from being updated should it have changed from another process.

Now the question remaining is, do you want to fail only that UPDATE or the entire Transaction?

Yeah, that really doesn't work in a disconnected environment like ASP. In reality, you connect to the database, run your query, and disconnect for each 4 steps you have there. You also have the problem of USER B hitting the edit button and going away for the weekend, or on vacation, and everybody else is locked out until it times out.

So, you really can't check when the update button is pressed, only when you go to do the UPDATES. You may want to rethink you EDIT/UPDATE logic from the mass-update you describe to a record-at-a-time update.

That said, can you set the DateChanged field when the record is created (same as DateCreated) or would that break things? Then, just use this as your 'record changed' indicator. If you want to fail the whole update, just wrapper your loop in a BEGIN TRANSACTION and either COMMIT or ROLLBACK.

Hi Wolffy,

Ok...working on not doing the multiple edit recordset....just doing a one record at a time but I still can't seem to figure out how to get it to lock.

I still have 2 pages, an edit page and an update page.

edit page looks like:

Code:

Set rs = Server.CreateObject ("ADODB.Recordset")
 
'build SQL
sSql = "SELECT * FROM dbo.tblTEST"
sSql = sSql & " WHERE TestID='" & sTestID & "'" 

rs.Open sSql, conn,3,3
If rs.EOF then

my update:

Code:

Set rs = Server.CreateObject("ADODB.Recordset")
sSql = "Update tblTEST set RegID = " & strRegID & ", Field1 = " & strField1 & ", Field2 = " & strField2 & ", ChangedDate = (getdate())"
sSql = sSql & " Where ItemID = " & strItemID & " "

rs.open sSql, Conn, 3, 3

Maybe you've already stated in above posts but can we start over since this is just one record instead of multiple. Thanks!

oh another question....does it matter that I don't have this restricted...like I'm not having users login? at least for right now.

just more info...have a search screen that returns recordset like in link here...and edit link like so:
What mine looks like

On your select add sumpin' like original_ChangeDate = ChangeDate and then on your update query add to the WHERE clause AND ChangeDate = original_ChangeDate. Then, if you get 0 records updated, you know if record has changed from elsewhere.

I would do this even if you managed to lock the record out as in you other post.