Sir,

I am a newbie to web technology & databases.

I designed a web database (ASP & SQL'2000). So far so good with the esteem 
help of this discussion group. I am able to insert, update, delete etc.
I am wondering, how to prevent these :-

user A update a record, user B viewing at the same time
user A deleting a record, user B viewing at the same time.

How to handle these sought of operations. So far, my record set statement's
look like this :-

Set recs= Server.CreateObject("ADODB.RecordSet")
recs.ActiveConnection=Conn
recs.Open "Select * from tblMG WHERE Sequence='"&vSeq&"'", Conn

Do I need to add any attributes here. Kindly let me know.

One more thing, when I am trying to post messages in other categories, it 
is not displaying my messages.

Thanks,
Jay

You'll want to run your query using all of the parameters of the 
RecordSet Object as follows:

Dim sSQL, rsAdd
	Set rsAdd = Server.CreateObject("ADODB.Recordset")
	
	sSQL = "SELECT * FROM tblMG"
	sSQL=sSQL& " WHERE Sequence='"&vSeq&"';"
	
	rsAdd.Open sSQL, Conn, adOpenForwardOnly, adLockOptimistic, 
adCmdText

The 4th parameter is the Lock.  You can choose from adLockReadOnly, 
adLockPessimisic, adLockOptimistic, and adLockOptimisticBatch.  You 
should be able to find info on these locks easily online.  Also, don't 
forget to use the msado15.dll or adovbs.inc file.

- Tim


> Sir,

> I am a newbie to web technology & databases.

> I designed a web database (ASP & SQL'2000). So far so good with the 
esteem 
h> elp of this discussion group. I am able to insert, update, delete etc.
I>  am wondering, how to prevent these :-

> user A update a record, user B viewing at the same time
u> ser A deleting a record, user B viewing at the same time.

> How to handle these sought of operations. So far, my record set 
statement's
l> ook like this :-

> Set recs= Server.CreateObject("ADODB.RecordSet")
r> ecs.ActiveConnection=Conn
r> ecs.Open "Select * from tblMG WHERE Sequence='"&vSeq&"'", Conn

> Do I need to add any attributes here. Kindly let me know.

> One more thing, when I am trying to post messages in other categories, 
it 
i> s not displaying my messages.

> Thanks,
J> ay

None of that is going to help...

The web is "disconnected". If user A deletes the record whilst User B is
viewing it in a webpage, none of the ADO locking mechanisms are going to
help.

Instead, you need a "collision detection" strategy. Eg a date stamp for the
last update for record. When the changes to a record are posted back to the
server, the date stamp that is posted back is compared to the date stamp in
the database. If they are the same, then no one has updated the record in
the meantime, so commit the user's changes. If the datestamps are different,
then the user needs to be warned that they were viewing out-of-date
information.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Timothy M. Straub" <tim@r...>
Subject: [asp_databases] Re: Database--- concurrent usage


: You'll want to run your query using all of the parameters of the
: RecordSet Object as follows:
:
: Dim sSQL, rsAdd
: Set rsAdd = Server.CreateObject("ADODB.Recordset")
:
: sSQL = "SELECT * FROM tblMG"
: sSQL=sSQL& " WHERE Sequence='"&vSeq&"';"
:
: rsAdd.Open sSQL, Conn, adOpenForwardOnly, adLockOptimistic,
: adCmdText
:
: The 4th parameter is the Lock.  You can choose from adLockReadOnly,
: adLockPessimisic, adLockOptimistic, and adLockOptimisticBatch.  You
: should be able to find info on these locks easily online.  Also, don't
: forget to use the msado15.dll or adovbs.inc file.
:
: - Tim
:
:
: > Sir,
:
: > I am a newbie to web technology & databases.
:
: > I designed a web database (ASP & SQL'2000). So far so good with the
: esteem
: h> elp of this discussion group. I am able to insert, update, delete etc.
: I>  am wondering, how to prevent these :-
:
: > user A update a record, user B viewing at the same time
: u> ser A deleting a record, user B viewing at the same time.
:
: > How to handle these sought of operations. So far, my record set
: statement's
: l> ook like this :-
:
: > Set recs= Server.CreateObject("ADODB.RecordSet")
: r> ecs.ActiveConnection=Conn
: r> ecs.Open "Select * from tblMG WHERE Sequence='"&vSeq&"'", Conn
:
: > Do I need to add any attributes here. Kindly let me know.
:
: > One more thing, when I am trying to post messages in other categories,
: it
: i> s not displaying my messages.
:
: > Thanks,
: J> ay