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

Posted on 2007-03-28
Last Modified: 2008-02-01
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:

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?
Question by:Eamon
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2

Expert Comment

ID: 18806679
Use double quote marks:

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...

LVL 43

Accepted Solution

TimCottee earned 500 total points
ID: 18806694
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.

Author Comment

ID: 18806779
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

LVL 43

Expert Comment

ID: 18806957
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/ """" 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.

Author Comment

ID: 18807089
Thanks for your help Tim.


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to set focus on a dynamic control 18 52
T-SQL: problem comparing datetime 4 81
Import a excel sheet in a grid 2 45
MessageBox Appear behind a form in Runtime 1 32
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question