?
Solved

Dynamic SQL  in Oracle Stored Procedure thro' ASP

Posted on 1999-11-22
5
Medium Priority
?
260 Views
Last Modified: 2012-06-27
suppose that i have a stored procedure that takes in an SQL command as input parameter and executes that statement.
We need to call that procedure through ASP.

The problem arises when we pass a SQL command, with a quoted string such as 'select * from temp where c1 = 'gh ' '
as

" ADODB.Command error '800a0d5d'
The application is using a value of the wrong type for the current operation. "

How could this be solved...

Thanks and Any help in this regards is appreciated.
0
Comment
Question by:rkruba
  • 3
5 Comments
 
LVL 8

Accepted Solution

by:
xabi earned 800 total points
ID: 2227573
Use SQLEncode() function.
Ie:

Dim sString
sString = "'hi there" & """" ' Anything
strSQL = "SELECT * FROM foo WHERE text=" & SQLEncode(sString)
..
..

This should work

Xabi
0
 

Author Comment

by:rkruba
ID: 2227648
This gives a way out..  But still VBSCript gives Error on
SQLEncode

Microsoft VBScript runtime error '800a000d'
Type mismatch: 'SQLEncode'

What to do now?



Thanks in ADvance
rkruba
0
 
LVL 8

Expert Comment

by:xabi
ID: 2227883
Hi there again:

This is part of my code, and it works:

function SQLEncode(strSqlTmp)
 SQLEncode = replace(strSqlTmp,"'","'+CHAR(39)+'")
end function

strExample = "john " & chr(34) & "leftie's" & chr(34)
strSql = "INSERT INTO data (id) VALUES ('" & SQLEncode(strExample) & "')"
objConn.execute strSql
 .
 .

Xabi
0
 
LVL 8

Expert Comment

by:xabi
ID: 2227889
I will splain you a few about this:

String values must be betwen ' (sigle quotes).
Ie: 'john'

If you want to inser a double quote you can.
Ie: 'John "the fly"'

If you want to add a sigle quote you must insert de SQL funtion CHAR(intAsc)
ie: 'John "leftie' + CHAR(39) + 's"'

Example of a complete query:

INSERT INTO data (id) VALUES ('john "leftie'+CHAR(39)+'s"')

Hope it helps
Xabi
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6833446
This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed.  Also a question is posted there specific to these changes that apply to the experts here.  Also, I am including the link to our All Topics, since many new ones have recently been added.

http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
http://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thank you,
Moondancer
Moderator @ Experts Exchange
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
There is a wide range of advantages associated with the use of ASP.NET. This is why this programming framework is used to create excellent enterprise-class websites, technologies, and web applications.
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
The video provides a quick and easy steps to migrate MBOX file to well known Outlook PST and Office 365. Besides this, it also supports and migrates more than 20 email clients of MBOX which include AppleMail, Opera, Thunderbird and SeaMonkey effortl…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question