The problem is simple: you are concatenating strings to form an SQL expression, and that's a very, very bad idea. Not only does it give problems like you ar eseeing, but it hands control of your DB to your user, to do with as he sees fit. And not juist with this command either!
Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.
When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.
So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
And remember that a LIKE query requires wild card characters to work: if you want to find "To 'Reto" inside a string column, you need to pass it as a parameter and add a '%' wild card to the beginning and end:
Using con As New SqlConnection(strConnect)
con.Open()
Using cmd As New SqlCommand("SELECT * FROM myTable WHERE MyColumn LIKE '%' + @SEARCH + '%'", con)
cmd.Parameters.AddWithValue("@SEARCH", MyTextBox.Text)
Using reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
...
End While
End Using
End Using
End Using