Discuss "Lock page/recordset?" in the ASP Development forum.

Rebelle · #1 August 12th, 2008, 04:16 PM

Hi All,

I currently an Edit page (open form) then and update page (submitted on the edit page to run sql update statement). On my Edit page, I have the following code:

Code:

sSql = "Select field1, field2, field3 from vwSQL"
sSql = sSql & " Where 1=1"

if strType <> "" then
  sSql = sSql & " And Type like '" & strType & "'"
end if

sSql = sSql & " Order By " & sOrderBy & " " & sOrderByDir

Set rs = Server.CreateObject ("ADODB.Recordset")
rs.Open sSql, conn,3,1

if rs.eof then

The issue is, I have multiple users that can use the Edit page, therefore, it saves only the results from the user that last hits the save/submit button. How can I lock it where if someone already has the recordset (batch updating) pulled up, the next user that tries will not be able to? I've read up on the subject but wasn't successul with this so far...

.... I've tried, 3,4 but I don't get any errors but I also don't get any records.

mehere · #2 August 12th, 2008, 04:26 PM

try this:

Code:

rs.Open sSql, conn,3,2

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

Wolffy · #3 August 12th, 2008, 05:07 PM

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.

Rebelle · #4 August 13th, 2008, 02:32 PM

Hi Mehere,

I tried 3,2 ...I don't get any errors....but I don't get any records...it only shows my table header info and no records....I have a counter at the top of the screen that usually shows the number of records like (Records 1 to 8) when I have 8 records...and so on. when i tried 3,2 it shows (Records 1 to -1)...let me know if you need to see this code.

Hi Wolffy,
I'm using SQL2000.
I currently have something like below on the Update.asp page...because I am returning/editing multiple records from a search screen....looping thru all the results.

Code:

strItemID = Request("ItemIDRow" & nCounter)
do while strItemID > 0

strRegID = Request.Form("Reg" & nCounter)
strField1 = Request.Form("Field1" & nCounter)
strField2 = Request.Form("Field2" & nCounter)


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

rs.open sSql, Conn, 3, 1

nCounter = nCounter + 1
strItemID = Request("ItemIDRow" & nCounter)
Loop

Thank you both for trying to help me with this....this is a thorn in my side.

Wolffy · #5 August 13th, 2008, 02:56 PM

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?

Rebelle · #6 August 13th, 2008, 03:14 PM

Hiya Wolffy,

hmmm....yes, my changedDate is a datetime field and I also have a DateAdded field that is a datetime (this one gets the current datetime when a user is adding a new record). The ChangeDate gets updated when they are updating records from their search results.

I would think the entire transaction...but is there a way for me to stop it before it gets them to the Submit button (sql update statement)?

ok...steps are like this...
Search screen (allows multiple search criteria) when submitted goes to
SearchResults screen (has an edit button at the top) - this is where I would like to stop if they hit Edit and someone else is already editing...do not allow to go to next screen (edit screen).
If not in edit mode by anyone else...allow to go to Edit screen...(this screen has Submit/Save button)...
when Submit/Save is clicked it submits Finish page (this page has the sql update statement)

...crud...not sure if the above will work...because if i'm a different user and get only some of the records but not all the records already being edited by someone else...not sure what to do in this case..?? grrr..

Wolffy · #7 August 13th, 2008, 03:36 PM

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.

Rebelle · #8 August 13th, 2008, 03:49 PM

Okies....I need to let this sink in a bit and relook at things how I have them. I do have another scenario that's a little different, it will always pull the same recordset.....like tblTools....I just thought using the cursortype and locktype that would be the trick...I haven't tried this on the second scenario I have but I will try it.

And rethink the other first scenario and post back later in the week. Thank you both again!

Rebelle · #9 October 15th, 2008, 03:15 PM

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

Wolffy · #10 October 16th, 2008, 09:53 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to retrieve unknown recordset field names in table?	BLaaaaaaaaaarche	ASP Development	2	August 10th, 2008 08:33 AM
Help with asp display / recordset (troubleshoot)	Rebelle	ASP Development	27	June 30th, 2008 11:19 AM
go page to page questionaire app	peebman2000	.Net Development	3	April 26th, 2008 04:42 PM

Discuss "Lock page/recordset?" in the ASP Development forum.

ASP Development - Learn coding practices and tips to get the best out of your Active Server Pages (ASP). The Classic ASP forum is for ASP/VBScript and ASP/JScript applications.