Eamon
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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..
Thanks..
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(OriginalStr ing,Replac ementStrin g) 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.
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(OriginalStr
ASKER
Thanks for your help Tim.
Cheers.
Cheers.
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...