Friday, November 28, 2008

Dealing with apostrophe (‘) problem in asp.net, sql server, oracle database query

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;
if xtSearchInput.Text.Contains("'"))
{
searchText = txtSearchInput.Text.Replace("'", "\''");
}
Happy Programming! Happy dot-netting!!

kick it on DotNetKicks.com

6 comments:

Anonymous said...

.Replace("'", "\''")
This saved my day...cheers!!

May God ride with you.

=DivineBliss

Kishore R said...

Helpful Post,Thanks a lot

Anonymous said...

thanks it worked.

Wakil said...

good one.

Juan Mandagit said...

thank you so much!

God bless you.

Anonymous said...

Thanks alot.

I got this link after 2 days. But finally happy :-)

Sunil
http://hostnow.co.in

Post a Comment

Hope you liked this post. You can leave your message or you can put your valuable suggestions on this post here. Thanks for the sharing and cooperation!

Popular Posts

Recent Articles