|
 |
asp_web_howto thread: RE: recordset locking
Message #1 by "Drew, Ron" <RDrew@B...> on Wed, 12 Jun 2002 11:46:49 -0400
|
|
Maybe test the recordobject.state to be openned or closed. I do not
think you want to lock the recordset, just the record within it.
http://www.devguru.com/technologies/ado/quickref/record.html
-----Original Message-----
From: Pham, Khanh [mailto:Khanh.Pham@d...]
Sent: Wednesday, June 12, 2002 9:06 AM
To: ASP Web HowTo
Subject: [asp_web_howto] recordset locking
I'm trying to lock a recordset from Access 2000 to avoid concurrent
users from updating the same record. I opened up the same page on 2
browsers and submit both. I get no error message that the record was
locked. When I check the database the data was the last form submitted.
Seems like who ever submits last will overwrite the first. What I want
to do is if the same record is open by 2 users I want the second users
to get a message that the record is being updated by someone else.
Please help.
<%
Set Conn =3D server.CreateObject("ADODB.Connection")
strDatabase =3D server.MapPath("includes/peopleDb.mdb")
Conn.Open =3D "Provider=3DMicrosoft.Jet.OLEDB.4.0;Data Source=3D" &
strDatabase & ";" & _
"Persist Security
Info=3DFalse"
Set adoRs =3D server.CreateObject("ADODB.Recordset")
strSQL =3D "Select * from peopleTB where id =3D 2"
adoRs.Open strSQL,Conn,adOpenDynamic,adLockPessimistic
CheckForErrors (adoRs.ActiveConnection)
%>
<HTML>
<HEAD>
<META NAME=3D"GENERATOR" Content=3D"Microsoft Visual Studio 6.0">
<TITLE></TITLE> </HEAD> <BODY bgcolor=3D"lightyellow"> <form
method=3D"post">
id:
<input type=3D"text" name=3D"id" value=3D"<%=3DadoRs("ID")%>"><BR>
rank:<BR>
<input type=3D"text" name=3D"rank" value=3D"<%=3DadoRs("Rank")%>"><BR>
name:
<input type=3D"text" name=3D"name" value=3D"<%=3DadoRs("Name")%>"><BR>
<input
type=3D"submit" name=3D"submit" value=3D"submit">
</form>
<%
'CloseConnection
if Request.Form("submit") =3D "submit" then
'on error resume next
Set Conn =3D server.CreateObject("ADODB.Connection")
strDatabase =3D server.MapPath("includes/peopleDb.mdb")
Conn.Open =3D "Provider=3DMicrosoft.Jet.OLEDB.4.0;Data Source=3D" &
strDatabase & ";" & _
"Persist Security
Info=3DFalse"
strID =3D Request.Form("id")
strRank =3D Request.Form("rank")
strName =3D Request.Form("name")
Response.Write strDatabase
Set adoRs =3D server.CreateObject("ADODB.Recordset")
strSQL =3D "Select * from peopleTB where id =3D" & strID
adoRs.Open strSQL,Conn,adOpenDynamic,adLockPessimistic
adoRs.Fields("Rank") =3D strRank
adoRs.Fields("Name") =3D strName
adoRs.Update
CheckForErrors (adoRs.ActiveConnection)
'-----------------------------------------------------------------------
----
---------
Function CheckForErrors(objConn)
Dim objErorr
for each objError in objConn.Errors
If objConn.Errors.Count > 0 then
Response.Write "<table border=3D1>" & _
"<tr><td>Error
Property</td><td>Contents</td>" & _
"</tr><tr><td>Number</td><td>" &
objError.Number & _
=09
"</td></tr><tr><td>NativeError</td><td>" & _
objError.NativeError &
"</td></tr>"
& _
"<tr><td>SQLState</td><td>" &
objError.SQLState & _
=09
"</td></tr><tr><td>Source</td><td>"
& _
objError.Source & "</td></tr>" &
_
"<tr><td>Description</td><td>" &
_
objError.Description &
"</td></tr></table>"
CheckForErrors =3D true
else
CheckForErrors =3D False
end if
next
end Function
'-----------------------------------------------------------------------
----
---------
CloseConnection
end if
'-------close database connection and release the
resources--------------------------
sub CloseConnection()
if TypeName(adoRs) =3D "Recordset" then
if adoRs.state <> adStateClosed then
adoRs.close
set adoRs =3D nothing
=09
end if
end if
'If it's a connection and open, close it
if TypeName(Conn) =3D "Connection" then
if Conn.state <> adStateClosed then
Conn.close
set Conn =3D nothing
=09
end if
end if
end sub
%>
</BODY>
</HTML>
---
Improve your web design skills with these new books from Glasshaus.
Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=3Dnosim/theprogramm
e
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=3Dnosim/theprogramm
e
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=3Dnosim/theprogramm
e
r-20
Message #2 by "Pham, Khanh" <Khanh.Pham@d...> on Wed, 12 Jun 2002 11:37:27 -0400
|
|
Thanks. I will try it out.
-----Original Message-----
From: Waleed Fahmy [mailto:fahmywaleed@h...]
Sent: Wednesday, June 12, 2002 12:41 PM
To: ASP Web HowTo
Subject: [asp_web_howto] Re: recordset locking
Hi
What I do for these cases that I add a Locked field in the data base
that holds a boolean value. So whenever a user opens a record this field
is set to 1. and before opening any record you check for this value is not
1. And when the user submits his record this value resets to 0.
This method has a draw back. If the user closes the browser without
submit, the record stays locked. To solve this problem you can add a sub
in the session timeout event to unlock the record.
Waleed
> I'm trying to lock a recordset from Access 2000 to avoid concurrent users
from updating the same record. I opened up the same page on 2 browsers and
submit both. I get no error message that the record was locked. When I
check the database the data was the last form submitted. Seems like who
ever submits last will overwrite the first. What I want to do is if the
same record is open by 2 users I want the second users to get a message
that
the record is being updated by someone else. Please help.
<%
Set Conn = server.CreateObject("ADODB.Connection")
strDatabase = server.MapPath("includes/peopleDb.mdb")
Conn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase &
";" & _
"Persist Security
Info=False"
Set adoRs = server.CreateObject("ADODB.Recordset")
strSQL = "Select * from peopleTB where id = 2"
adoRs.Open strSQL,Conn,adOpenDynamic,adLockPessimistic
CheckForErrors (adoRs.ActiveConnection)
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY bgcolor="lightyellow">
<form method="post">
id:
<input type="text" name="id" value="<%=adoRs("ID")%>"><BR>
rank:<BR>
<input type="text" name="rank" value="<%=adoRs("Rank")%>"><BR>
name:
<input type="text" name="name" value="<%=adoRs("Name")%>"><BR>
<input type="submit" name="submit" value="submit">
</form>
<%
'CloseConnection
if Request.Form("submit") = "submit" then
'on error resume next
Set Conn = server.CreateObject("ADODB.Connection")
strDatabase = server.MapPath("includes/peopleDb.mdb")
Conn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase &
";" & _
"Persist Security
Info=False"
strID = Request.Form("id")
strRank = Request.Form("rank")
strName = Request.Form("name")
Response.Write strDatabase
Set adoRs = server.CreateObject("ADODB.Recordset")
strSQL = "Select * from peopleTB where id =" & strID
adoRs.Open strSQL,Conn,adOpenDynamic,adLockPessimistic
adoRs.Fields("Rank") = strRank
adoRs.Fields("Name") = strName
adoRs.Update
CheckForErrors (adoRs.ActiveConnection)
'--------------------------------------------------------------------------
-
---------
Function CheckForErrors(objConn)
Dim objErorr
for each objError in objConn.Errors
If objConn.Errors.Count > 0 then
Response.Write "<table border=1>" & _
"<tr><td>Error
Property</td><td>Contents</td>" & _
"</tr><tr><td>Number</td><td>" &
objError.Number & _
"</td></tr><tr><td>NativeError</td><td>" & _
objError.NativeError & "</td></tr>"
& _
"<tr><td>SQLState</td><td>" &
objError.SQLState & _
"</td></tr><tr><td>Source</td><td>"
& _
objError.Source & "</td></tr>" & _
"<tr><td>Description</td><td>" & _
objError.Description &
"</td></tr></table>"
CheckForErrors = true
else
CheckForErrors = False
end if
next
end Function
'--------------------------------------------------------------------------
-
---------
CloseConnection
end if
'-------close database connection and release the
resources--------------------------
sub CloseConnection()
if TypeName(adoRs) = "Recordset" then
if adoRs.state <> adStateClosed then
adoRs.close
set adoRs = nothing
end if
end if
'If it's a connection and open, close it
if TypeName(Conn) = "Connection" then
if Conn.state <> adStateClosed then
Conn.close
set Conn = nothing
end if
end if
end sub
%>
</BODY>
</HTML>
---
Improve your web design skills with these new books from Glasshaus.
Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
r-20
Message #3 by "Pham, Khanh" <Khanh.Pham@d...> on Wed, 12 Jun 2002 09:05:30 -0400
|
|
I'm trying to lock a recordset from Access 2000 to avoid concurrent users
from updating the same record. I opened up the same page on 2 browsers and
submit both. I get no error message that the record was locked. When I
check the database the data was the last form submitted. Seems like who
ever submits last will overwrite the first. What I want to do is if the
same record is open by 2 users I want the second users to get a message that
the record is being updated by someone else. Please help.
<%
Set Conn = server.CreateObject("ADODB.Connection")
strDatabase = server.MapPath("includes/peopleDb.mdb")
Conn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase &
";" & _
"Persist Security
Info=False"
Set adoRs = server.CreateObject("ADODB.Recordset")
strSQL = "Select * from peopleTB where id = 2"
adoRs.Open strSQL,Conn,adOpenDynamic,adLockPessimistic
CheckForErrors (adoRs.ActiveConnection)
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY bgcolor="lightyellow">
<form method="post">
id:
<input type="text" name="id" value="<%=adoRs("ID")%>"><BR>
rank:<BR>
<input type="text" name="rank" value="<%=adoRs("Rank")%>"><BR>
name:
<input type="text" name="name" value="<%=adoRs("Name")%>"><BR>
<input type="submit" name="submit" value="submit">
</form>
<%
'CloseConnection
if Request.Form("submit") = "submit" then
'on error resume next
Set Conn = server.CreateObject("ADODB.Connection")
strDatabase = server.MapPath("includes/peopleDb.mdb")
Conn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase &
";" & _
"Persist Security
Info=False"
strID = Request.Form("id")
strRank = Request.Form("rank")
strName = Request.Form("name")
Response.Write strDatabase
Set adoRs = server.CreateObject("ADODB.Recordset")
strSQL = "Select * from peopleTB where id =" & strID
adoRs.Open strSQL,Conn,adOpenDynamic,adLockPessimistic
adoRs.Fields("Rank") = strRank
adoRs.Fields("Name") = strName
adoRs.Update
CheckForErrors (adoRs.ActiveConnection)
'---------------------------------------------------------------------------
---------
Function CheckForErrors(objConn)
Dim objErorr
for each objError in objConn.Errors
If objConn.Errors.Count > 0 then
Response.Write "<table border=1>" & _
"<tr><td>Error
Property</td><td>Contents</td>" & _
"</tr><tr><td>Number</td><td>" &
objError.Number & _
"</td></tr><tr><td>NativeError</td><td>" & _
objError.NativeError & "</td></tr>"
& _
"<tr><td>SQLState</td><td>" &
objError.SQLState & _
"</td></tr><tr><td>Source</td><td>"
& _
objError.Source & "</td></tr>" & _
"<tr><td>Description</td><td>" & _
objError.Description &
"</td></tr></table>"
CheckForErrors = true
else
CheckForErrors = False
end if
next
end Function
'---------------------------------------------------------------------------
---------
CloseConnection
end if
'-------close database connection and release the
resources--------------------------
sub CloseConnection()
if TypeName(adoRs) = "Recordset" then
if adoRs.state <> adStateClosed then
adoRs.close
set adoRs = nothing
end if
end if
'If it's a connection and open, close it
if TypeName(Conn) = "Connection" then
if Conn.state <> adStateClosed then
Conn.close
set Conn = nothing
end if
end if
end sub
%>
</BODY>
</HTML>
Message #4 by "Waleed Fahmy" <fahmywaleed@h...> on Wed, 12 Jun 2002 16:40:30
|
|
Hi
What I do for these cases that I add a Locked field in the data base
that holds a boolean value. So whenever a user opens a record this field
is set to 1. and before opening any record you check for this value is not
1. And when the user submits his record this value resets to 0.
This method has a draw back. If the user closes the browser without
submit, the record stays locked. To solve this problem you can add a sub
in the session timeout event to unlock the record.
Waleed
> I'm trying to lock a recordset from Access 2000 to avoid concurrent users
from updating the same record. I opened up the same page on 2 browsers and
submit both. I get no error message that the record was locked. When I
check the database the data was the last form submitted. Seems like who
ever submits last will overwrite the first. What I want to do is if the
same record is open by 2 users I want the second users to get a message
that
the record is being updated by someone else. Please help.
<%
Set Conn = server.CreateObject("ADODB.Connection")
strDatabase = server.MapPath("includes/peopleDb.mdb")
Conn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase &
";" & _
"Persist Security
Info=False"
Set adoRs = server.CreateObject("ADODB.Recordset")
strSQL = "Select * from peopleTB where id = 2"
adoRs.Open strSQL,Conn,adOpenDynamic,adLockPessimistic
CheckForErrors (adoRs.ActiveConnection)
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY bgcolor="lightyellow">
<form method="post">
id:
<input type="text" name="id" value="<%=adoRs("ID")%>"><BR>
rank:<BR>
<input type="text" name="rank" value="<%=adoRs("Rank")%>"><BR>
name:
<input type="text" name="name" value="<%=adoRs("Name")%>"><BR>
<input type="submit" name="submit" value="submit">
</form>
<%
'CloseConnection
if Request.Form("submit") = "submit" then
'on error resume next
Set Conn = server.CreateObject("ADODB.Connection")
strDatabase = server.MapPath("includes/peopleDb.mdb")
Conn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase &
";" & _
"Persist Security
Info=False"
strID = Request.Form("id")
strRank = Request.Form("rank")
strName = Request.Form("name")
Response.Write strDatabase
Set adoRs = server.CreateObject("ADODB.Recordset")
strSQL = "Select * from peopleTB where id =" & strID
adoRs.Open strSQL,Conn,adOpenDynamic,adLockPessimistic
adoRs.Fields("Rank") = strRank
adoRs.Fields("Name") = strName
adoRs.Update
CheckForErrors (adoRs.ActiveConnection)
'--------------------------------------------------------------------------
-
---------
Function CheckForErrors(objConn)
Dim objErorr
for each objError in objConn.Errors
If objConn.Errors.Count > 0 then
Response.Write "<table border=1>" & _
"<tr><td>Error
Property</td><td>Contents</td>" & _
"</tr><tr><td>Number</td><td>" &
objError.Number & _
"</td></tr><tr><td>NativeError</td><td>" & _
objError.NativeError & "</td></tr>"
& _
"<tr><td>SQLState</td><td>" &
objError.SQLState & _
"</td></tr><tr><td>Source</td><td>"
& _
objError.Source & "</td></tr>" & _
"<tr><td>Description</td><td>" & _
objError.Description &
"</td></tr></table>"
CheckForErrors = true
else
CheckForErrors = False
end if
next
end Function
'--------------------------------------------------------------------------
-
---------
CloseConnection
end if
'-------close database connection and release the
resources--------------------------
sub CloseConnection()
if TypeName(adoRs) = "Recordset" then
if adoRs.state <> adStateClosed then
adoRs.close
set adoRs = nothing
end if
end if
'If it's a connection and open, close it
if TypeName(Conn) = "Connection" then
if Conn.state <> adStateClosed then
Conn.close
set Conn = nothing
end if
end if
end sub
%>
</BODY>
</HTML>
|
|
|
View Cart
