«

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.CreateObject("ADODB.Connection")
objConn.Open strConnectionString
Set objRs = Server.CreateObject("ADODB.RecordSet")
objRs.CursorLocation = adUseClient
objRs.open "SELECT * FROM EMP UPDATE OF EMPNO", objConn,
adOpenForwardOnly, adLockBatchOptimistic
//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!!

Where are you getting this syntax from - 'update of', 'select for update'?

Bob Lehmann

"Steve" <> wrote in message
news: oups.com...
> 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.CreateObject("ADODB.Connection")
> objConn.Open strConnectionString
> Set objRs = Server.CreateObject("ADODB.RecordSet")
> objRs.CursorLocation = adUseClient
> objRs.open "SELECT * FROM EMP UPDATE OF EMPNO", objConn,
> adOpenForwardOnly, adLockBatchOptimistic
> //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!!
>

Steve wrote:
> ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement
>

Never ask a database-related question without revealing what database type
and version you are using.

I have never seen "FOR UPDATE" used except in SQL Server. In SQL Server, it
can only be used when declaring a T-SQL cursor. So, even in SQL Server, if
you use it anywhere else, you will get a syntax error.

I cannot speak for other databases like Oracle.

Why do you wish to (or think you need to) use this syntax?

Bob Barrows


--
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"

I am using oracle 9i database. I tried to execute the query in Oracle
and it works fine, then it should work fine if the ASP page executes
that query runs on Oracle?


Bob Barrows [MVP] wrote:
> Steve wrote:
> > ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement
> >
>
> Never ask a database-related question without revealing what database type
> and version you are using.
>
> I have never seen "FOR UPDATE" used except in SQL Server. In SQL Server, it
> can only be used when declaring a T-SQL cursor. So, even in SQL Server, if
> you use it anywhere else, you will get a syntax error.
>
> I cannot speak for other databases like Oracle.
>
> Why do you wish to (or think you need to) use this syntax?
>
> Bob Barrows
>
>
> --
> 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"

I don't know. I have no experience with Oracle.
What is the purpose of that syntax in Oracle? I.E., what is the goal you
are trying to accomplish that can't be accomplished with standard ANSI
SQL?

Bob Barrows
Steve wrote:
> I am using oracle 9i database. I tried to execute the query in Oracle
> and it works fine, then it should work fine if the ASP page executes
> that query runs on Oracle?
>
>
> Bob Barrows [MVP] wrote:
>> Steve wrote:
>>> ASP error number 13 - Type mismatch with SELECT...FOR UPDATE
>>> statement
>>>
>>
>> Never ask a database-related question without revealing what
>> database type and version you are using.
>>
>> I have never seen "FOR UPDATE" used except in SQL Server. In SQL
>> Server, it can only be used when declaring a T-SQL cursor. So, even
>> in SQL Server, if you use it anywhere else, you will get a syntax
>> error.
>>
>> I cannot speak for other databases like Oracle.
>>
>> Why do you wish to (or think you need to) use this syntax?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

I tried to put con.BeginTrans and it seems working. Is that all I
need??

objConn.BeginTrans
objRS.Open "SELECT * FROM EMP UPDATE OF EMPNO", objConn,
adOpenForwardOnly, adLockBatchOptimistic
con.CommitTrans


SEOSpecialist wrote:
> I am wondering the same thing.....
>
> It sounds to me like you may be wanting to carry out a secure
> transaction... in which case, ASP has the tools to do it.
>
> You'll need to use the proper transaction properties of the database
> objects....
> E.g....
> ....
>
> <%
> Set con = Server.CreateObject("ADODB.Connection")
>
> con.Open connectionString 'open connection
> con.BeginTrans 'Begin the transaction
>
> con.Execute("insert into YourTablename values ('testFieldData')")
> 'insert a record
>
> if YourTestCondition = True then
> con.CommitTrans
> else
> con.RollBackTrans
> end if
>
> Set Con = Nothing
> %>
>
> Hope this helps,
>
> Rob
> http://www.webforumz.com/asp-forum/ - ASP Forum

The interesting thing is that why we need to call objConn.BeginTrans if
we use UPDATE OF clause? For other regular SQL statement, we don't need
to call objConn.BeginTrans at all, and I never use this method before.
Is this ASP specific problem?

please advise. thanks again!!

Basically below is what I am doing, and the interesting observation is
that Oracle SELECT ... FOR UPDATE clause needs to use with
objConn.BeginTrans 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.RollbackTrans (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.BeginTrans
objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
adOpenForwardOnly, adLockBatchOptimistic
current_balance = objRS(0)
objConn.RollbackTrans



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.BeginTrans 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.RollbackTrans (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.BeginTrans
> objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
> adOpenForwardOnly, adLockBatchOptimistic
> current_balance = objRS(0)
> objConn.RollbackTrans
>
>
>
> 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.BeginTrans 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.RollbackTrans (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.BeginTrans
>> objRS.Open "SELECT * FROM EMP FOR UPDATE OF EMPNO", objConn,
>> adOpenForwardOnly, adLockBatchOptimistic
>> current_balance = objRS(0)
>> objConn.RollbackTrans
>>
>>
>>
>> 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"