|
 |
asp_databases thread: Access record locking
Message #1 by "sara" <fillet70@h...> on Tue, 18 Feb 2003 16:28:53
|
|
Hi,
Q1) I'm using ASP with ADO to connect to an Access 2000 databse.
I want to lock the record on the select issued before the update statement.
I dont want to lock the recordset(using the optimistic and pessimistic
parameters). I come from a Sybase/DB2 background.
I plan to update my record as follows..
For example, create a LastUpdatedTimestamp field in every table. Whenever
i retrieve records from the DB, i also extract this datestamp field and
pass it back to the client. When the changes from the client are sent back
to the DB, check the datestamp extracted with the datestamp in the DB. If
they are the same, then no one has changed the data in the interim. If
they are different, then someone else has changed the data in the interim,
so an error message will be displayed to the user.
When the data in the form is sent to the server,
i issue the Select statement followed by the Update statement. Can i issue
the select with an intent to update cause i want the record to be locked
as soon as i issue the select. I dont want anybody to update the record
between my select and update.
How can i do this directly with the sql statement without locking the
recordset ?
Q2) Is there a way to write an insert trigger in access ?
Regards,
Sara
Message #2 by Sam Clohesy <samc@e...> on Tue, 18 Feb 2003 16:34:34 -0000
|
|
Hi-
Is there no chance you can use SQL server? (I had to do something similar to
this and ended up running transactions in SQL rather than mucking about with
access..)
Thats my twopeneth
Sam
Sam Clohesy
Project Manager
T: 0208 772 3958
E: samc@e...
W: http://www.etypemedia.co.uk
W: http://www.siteactive.net
-----Original Message-----
From: sara [mailto:fillet70@h...]
Sent: 18 February 2003 16:29
To: ASP Databases
Subject: [asp_databases] Access record locking
Hi,
Q1) I'm using ASP with ADO to connect to an Access 2000 databse.
I want to lock the record on the select issued before the update statement.
I dont want to lock the recordset(using the optimistic and pessimistic
parameters). I come from a Sybase/DB2 background.
I plan to update my record as follows..
For example, create a LastUpdatedTimestamp field in every table. Whenever
i retrieve records from the DB, i also extract this datestamp field and
pass it back to the client. When the changes from the client are sent back
to the DB, check the datestamp extracted with the datestamp in the DB. If
they are the same, then no one has changed the data in the interim. If
they are different, then someone else has changed the data in the interim,
so an error message will be displayed to the user.
When the data in the form is sent to the server,
i issue the Select statement followed by the Update statement. Can i issue
the select with an intent to update cause i want the record to be locked
as soon as i issue the select. I dont want anybody to update the record
between my select and update.
How can i do this directly with the sql statement without locking the
recordset ?
Q2) Is there a way to write an insert trigger in access ?
Regards,
Sara
Message #3 by "Ken Schaefer" <ken@a...> on Wed, 19 Feb 2003 11:39:39 +1100
|
|
Answer to Q1)
Avoid the select statement altogether. Issue an UPDATE statement and add the
datestamp to the WHERE clause. If the value of the datestamp has changed in
the database, then no records will be updated:
<%
strSQL = _
"UPDATE..." & _
"WHERE RecordID = " & intRecordID & " " & _
"AND DateStamp = #" dtePostedDateStampe & "#"
objConn.Execute strSQL, intRecsAffected, adCmdText+adExecuteNoRecords
If intRecsAffected = 0 then
' no records updated
Elseif intRecsAffected = 1 then
' 1 record updated - success
Else
' uh-oh we shouldn't be seeing this
End If
%>
Answer to Q2)
Not as far as I know
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "sara" <fillet70@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, February 18, 2003 4:28 PM
Subject: [asp_databases] Access record locking
: Hi,
:
: Q1) I'm using ASP with ADO to connect to an Access 2000 databse.
: I want to lock the record on the select issued before the update
statement.
: I dont want to lock the recordset(using the optimistic and pessimistic
: parameters). I come from a Sybase/DB2 background.
:
: I plan to update my record as follows..
: For example, create a LastUpdatedTimestamp field in every table. Whenever
: i retrieve records from the DB, i also extract this datestamp field and
: pass it back to the client. When the changes from the client are sent back
: to the DB, check the datestamp extracted with the datestamp in the DB. If
: they are the same, then no one has changed the data in the interim. If
: they are different, then someone else has changed the data in the interim,
: so an error message will be displayed to the user.
:
: When the data in the form is sent to the server,
: i issue the Select statement followed by the Update statement. Can i issue
: the select with an intent to update cause i want the record to be locked
: as soon as i issue the select. I dont want anybody to update the record
: between my select and update.
:
: How can i do this directly with the sql statement without locking the
: recordset ?
:
: Q2) Is there a way to write an insert trigger in access ?
:
: Regards,
: Sara
Message #4 by "sara" <fillet70@h...> on Wed, 19 Feb 2003 20:54:41
|
|
Hi,
Thanks for the reply. I tried it but Javascript does not return the no. of
records after an update. That is applicable only for vbscript.
Since i have no option i'm planning to lock the recordset and update the
recordset.
When i try to open the recordset i get an error that adOpenKeyset is
undefined. I tried adOpenKeySet. I still get the same error.
Is it because adojavas.inc is not in my directory on the webserver. Should
i include the full path of this file after talking to web server admin ?
The code is as follows.
<!--include File="adojavas.inc"-->
...
...
oConn = Server.CreateObject("ADODB.Connection");
oConn.Open("TimeReportingDSN");
oRs= Server.CreateObject("ADODB.RecordSet");
sql="SELECT user_id,eff_dt, name, dts From USER where login = " + "'" +
loginid + "'" + " and company_id = " + company_id ;
oRs.Open(sql,oConn,adOpenKeyset,adLockOptimistic,adCmdText);
oRs("name")=user_name;
oRs.Update();
Regards,
Sara
> Answer to Q1)
Avoid the select statement altogether. Issue an UPDATE statement and add
the
datestamp to the WHERE clause. If the value of the datestamp has changed in
the database, then no records will be updated:
<%
strSQL = _
"UPDATE..." & _
"WHERE RecordID = " & intRecordID & " " & _
"AND DateStamp = #" dtePostedDateStampe & "#"
objConn.Execute strSQL, intRecsAffected, adCmdText+adExecuteNoRecords
If intRecsAffected = 0 then
' no records updated
Elseif intRecsAffected = 1 then
' 1 record updated - success
Else
' uh-oh we shouldn't be seeing this
End If
%>
Answer to Q2)
Not as far as I know
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "sara" <fillet70@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, February 18, 2003 4:28 PM
Subject: [asp_databases] Access record locking
: Hi,
:
: Q1) I'm using ASP with ADO to connect to an Access 2000 databse.
: I want to lock the record on the select issued before the update
statement.
: I dont want to lock the recordset(using the optimistic and pessimistic
: parameters). I come from a Sybase/DB2 background.
:
: I plan to update my record as follows..
: For example, create a LastUpdatedTimestamp field in every table. Whenever
: i retrieve records from the DB, i also extract this datestamp field and
: pass it back to the client. When the changes from the client are sent
back
: to the DB, check the datestamp extracted with the datestamp in the DB. If
: they are the same, then no one has changed the data in the interim. If
: they are different, then someone else has changed the data in the
interim,
: so an error message will be displayed to the user.
:
: When the data in the form is sent to the server,
: i issue the Select statement followed by the Update statement. Can i
issue
: the select with an intent to update cause i want the record to be locked
: as soon as i issue the select. I dont want anybody to update the record
: between my select and update.
:
: How can i do this directly with the sql statement without locking the
: recordset ?
:
: Q2) Is there a way to write an insert trigger in access ?
:
: Regards,
: Sara
Message #5 by "sara" <fillet70@h...> on Wed, 19 Feb 2003 22:39:21
|
|
Hi,
I downloaded adojavas.inc from the web and put it in home directory on
the web server. I still get the error that adOpenKeyset is undefined. What
could be the problem ?
Regards,
Sara
> Hi,
> Thanks for the reply. I tried it but Javascript does not return the no.
of
r> ecords after an update. That is applicable only for vbscript.
S> ince i have no option i'm planning to lock the recordset and update the
r> ecordset.
W> hen i try to open the recordset i get an error that adOpenKeyset is
u> ndefined. I tried adOpenKeySet. I still get the same error.
I> s it because adojavas.inc is not in my directory on the webserver.
Should
i> include the full path of this file after talking to web server admin ?
> The code is as follows.
<> !--include File="adojavas.inc"-->
.> ..
.> ..
o> Conn = Server.CreateObject("ADODB.Connection");
o> Conn.Open("TimeReportingDSN");
o> Rs= Server.CreateObject("ADODB.RecordSet");
> sql="SELECT user_id,eff_dt, name, dts From USER where login = " + "'" +
l> oginid + "'" + " and company_id = " + company_id ;
o> Rs.Open(sql,oConn,adOpenKeyset,adLockOptimistic,adCmdText);
o> Rs("name")=user_name;
o> Rs.Update();
>
R> egards,
S> ara
>
>> Answer to Q1)
A> void the select statement altogether. Issue an UPDATE statement and add
t> he
d> atestamp to the WHERE clause. If the value of the datestamp has changed
in
t> he database, then no records will be updated:
> <%
s> trSQL = _
> "UPDATE..." & _
> "WHERE RecordID = " & intRecordID & " " & _
> "AND DateStamp = #" dtePostedDateStampe & "#"
> objConn.Execute strSQL, intRecsAffected, adCmdText+adExecuteNoRecords
> If intRecsAffected = 0 then
> ' no records updated
E> lseif intRecsAffected = 1 then
> ' 1 record updated - success
E> lse
> ' uh-oh we shouldn't be seeing this
E> nd If
%> >
> Answer to Q2)
N> ot as far as I know
> Cheers
K> en
>
~> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-> ---- Original Message -----
F> rom: "sara" <fillet70@h...>
T> o: "ASP Databases" <asp_databases@p...>
S> ent: Tuesday, February 18, 2003 4:28 PM
S> ubject: [asp_databases] Access record locking
>
:> Hi,
:>
:> Q1) I'm using ASP with ADO to connect to an Access 2000 databse.
:> I want to lock the record on the select issued before the update
s> tatement.
:> I dont want to lock the recordset(using the optimistic and pessimistic
:> parameters). I come from a Sybase/DB2 background.
:>
:> I plan to update my record as follows..
:> For example, create a LastUpdatedTimestamp field in every table.
Whenever
:> i retrieve records from the DB, i also extract this datestamp field and
:> pass it back to the client. When the changes from the client are sent
b> ack
:> to the DB, check the datestamp extracted with the datestamp in the DB.
If
:> they are the same, then no one has changed the data in the interim. If
:> they are different, then someone else has changed the data in the
i> nterim,
:> so an error message will be displayed to the user.
:>
:> When the data in the form is sent to the server,
:> i issue the Select statement followed by the Update statement. Can i
i> ssue
:> the select with an intent to update cause i want the record to be
locked
:> as soon as i issue the select. I dont want anybody to update the record
:> between my select and update.
:>
:> How can i do this directly with the sql statement without locking the
:> recordset ?
:>
:> Q2) Is there a way to write an insert trigger in access ?
:>
:> Regards,
:> Sara
Message #6 by "Ken Schaefer" <ken@a...> on Thu, 20 Feb 2003 10:55:48 +1100
|
|
< !--include File="adojavas.inc"--> is incorrect.
You need:
<!-- #include file="adojavas.inc" -->
or
<!-- #include virtual="/adojavas.inc" -->
(note the use of # - otherwise all you have is a HTML comment. The # turns
it into a server-side include)
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "sara" <fillet70@h...>
Subject: [asp_databases] Re: Access record locking
: Hi,
: I downloaded adojavas.inc from the web and put it in home directory on
: the web server. I still get the error that adOpenKeyset is undefined. What
: could be the problem ?
:
: Regards,
: Sara
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #7 by "sara" <fillet70@h...> on Thu, 20 Feb 2003 15:22:23
|
|
Thank you. That worked.
> < !--include File="adojavas.inc"--> is incorrect.
You need:
<!-- #include file="adojavas.inc" -->
or
<!-- #include virtual="/adojavas.inc" -->
(note the use of # - otherwise all you have is a HTML comment. The # turns
it into a server-side include)
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "sara" <fillet70@h...>
Subject: [asp_databases] Re: Access record locking
: Hi,
: I downloaded adojavas.inc from the web and put it in home directory on
: the web server. I still get the error that adOpenKeyset is undefined.
What
: could be the problem ?
:
: Regards,
: Sara
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #8 by "Meinken, Joe" <Joe.Meinken@q...> on Tue, 25 Feb 2003 10:02:16 -0600
|
|
is adOpenKeyset assigned a value within the included file? If not,
including it will not resolve this issue. You will have to define the
constant and assign a value to it.
-----Original Message-----
From: sara [mailto:fillet70@h...]
Sent: Wednesday, February 19, 2003 4:39 PM
To: ASP Databases
Subject: [asp_databases] Re: Access record locking
Hi,
I downloaded adojavas.inc from the web and put it in home directory on
the web server. I still get the error that adOpenKeyset is undefined. What
could be the problem ?
Regards,
Sara
> Hi,
> Thanks for the reply. I tried it but Javascript does not return the no.
of
r> ecords after an update. That is applicable only for vbscript.
S> ince i have no option i'm planning to lock the recordset and update the
r> ecordset.
W> hen i try to open the recordset i get an error that adOpenKeyset is
u> ndefined. I tried adOpenKeySet. I still get the same error.
I> s it because adojavas.inc is not in my directory on the webserver.
Should
i> include the full path of this file after talking to web server admin ?
> The code is as follows.
<> !--include File="adojavas.inc"-->
.> ..
.> ..
o> Conn = Server.CreateObject("ADODB.Connection");
o> Conn.Open("TimeReportingDSN");
o> Rs= Server.CreateObject("ADODB.RecordSet");
> sql="SELECT user_id,eff_dt, name, dts From USER where login = " + "'" +
l> oginid + "'" + " and company_id = " + company_id ;
o> Rs.Open(sql,oConn,adOpenKeyset,adLockOptimistic,adCmdText);
o> Rs("name")=user_name;
o> Rs.Update();
>
R> egards,
S> ara
>
>> Answer to Q1)
A> void the select statement altogether. Issue an UPDATE statement and add
t> he
d> atestamp to the WHERE clause. If the value of the datestamp has changed
in
t> he database, then no records will be updated:
> <%
s> trSQL = _
> "UPDATE..." & _
> "WHERE RecordID = " & intRecordID & " " & _
> "AND DateStamp = #" dtePostedDateStampe & "#"
> objConn.Execute strSQL, intRecsAffected, adCmdText+adExecuteNoRecords
> If intRecsAffected = 0 then
> ' no records updated
E> lseif intRecsAffected = 1 then
> ' 1 record updated - success
E> lse
> ' uh-oh we shouldn't be seeing this
E> nd If
%> >
> Answer to Q2)
N> ot as far as I know
> Cheers
K> en
>
~> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-> ---- Original Message -----
F> rom: "sara" <fillet70@h...>
T> o: "ASP Databases" <asp_databases@p...>
S> ent: Tuesday, February 18, 2003 4:28 PM
S> ubject: [asp_databases] Access record locking
>
:> Hi,
:>
:> Q1) I'm using ASP with ADO to connect to an Access 2000 databse.
:> I want to lock the record on the select issued before the update
s> tatement.
:> I dont want to lock the recordset(using the optimistic and pessimistic
:> parameters). I come from a Sybase/DB2 background.
:>
:> I plan to update my record as follows..
:> For example, create a LastUpdatedTimestamp field in every table.
Whenever
:> i retrieve records from the DB, i also extract this datestamp field and
:> pass it back to the client. When the changes from the client are sent
b> ack
:> to the DB, check the datestamp extracted with the datestamp in the DB.
If
:> they are the same, then no one has changed the data in the interim. If
:> they are different, then someone else has changed the data in the
i> nterim,
:> so an error message will be displayed to the user.
:>
:> When the data in the form is sent to the server,
:> i issue the Select statement followed by the Update statement. Can i
i> ssue
:> the select with an intent to update cause i want the record to be
locked
:> as soon as i issue the select. I dont want anybody to update the record
:> between my select and update.
:>
:> How can i do this directly with the sql statement without locking the
:> recordset ?
:>
:> Q2) Is there a way to write an insert trigger in access ?
:>
:> Regards,
:> Sara
|
|
|
View Cart
