Solved

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

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

770 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