Solved

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

Posted on 2007-03-28
5
487 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
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

856 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