ASP Type mismatch error with SELECT...FOR UPDATE statement

[Steve]

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

[Bob Lehmann]

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

Bob Lehmann

"Steve" <ja*****@gmail. com> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.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.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!!

[SEOSpecialist]

I presume you are using mssql and not msAccess ?

[Bob Barrows [MVP]]

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"

[Steve]

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"

[Bob Barrows [MVP]]

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.

[SEOSpecialist]

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.CreateOb ject("ADODB.Con nection")

con.Open connectionStrin g 'open connection
con.BeginTrans 'Begin the transaction

con.Execute("in sert into YourTablename values ('testFieldData ')")
'insert a record

if YourTestConditi on = True then
con.CommitTrans
else
con.RollBackTra ns
end if

Set Con = Nothing
%>

Hope this helps,

Rob
http://www.webforumz.com/asp-forum/ - ASP Forum

[Steve]

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

objConn.BeginTr ans
objRS.Open "SELECT * FROM EMP UPDATE OF EMPNO", objConn,
adOpenForwardOn ly, adLockBatchOpti mistic
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.CreateOb ject("ADODB.Con nection")

con.Open connectionStrin g 'open connection
con.BeginTrans 'Begin the transaction

con.Execute("in sert into YourTablename values ('testFieldData ')")
'insert a record

if YourTestConditi on = True then
con.CommitTrans
else
con.RollBackTra ns
end if

Set Con = Nothing
%>

Hope this helps,

Rob
http://www.webforumz.com/asp-forum/ - ASP Forum

[SEOSpecialist]

Yes, you need to tell SQL you are beginning a transaction.

[Steve]

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

please advise. thanks again!!