Single Quotes and SQL

[Guest]

I have a function that replaces all my single quotes going into the DB with two single quotes. So "O'Hare" becomes "O''Hare".
I have a dynamic sql statement for searching for customers. When they type in "O'Hare" I do the same command and turn it into "O''Hare" and use that in the where clause. The problem is it never returns any records. I have to change it to "O''''Hare" (or put 4 single quotes for each single quote that was originally specified) and then it returns the record. Is this the correct way to do this or is there something I'm missing?

 

[William \(Bill\) Vaughn]

It's for these reasons that we recommend that you use a Command object to
handle this issue. By building and INSERT Command and passing in the string
(without the double single quotes) as an input parameter, ADO handles the
issue automatically.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

I have a function that replaces all my single quotes going into the DB

with two single quotes. So "O'Hare" becomes "O''Hare".

I have a dynamic sql statement for searching for customers. When they type

in "O'Hare" I do the same command and turn it into "O''Hare" and use that in
the where clause. The problem is it never returns any records. I have to
change it to "O''''Hare" (or put 4 single quotes for each single quote that
was originally specified) and then it returns the record. Is this the
correct way to do this or is there something I'm missing?

 

[William Ryan eMVP]

Bill V summed it up perfectly in his response below. If you don't use
params is going to come up again - someone is going to forget to escape the
stuff and that's just one of the many problems
http://www.knowdotnet.com/articles/dynamisql.html

--

W.G. Ryan, eMVP

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups

I have a function that replaces all my single quotes going into the DB

with two single quotes. So "O'Hare" becomes "O''Hare".

I have a dynamic sql statement for searching for customers. When they type

in "O'Hare" I do the same command and turn it into "O''Hare" and use that in
the where clause. The problem is it never returns any records. I have to
change it to "O''''Hare" (or put 4 single quotes for each single quote that
was originally specified) and then it returns the record. Is this the
correct way to do this or is there something I'm missing?

 

[Guest]

Thank you, I have changed my code to use such.

It's for these reasons that we recommend that you use a Command object to
handle this issue. By building and INSERT Command and passing in the string
(without the double single quotes) as an input parameter, ADO handles the
issue automatically.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


with two single quotes. So "O'Hare" becomes "O''Hare".
in "O'Hare" I do the same command and turn it into "O''Hare" and use that in
the where clause. The problem is it never returns any records. I have to
change it to "O''''Hare" (or put 4 single quotes for each single quote that
was originally specified) and then it returns the record. Is this the
correct way to do this or is there something I'm missing?