Solved

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

Posted on 2007-03-28
5
499 Views
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:

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?
0
Comment
Question by:Eamon
[X]
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
5 Comments
 
LVL 9

Expert Comment

by:TheSloath
ID: 18806679
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
 
LVL 43

Accepted Solution

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

Author Comment

by:Eamon
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

Thanks..
0
 
LVL 43

Expert Comment

by:TimCottee
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/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
 
LVL 1

Author Comment

by:Eamon
ID: 18807089
Thanks for your help Tim.

Cheers.
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

636 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