Where clause that is looking at an Apostrophe in a surname.

How would i solve a problem with a SELECT query that includes a where clause = to a surname where the surname is O'Dowd..My query is:

SELECT *
FROM TempAuct
WHERE (SellerNo = '" & sellerNo & "')
ORDER BY tempAuct.LotNo

It throws up the error,
Incorrect syntax near 'DOWD'.
Unclosed quotation mark after the character string ') ORDER BY TempAUCT.LotNo'.

I know that it is the Apostrophe in the O'Dowd is causing the problem?
LVL 1
EamonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TheSloathCommented:
Use double quote marks:

SELECT *
FROM TempAuct
WHERE (SellerNo = " & Chr$(34) & sellerNo & Chr$(34) & ")
ORDER BY tempAuct.LotNo


...obviously if you ever get a surname O"Dowd you're up the creek, but...

0
TimCotteeHead of Software ServicesCommented:
SELECT *
FROM TempAuct
WHERE (SellerNo = '" & sellerNo.Replace("'","''").Replace("""","""""") & "')
ORDER BY tempAuct.LotNo

This doubles the ' and also the " character just in case. This will allow it to be interpreted by sql as a single ' or " without causing the error. If ' or " are not present in the string of course nothing will be changed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EamonAuthor Commented:
Thats seems to do the trick Tim..Im trying to understand the code though..would you be able to walk through an example lets say, O'Dowd

Thanks..
0
TimCotteeHead of Software ServicesCommented:
Ok, with sql, a simple insert statement like this:

Insert Into myTable(MyField) Values('O'Dowd')

Would cause an error because the ' between O and D would be treated as the end of the statement, subsequent characters would then fail parsing.

Insert Into MyTable(MyField) Values ('O''Dowd')

Allows sql to complete the parsing of the statement but it will insert only a single ' between O and D instead of the two that are shown in the statement. The same logic applies to a " character.

All I have done here is to use String.Replace(OriginalString,ReplacementString) to replace all occurrences of ' with ''. The complicated looking bit with " is actually because you have to do the same in vb/vb.net """" is actually treated as a string containing a single " character (the doubling here makes this happen in the same way as it does in SQL) so we are replacing all instances of " with "" as well as for the single quote character.
0
EamonAuthor Commented:
Thanks for your help Tim.

Cheers.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.