Special Characters on SQL-statement

[Sephozzy]

I have the following:


Dim SQL as string
...
SQL = "SELECT * FROM COMPANY WHERE NAME = '" & txtName.text &"'"
...
rstCompany = conOracle.OpenRecordset(SQL, dbOpenForwardOnly)




Works fine for my purpose until I encounter name with the '-character, for example d'Or. My ODBC call then fails because it assumes that I'm only looking for NAME = 'd' and that the Or' at the end is another part of the SQL statement that cannot be parsed.

Any solution or workaround GREATLY appreciated (and rated ;-)

Thanks

<i>If anything in this post makes sense it was written by me, if not I don't know who wrote it</i>

[Andrew_Fryer]

Use two single qoutes instead of one, i.e

d''or

That should fix it!

Andrew

[Sephozzy]

Sounds tempting but I still have one problem, the text isn't always coming from a textbox (I only use it to test). The real names come from MS Access and dBase and there are already '-characters in there. Since there are a couple hundred of these external db's I'm not really planning to change them manually.
The most functional solution would be a procedure that can replace the ' with something else (or just remove it). Normally I program for VC++ and I don't know of any stnadard VB function to replace characters in a String.

If anything in this post makes sense it was written by me, if not I don't know who wrote it

[obiwan444]

... and this will make the replacement for you:

SQL = Replace (SQL,"'","''",1,-1,vbTextCompare)

[Andrew_Fryer]

try this



dim counter as integer

for counter = len(myStr) to 1 step -1
if mid (myStr, counter, 1) = "'") then
mid(myStr, counter, 1) = ""
end if
next counter





This will remove them from the string

[hoa01206]

Hi,

SQL = "SELECT * FROM COMPANY WHERE NAME = '" & chr(39) & txtName.text & chr(39) &"'"

Try this one. I had a problem like this before and when I put my variable within chr(39) which is a single qoute it worked.

Thanks
Hisham

[hoa01206]

Access DB when it has names that contain ' in them tends not to find them if you search for them. Unless, you put the entire name withing single qoutes.

Example:

Job'todo

if you search for it it won't find it.

Solution:

'Job'todo'

This will work. In VB to to put the single qoutes use chr(39).
The solution for your SQL is

txtName.text replace with
chr(39) & txtName.text & chr(39)

chr(39) ---------> '

Thanks
Hisham

[hoa01206]

By the way, having single quotes around a variable won't affect it if it does not have a single qoute within it.

'cool' you still can search it with r without the single qoutes. So, having single qoutes won't affect your other work

Thanks
Hisham