The use of apostrophe (') character as an input text in an sql query troubles the query operation. Say, you have a textbox in which user can input her search text and she inputs "john's" as her search text. Now your employee query will look like:
SELECT *FROM [Employee] WHERE Employee].Employee_Name LIKE 'john's' AND [Your other WHERE conditions]
See the problem? You are right. The database engine treats the query segment:
SELECT * FROM [Employee] WHERE [Employee].Employee_Name LIKE john
And the remaining portion will unfortunately contribute to syntax error.It is the problem and I tried my best to elaborate it. Then solution? Yeah, there exists simple but tricky one! Just replace the apostrophe (') character in the search text with double apostrophe ('') character and you are done. Let me put it simply.
string searchText = txtSearchInput.Text;
searchText = txtSearchInput.Text.Replace("'", "\''");
Happy Programming! Happy dot-netting!!