Single Quote problem when connecting to Oracle db

[flip79]

Hi,

I would greatly appreciate it if anyone could help me figure out how to pass a SQL string to an Oracle database if the where criteria has a single quote in it somewhere.

For Example:

Select * From tblTitle Where Title = "A Book's Title"

I tried using this statement below where me.txtTitle = "A Book's Title":

strSQL = "Select * from tblTitle Where Title = " & CHR$(34) & me.txtTitle & CHR$(34)

but this didn't work.

I know in Oracle you must "SET SCAN OFF" and put another single quote in front of the existing quote, but I'm not sure how to do this in VB.

Help Please!!

 

[zemp]

Assuming that you are using ADO, take a look at faq709-1526. You will want to use something similar to a stored procedusr and it is not Oracle specific but the same principles apply.

Thanks and Good Luck!

zemp

 

[flip79]

I can't get to your link. Can you post the entire address?

Thanks for your help!!!

 

[zemp]

It worked for me! Anyway here is the page, forum in forum 709,

http://www.tek-tips.com/faqs.cfm?spid=709&sfid=1526&newpid=709

Thanks and Good Luck!

zemp

 

[chiph]

flip79 -

Even though my FAQ is for SQL Server, all the same concepts still apply when using Oracle. You'll want to follow some of the links in the FAQ and read some of the technical articles (Zemp provided most of the links, and they're excellent).

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first