Stripping apostrophes from SQL or strings 2

[Dusty1]

Further to my previous post concerning apostrophes, I've discovered that it's not the VBScript thats catching the apostrophes but the SQL which inserts a text string into a table. When the text string contains an apostrophe, the SQL takes the apostrophe as the end of a value ie

INSERT INTO mytable (mycolumn) VALUES ('mytext's')

which obviously causes problems. "mytext's" comes straight from a textbox, but does anyone know how to strip out a character from a textstring bearing in mind an apostrophe in VBScript would be taken as a comment? that way i could intercept the value, take out the apostrophe and pass it to the SQL. Or do I have to strip out the apostrophe clientside using javascript or something - and how would i do this? Any help at all would be great, I'm hoping there is a really simple way that i don't know.
Thanks
Dusty

 

[griffinmt]

Actually, your issue is not with an apostrophe, it is with the single quote character. In fact, what you should be using all the time, except for quoted strings, is the apostrophe (lower case tilde on most keyboards).

Try something like:
sText = Replace(sText,"'","`&quot
sText = Replace(sText,"""","`&quot

 

[Dusty1]

Thats great, worked like a dream, thanks a lot you've really helped me out.
Dusty

 

[EagleWeb]

Thanks, Dusty, for taking the time to help. I have been trying to figure out how to keep a submitted name input field from reading the apostrophe as a comment (e.g. "O'Conner" turns into "O" when read!)... and your script answered my question, too, and fixed the bug. Thanks again! EagleWeb

 

[chdavisjr]

Hello, all.
I know this is late, but I hope someone sees and replies!
I've always used the
sText=Replace(sText,"'","''") to create sql to access my MS Access databases online.
However, I am doing something new - searching - and I have a problem.
my SQL looks like

sql="SELECT PID, Term, Definition from glossary WHERE "
sql= sql & "(Term like '%" & sText & "'% OR "
sql= sql & "Definition like '%" & sText & "'%);"

Now if the user enters farmer's
the sql tells me it could not find farmer''s.
I even tried replacing ' with ` and this doesn't work.
I thought ths should work ... any ideas why it doesn't?
Thanks in advance,
Chalmers

 

[chdavisjr]

Duh!! My Bad!!!
I just got the brilliant idea to check my databse, and found that I had earlier replaced all the apostrophes with '
So when I was searching for the ' ... guess what! It didn't find it!
Now I replace ' with ' and it works!
Chalmers