ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement
I got ASP error number 13 when I use the SELECT...FOR UPDATE statement
as below.
However, if I use SELECT statement without FOR UPDATE, it is fine and
no error.
I also tried Set objRs = objConn.Execute ("SELECT * FROM EMP UPDATE OF
EMPNO"), but it still couldn't help.
any ideas? I tried to search in the web but couldn't find similar
problem. Is it because
the setting problems?
Here's the code fragment:
=============== =========
On Error Resume Next
Set objConn = Server.CreateOb ject("ADODB.Con nection")
objConn.Open strConnectionSt ring
Set objRs = Server.CreateOb ject("ADODB.Rec ordSet")
objRs.CursorLoc ation = adUseClient
objRs.open "SELECT * FROM EMP UPDATE OF EMPNO", objConn,
adOpenForwardOn ly, adLockBatchOpti mistic
//etc...
ErrHndl:
response.write Err.Source & "<br>"
response.write "Error number " & err.number & "<br>"
response.write "Error description " & err.description & "<br>"
response.end
Errors:
=============== ========
Microsoft VBScript runtime error
Error number 13
Error description Type mismatch
Please advise. thanks a lot!!
May 24 '06
19  8382 
Basically below is what I am doing, and the interesting observation is
that Oracle SELECT ... FOR UPDATE clause needs to use with
objConn.BeginTr ans method. I still don't understand what is the reason.
Any ideas?
Another concern is do you think it is possible another transaction can
happen right after objConn.Rollbac kTrans (after step 1 & 2), since the
lock has released? If this is the case, then current_balance may not be
updated when the application calls tb_update() and tb_insert() methods.
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance
code fragment of get_balance() method:
=============== =============== =======
objConn.BeginTr ans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOn ly, adLockBatchOpti mistic
current_balance = objRS(0)
objConn.Rollbac kTrans
Please advise more. thanks!!!
More details please. It appears that you are planning to write some sort of
procedural code to do something that can be done in a single efficient
set-based operation.
I'm still not clear what the FOR UPDATE clause does for you in Oracle.
Steve wrote: Basically below is what I am doing, and the interesting observation is
that Oracle SELECT ... FOR UPDATE clause needs to use with
objConn.BeginTr ans method. I still don't understand what is the
reason. Any ideas?
Another concern is do you think it is possible another transaction can
happen right after objConn.Rollbac kTrans (after step 1 & 2), since the
lock has released? If this is the case, then current_balance may not
be updated when the application calls tb_update() and tb_insert()
methods.
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance
code fragment of get_balance() method:
=============== =============== =======
objConn.BeginTr ans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOn ly, adLockBatchOpti mistic
current_balance = objRS(0)
objConn.Rollbac kTrans
Please advise more. thanks!!!
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
On reflection, I think you may get quicker, more focussed help if you find
an Oracle newsgroup or forum and post your scenario there. They will
probably be more able to tell you the most efficient way to accomplish your
task than we would.
This really sounds like the type of activity I would be doing via a stored
procedure.
Bob Barrows
Bob Barrows [MVP] wrote: More details please. It appears that you are planning to write some
sort of procedural code to do something that can be done in a single
efficient set-based operation.
I'm still not clear what the FOR UPDATE clause does for you in Oracle.
Steve wrote: Basically below is what I am doing, and the interesting observation
is that Oracle SELECT ... FOR UPDATE clause needs to use with
objConn.BeginTr ans method. I still don't understand what is the
reason. Any ideas?
Another concern is do you think it is possible another transaction
can happen right after objConn.Rollbac kTrans (after step 1 & 2),
since the lock has released? If this is the case, then
current_balance may not be updated when the application calls tb_update()
and tb_insert()
methods.
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance
code fragment of get_balance() method:
=============== =============== =======
objConn.BeginTr ans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOn ly, adLockBatchOpti mistic
current_balance = objRS(0)
objConn.Rollbac kTrans
Please advise more. thanks!!!
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
"Steve" <ja*****@gmail. com> wrote in message
news:11******** **************@ j73g2000cwa.goo glegroups.com.. . Basically below is what I am doing, and the interesting observation is
that Oracle SELECT ... FOR UPDATE clause needs to use with
objConn.BeginTr ans method. I still don't understand what is the reason.
Any ideas?
Another concern is do you think it is possible another transaction can
happen right after objConn.Rollbac kTrans (after step 1 & 2), since the
lock has released? If this is the case, then current_balance may not be
updated when the application calls tb_update() and tb_insert() methods.
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance
code fragment of get_balance() method:
=============== =============== =======
objConn.BeginTr ans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOn ly, adLockBatchOpti mistic
current_balance = objRS(0)
objConn.Rollbac kTrans
Please advise more. thanks!!!
FOR UPDATE informs Oracle that the rows in the selected set are about to be
updated. It therefore will place a lock on them. For that lock to have any
meaning it needs to be made in the context of a transaction.
The if you use the RollbackTrans call as it seems you are in the code above
you might as well not bother with the transaction at all and drop the FOR
UPDATE clause.
The sequence you've outlined above should be protected by a transaction. It
would be best handled by an SP in ORACLE that does all the transaction
management locally. If you must do it in ASP then you need a
BeginTrans/CommitTrans to span the whole sequence.
Bob, FOR UPDATE is the same as using the table hint SERIALIZABLE in SQL
Server.
Anthony Jones wrote: "Steve" <ja*****@gmail. com> wrote in message
news:11******** **************@ j73g2000cwa.goo glegroups.com.. . Basically below is what I am doing, and the interesting observation
is that Oracle SELECT ... FOR UPDATE clause needs to use with
objConn.BeginTr ans method. I still don't understand what is the
reason. Any ideas?
Another concern is do you think it is possible another transaction
can happen right after objConn.Rollbac kTrans (after step 1 & 2),
since the lock has released? If this is the case, then
current_balance may not be updated when the application calls
tb_update() and tb_insert() methods.
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance
code fragment of get_balance() method:
=============== =============== =======
objConn.BeginTr ans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOn ly, adLockBatchOpti mistic
current_balance = objRS(0)
objConn.Rollbac kTrans
Please advise more. thanks!!!
FOR UPDATE informs Oracle that the rows in the selected set are about
to be updated. It therefore will place a lock on them. For that
lock to have any meaning it needs to be made in the context of a
transaction.
The if you use the RollbackTrans call as it seems you are in the code
above you might as well not bother with the transaction at all and
drop the FOR UPDATE clause.
The sequence you've outlined above should be protected by a
transaction. It would be best handled by an SP in ORACLE that does
all the transaction management locally. If you must do it in ASP
then you need a BeginTrans/CommitTrans to span the whole sequence.
Bob, FOR UPDATE is the same as using the table hint SERIALIZABLE in
SQL Server.
Ah, that turns the light on! Thanks.
Still, it seems to me that a set-based rather than cursor-based solution
should be pursued for this.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Anthony:
I will use ASP approach without using stored procedure. What bothers me
is that there are 3 methods that perform 3 different transactions. Do
you suggest I should put all 3 different transactions into a single
method?
In get_balance() method, I put SELECT...FOR UPDATE statement inside
BeginTrans/RollbackTrans block instead of BeginTrans/CommitTrans, since
I think it just tries to lock the row and no updates happen in that
get_balance() method. However, there will be table updates in
tb_update() and tb_insert() methods. It seems to me that ASP requires
BeginTrans method in order to use SELECT...FOR UPDATE statement, but I
have no idea why. My understanding is that BeginTrans will open a
nested transaction, but what is the rationale behind that?
Another concern is that if I continue to use 3 separate methods, is it
possible to have transactions happen in between method calls? Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance
code fragment of get_balance() method:
=============== =============== =======
objConn.BeginTr ans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOn ly, adLockBatchOpti mistic
current_balance = objRS(0)
objConn.Rollbac kTrans
Again, pleae advise more and thanks for your suggestions and inputs.
Steve.
Anthony Jones wrote: "Steve" <ja*****@gmail. com> wrote in message
news:11******** **************@ j73g2000cwa.goo glegroups.com.. . Basically below is what I am doing, and the interesting observation is
that Oracle SELECT ... FOR UPDATE clause needs to use with
objConn.BeginTr ans method. I still don't understand what is the reason.
Any ideas?
Another concern is do you think it is possible another transaction can
happen right after objConn.Rollbac kTrans (after step 1 & 2), since the
lock has released? If this is the case, then current_balance may not be
updated when the application calls tb_update() and tb_insert() methods.
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance
code fragment of get_balance() method:
=============== =============== =======
objConn.BeginTr ans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOn ly, adLockBatchOpti mistic
current_balance = objRS(0)
objConn.Rollbac kTrans
Please advise more. thanks!!!
FOR UPDATE informs Oracle that the rows in the selected set are about to be
updated. It therefore will place a lock on them. For that lock to have any
meaning it needs to be made in the context of a transaction.
The if you use the RollbackTrans call as it seems you are in the code above
you might as well not bother with the transaction at all and drop the FOR
UPDATE clause.
The sequence you've outlined above should be protected by a transaction. It
would be best handled by an SP in ORACLE that does all the transaction
management locally. If you must do it in ASP then you need a
BeginTrans/CommitTrans to span the whole sequence.
Bob, FOR UPDATE is the same as using the table hint SERIALIZABLE in SQL
Server.
Last time I checked, "UPDATE FOR ..." was not a valid statement in an SQL
string .... it should instead be;
sSQL = "UPDATE [TABLE_NAME] SET FIELD1 = '" & New_Value & "' etc
etc........."
Not quite sure where the BeginTrans/RollbackTrans etc comes from as I've
never heard of them myself ......... but thats just me....
As an additional FYI ..... you should NEVER EVER EVER EVER use "Select *" http://aspfaq.com/show.asp?id=2096
But the experts know best so I shall retire back to my lil' corner ........
--
Regards
Steven Burn
Ur I.T. Mate Group www.it-mate.co.uk
Keeping it FREE!
"Steve" <ja*****@gmail. com> wrote in message
news:11******** *************@j 55g2000cwa.goog legroups.com... Anthony:
I will use ASP approach without using stored procedure. What bothers me
is that there are 3 methods that perform 3 different transactions. Do
you suggest I should put all 3 different transactions into a single
method?
In get_balance() method, I put SELECT...FOR UPDATE statement inside
BeginTrans/RollbackTrans block instead of BeginTrans/CommitTrans, since
I think it just tries to lock the row and no updates happen in that
get_balance() method. However, there will be table updates in
tb_update() and tb_insert() methods. It seems to me that ASP requires
BeginTrans method in order to use SELECT...FOR UPDATE statement, but I
have no idea why. My understanding is that BeginTrans will open a
nested transaction, but what is the rationale behind that?
Another concern is that if I continue to use 3 separate methods, is it
possible to have transactions happen in between method calls?
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance
code fragment of get_balance() method:
=============== =============== =======
objConn.BeginTr ans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOn ly, adLockBatchOpti mistic
current_balance = objRS(0)
objConn.Rollbac kTrans
Again, pleae advise more and thanks for your suggestions and inputs.
Steve.
Anthony Jones wrote: "Steve" <ja*****@gmail. com> wrote in message
news:11******** **************@ j73g2000cwa.goo glegroups.com.. . Basically below is what I am doing, and the interesting observation is
that Oracle SELECT ... FOR UPDATE clause needs to use with
objConn.BeginTr ans method. I still don't understand what is the
reason. Any ideas?
Another concern is do you think it is possible another transaction can
happen right after objConn.Rollbac kTrans (after step 1 & 2), since the
lock has released? If this is the case, then current_balance may not
be updated when the application calls tb_update() and tb_insert()
methods.
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance
code fragment of get_balance() method:
=============== =============== =======
objConn.BeginTr ans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOn ly, adLockBatchOpti mistic
current_balance = objRS(0)
objConn.Rollbac kTrans
Please advise more. thanks!!!
FOR UPDATE informs Oracle that the rows in the selected set are about to
be updated. It therefore will place a lock on them. For that lock to have
any meaning it needs to be made in the context of a transaction.
The if you use the RollbackTrans call as it seems you are in the code
above you might as well not bother with the transaction at all and drop the
FOR UPDATE clause.
The sequence you've outlined above should be protected by a transaction.
It would be best handled by an SP in ORACLE that does all the transaction
management locally. If you must do it in ASP then you need a
BeginTrans/CommitTrans to span the whole sequence.
Bob, FOR UPDATE is the same as using the table hint SERIALIZABLE in SQL
Server.
'UPDATE FOR' is an Oracle DB statement. Apparently, it locks rows that are
being updated.
Bob Lehmann
"Steven Burn" <so*******@in-time.invalid> wrote in message
news:O9******** ******@TK2MSFTN GP02.phx.gbl... Last time I checked, "UPDATE FOR ..." was not a valid statement in an SQL
string .... it should instead be;
sSQL = "UPDATE [TABLE_NAME] SET FIELD1 = '" & New_Value & "' etc
etc........."
Not quite sure where the BeginTrans/RollbackTrans etc comes from as I've
never heard of them myself ......... but thats just me....
As an additional FYI ..... you should NEVER EVER EVER EVER use "Select *"
http://aspfaq.com/show.asp?id=2096
But the experts know best so I shall retire back to my lil' corner
.........
--
Regards
Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk
Keeping it FREE!
"Steve" <ja*****@gmail. com> wrote in message
news:11******** *************@j 55g2000cwa.goog legroups.com... Anthony:
I will use ASP approach without using stored procedure. What bothers me
is that there are 3 methods that perform 3 different transactions. Do
you suggest I should put all 3 different transactions into a single
method?
In get_balance() method, I put SELECT...FOR UPDATE statement inside
BeginTrans/RollbackTrans block instead of BeginTrans/CommitTrans, since
I think it just tries to lock the row and no updates happen in that
get_balance() method. However, there will be table updates in
tb_update() and tb_insert() methods. It seems to me that ASP requires
BeginTrans method in order to use SELECT...FOR UPDATE statement, but I
have no idea why. My understanding is that BeginTrans will open a
nested transaction, but what is the rationale behind that?
Another concern is that if I continue to use 3 separate methods, is it
possible to have transactions happen in between method calls?
> Pseudocode
> ==========
> //1) call get_balance() method to perform select statement to get
> current_balance
> //some transaction happens here ????
> //2) call tb_update() method to perform update statement based on
> current_balance
> //some transaction happens here ????
> //3) call tb_insert() method to perform insert statement based on
> current_balance
>
> code fragment of get_balance() method:
> =============== =============== =======
> objConn.BeginTr ans
> objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
> adOpenForwardOn ly, adLockBatchOpti mistic
> current_balance = objRS(0)
> objConn.Rollbac kTrans
Again, pleae advise more and thanks for your suggestions and inputs.
Steve.
Anthony Jones wrote: "Steve" <ja*****@gmail. com> wrote in message
news:11******** **************@ j73g2000cwa.goo glegroups.com.. .
> Basically below is what I am doing, and the interesting observation
is > that Oracle SELECT ... FOR UPDATE clause needs to use with
> objConn.BeginTr ans method. I still don't understand what is the reason. > Any ideas?
>
> Another concern is do you think it is possible another transaction
can > happen right after objConn.Rollbac kTrans (after step 1 & 2), since
the > lock has released? If this is the case, then current_balance may not be > updated when the application calls tb_update() and tb_insert() methods. >
>
> Pseudocode
> ==========
> //1) call get_balance() method to perform select statement to get
> current_balance
> //some transaction happens here ????
> //2) call tb_update() method to perform update statement based on
> current_balance
> //some transaction happens here ????
> //3) call tb_insert() method to perform insert statement based on
> current_balance
>
> code fragment of get_balance() method:
> =============== =============== =======
> objConn.BeginTr ans
> objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
> adOpenForwardOn ly, adLockBatchOpti mistic
> current_balance = objRS(0)
> objConn.Rollbac kTrans
>
>
>
> Please advise more. thanks!!!
>
FOR UPDATE informs Oracle that the rows in the selected set are about
to be updated. It therefore will place a lock on them. For that lock to
have any meaning it needs to be made in the context of a transaction.
The if you use the RollbackTrans call as it seems you are in the code above you might as well not bother with the transaction at all and drop the FOR UPDATE clause.
The sequence you've outlined above should be protected by a
transaction. It would be best handled by an SP in ORACLE that does all the transaction
management locally. If you must do it in ASP then you need a
BeginTrans/CommitTrans to span the whole sequence.
Bob, FOR UPDATE is the same as using the table hint SERIALIZABLE in
SQL Server.
"Steve" <ja*****@gmail. com> wrote in message
news:11******** *************@j 55g2000cwa.goog legroups.com... Anthony:
I will use ASP approach without using stored procedure. What bothers me
is that there are 3 methods that perform 3 different transactions. Do
you suggest I should put all 3 different transactions into a single
method?
In get_balance() method, I put SELECT...FOR UPDATE statement inside
BeginTrans/RollbackTrans block instead of BeginTrans/CommitTrans, since
I think it just tries to lock the row and no updates happen in that
get_balance() method. However, there will be table updates in
tb_update() and tb_insert() methods. It seems to me that ASP requires
BeginTrans method in order to use SELECT...FOR UPDATE statement, but I
have no idea why. My understanding is that BeginTrans will open a
nested transaction, but what is the rationale behind that?
Where's the nested transaction? Without a specific BeginTrans command the
only transaction is an implicit one that a DB engine may choose to create
fleetingly during the operation a single command.
In order to bind a series of commands under a transaction such a transaction
needs to be explicitly declared. Are you saying you are already doing that?
If so you should remember that a transaction is created with in the scope of
a connection so the same connection should be used for all commands that are
to operate under that transaction
Another concern is that if I continue to use 3 separate methods, is it
possible to have transactions happen in between method calls?
Without the protection of an explicit transaction declared before calling
these methods then yes. The whole point of FOR UPDATE is to lock a set of
rows so that don't change while modifications are being made. However a DB
Lock needs to be created in the context of an explicit transaction.
IMO this design is flawed. You should have a single method to update a
balance or whatever which in turn calls an SP that makes the change for you.
The SP should use ORACLEs native commands to create a transaction perform
the updates (and as Bob pointed out you probably don't even need the SELECT
to do this) and then commit the transaction.
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance
code fragment of get_balance() method:
=============== =============== =======
objConn.BeginTr ans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOn ly, adLockBatchOpti mistic
current_balance = objRS(0)
objConn.Rollbac kTrans
Again, pleae advise more and thanks for your suggestions and inputs.
Steve.
Anthony Jones wrote: "Steve" <ja*****@gmail. com> wrote in message
news:11******** **************@ j73g2000cwa.goo glegroups.com.. . Basically below is what I am doing, and the interesting observation is
that Oracle SELECT ... FOR UPDATE clause needs to use with
objConn.BeginTr ans method. I still don't understand what is the
reason. Any ideas?
Another concern is do you think it is possible another transaction can
happen right after objConn.Rollbac kTrans (after step 1 & 2), since the
lock has released? If this is the case, then current_balance may not
be updated when the application calls tb_update() and tb_insert()
methods.
Pseudocode
==========
//1) call get_balance() method to perform select statement to get
current_balance
//some transaction happens here ????
//2) call tb_update() method to perform update statement based on
current_balance
//some transaction happens here ????
//3) call tb_insert() method to perform insert statement based on
current_balance
code fragment of get_balance() method:
=============== =============== =======
objConn.BeginTr ans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOn ly, adLockBatchOpti mistic
current_balance = objRS(0)
objConn.Rollbac kTrans
Please advise more. thanks!!!
FOR UPDATE informs Oracle that the rows in the selected set are about to
be updated. It therefore will place a lock on them. For that lock to have
any meaning it needs to be made in the context of a transaction.
The if you use the RollbackTrans call as it seems you are in the code
above you might as well not bother with the transaction at all and drop the
FOR UPDATE clause.
The sequence you've outlined above should be protected by a transaction.
It would be best handled by an SP in ORACLE that does all the transaction
management locally. If you must do it in ASP then you need a
BeginTrans/CommitTrans to span the whole sequence.
Bob, FOR UPDATE is the same as using the table hint SERIALIZABLE in SQL
Server.
hehe prolly why I've never heard of it (don't use Oracle, lol).
Cheers for the correction ;o)
--
Regards
Steven Burn
Ur I.T. Mate Group www.it-mate.co.uk
Keeping it FREE!
"Bob Lehmann" <no****@dontbot herme.zzz> wrote in message
news:%2******** ********@TK2MSF TNGP05.phx.gbl. .. 'UPDATE FOR' is an Oracle DB statement. Apparently, it locks rows that are
being updated.
Bob Lehmann
"Steven Burn" <so*******@in-time.invalid> wrote in message
news:O9******** ******@TK2MSFTN GP02.phx.gbl... Last time I checked, "UPDATE FOR ..." was not a valid statement in an
SQL string .... it should instead be;
sSQL = "UPDATE [TABLE_NAME] SET FIELD1 = '" & New_Value & "' etc
etc........."
Not quite sure where the BeginTrans/RollbackTrans etc comes from as I've
never heard of them myself ......... but thats just me....
As an additional FYI ..... you should NEVER EVER EVER EVER use "Select
*"
http://aspfaq.com/show.asp?id=2096
But the experts know best so I shall retire back to my lil' corner ........
--
Regards
Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk
Keeping it FREE!
"Steve" <ja*****@gmail. com> wrote in message
news:11******** *************@j 55g2000cwa.goog legroups.com... Anthony:
I will use ASP approach without using stored procedure. What bothers
me is that there are 3 methods that perform 3 different transactions. Do
you suggest I should put all 3 different transactions into a single
method?
In get_balance() method, I put SELECT...FOR UPDATE statement inside
BeginTrans/RollbackTrans block instead of BeginTrans/CommitTrans,
since I think it just tries to lock the row and no updates happen in that
get_balance() method. However, there will be table updates in
tb_update() and tb_insert() methods. It seems to me that ASP requires
BeginTrans method in order to use SELECT...FOR UPDATE statement, but I
have no idea why. My understanding is that BeginTrans will open a
nested transaction, but what is the rationale behind that?
Another concern is that if I continue to use 3 separate methods, is it
possible to have transactions happen in between method calls?
> > Pseudocode
> > ==========
> > //1) call get_balance() method to perform select statement to get
> > current_balance
> > //some transaction happens here ????
> > //2) call tb_update() method to perform update statement based on
> > current_balance
> > //some transaction happens here ????
> > //3) call tb_insert() method to perform insert statement based on
> > current_balance
> >
> > code fragment of get_balance() method:
> > =============== =============== =======
> > objConn.BeginTr ans
> > objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
> > adOpenForwardOn ly, adLockBatchOpti mistic
> > current_balance = objRS(0)
> > objConn.Rollbac kTrans
Again, pleae advise more and thanks for your suggestions and inputs.
Steve.
Anthony Jones wrote:
> "Steve" <ja*****@gmail. com> wrote in message
> news:11******** **************@ j73g2000cwa.goo glegroups.com.. .
> > Basically below is what I am doing, and the interesting
observation is > > that Oracle SELECT ... FOR UPDATE clause needs to use with
> > objConn.BeginTr ans method. I still don't understand what is the reason. > > Any ideas?
> >
> > Another concern is do you think it is possible another transaction can > > happen right after objConn.Rollbac kTrans (after step 1 & 2), since the > > lock has released? If this is the case, then current_balance may
not be > > updated when the application calls tb_update() and tb_insert()
methods. > >
> >
> > Pseudocode
> > ==========
> > //1) call get_balance() method to perform select statement to get
> > current_balance
> > //some transaction happens here ????
> > //2) call tb_update() method to perform update statement based on
> > current_balance
> > //some transaction happens here ????
> > //3) call tb_insert() method to perform insert statement based on
> > current_balance
> >
> > code fragment of get_balance() method:
> > =============== =============== =======
> > objConn.BeginTr ans
> > objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
> > adOpenForwardOn ly, adLockBatchOpti mistic
> > current_balance = objRS(0)
> > objConn.Rollbac kTrans
> >
> >
> >
> > Please advise more. thanks!!!
> >
>
> FOR UPDATE informs Oracle that the rows in the selected set are
about to be > updated. It therefore will place a lock on them. For that lock to
have any > meaning it needs to be made in the context of a transaction.
>
> The if you use the RollbackTrans call as it seems you are in the
code above > you might as well not bother with the transaction at all and drop
the FOR > UPDATE clause.
>
> The sequence you've outlined above should be protected by a
transaction. It > would be best handled by an SP in ORACLE that does all the
transaction > management locally. If you must do it in ASP then you need a
> BeginTrans/CommitTrans to span the whole sequence.
>
>
> Bob, FOR UPDATE is the same as using the table hint SERIALIZABLE in
SQL > Server.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Robert Mark Bram |
last post by:
Hi All!
I have the following two methods in an asp/jscript page - my problem is that
without the update statement there is no error, but with the update
statement I get the following error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
Syntax error in UPDATE statement.
/polyprint/dataEntry.asp, line 158
|
by: Mike |
last post by:
I am getting a type mismatch error when I do a bulk insert.
---Begin Error Msg---
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 14
(STDCOST).
---End Error Msg---
The STDCOST is set to decimal (28,14) and is a formatted in Access as a
number, single with 14 decimal. I don't know why I would be getting a Type
|
by: news.paradise.net.nz |
last post by:
I have been developing access databases for over 5 years.
I have a large database and I have struck this problem with it before
but can find nothing in help or online. Access 2000
I have a query that will run fine without any criteria but as soon as I
add any criteria it gives a "Data type mismatch" error.
As soon as I remove any criteria it runs perfectly.
I know this query is based on another query but I have other processes
based on...
|
by: TD |
last post by:
I created a query in Access 2000 that runs perfectly. I then copied the
"sql" version of the same query and set it equal the variable "sql" in the
code below. When I run the code below I get an "Type Mismatch" error. Can
someone explain how to fix this?
Thanks,
TD
Private Sub cmdSave_Click()
|
by: aland |
last post by:
Hi, I'm hoping someone can help me with this code. I'm getting a 'Type
mismatch' error, and I'm not sure why. The SQL works fine in SQL view,
so I'm not sure if that's the problem or not.
This is a dumbed down version of what I'm trying to do so it looks like
more like an academic problem.
All I want at this stage is to get the highest value of table.id into a
variable.
Dim strSQL As String
Dim RS As Recordset
| | |
by: amitbadgi |
last post by:
HI i am getting the foll error while conv an asp application to
asp.net
Exception Details: System.Runtime.InteropServices.COMException: Syntax
error in UPDATE statement.
Source Error:
Line 112: MM_editCmd.ActiveConnection = MM_editConnection
Line 113: MM_editCmd.CommandText = MM_editQuery
Line 114: MM_editCmd.Execute
|
by: jodyblau |
last post by:
I am getting a type mismatch message under strange circumstances.
Here's whats going on:
1. I have split the database into a front end and a back end.
2. I have compiled the project.
3. I update the linked tables and then make an mde file.
4. When I use the mdb file, everything seems to work fine.
|
by: kjworm |
last post by:
Hello Everyone,
I have been fighting with a type mismatch error for many hours today and I can't seem to find what the problem is. Hopefully it is more than a missing apostrophe! I have isolated each SQL statement using a MsgBox and then input that exact code as a Query in access which works. I'm using Access 97 on Windows XP. My code is below:
Dim strKaizen As String
strKaizen = "UPDATE KaizenEvents SET(TeamLeader)= '" & & "' WHERE...
|
by: JaketheSnake27 |
last post by:
I am completely stumped on what seems to be a completely simple thing.
What I'm trying to do is use a SELECT CASE with a recordset. The SELECT CASE looks at one field from the recordset to establish the CASE's. I looked at the table that this field comes from and the field's data type is "text." When I make my CASE's, the program runs fine if I just put numbers for the requirements.
I.E. Case "734"
The problem I am having is that as...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
