Search for ' as in a name like "O'Neil"

[Ogi]

Hi,

Not sure if this is in the correct section but as my issue is in VB6, thought I'd ask here!

Just wondering how to get round a name search when you store in the database "O'Neil" and want to do a search?

Obviously an Sql statement can't read:-

SELECT * FROM Customers WHERE Name Like 'O'Neil'

So what's the best way round it?

Cheers in advance,
Carl.

 

[MakeItSo]

You need to double the single quote:

SELECT * FROM Customers WHERE Name Like 'O''Neil'

;-)

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[dilettante]

Or better yet use a parameterized query. This is good protection against inadvertant SQL injection attacks too.

 

[TysonLPrice]

Without a wild card % it might as well be:

SELECT * FROM Customers WHERE Name = 'O''Neil'

 

[harebrain]

And because it's likely that you're not hard-coding 'O''Neil' in your program, you probably want a function like this to do the job:

Private Function SqlQuoteString(ByVal strValue As String)
    SqlQuoteString = Chr$(39) & Replace(strValue, Chr$(39), String$(2, 39), 1, , vbTextCompare) & Chr$(39)
End Function

Use it like this:

SELECT * FROM Customers WHERE Name = SqlQuoteString(strLastName)