How I Escape ' And % ..?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

this is my query and i m not able to escape special characters from the query .. i like to search from a textbox in gridview i need result as like"%somevalue%" my query is not coming right plz help ..

Select ProductName,Description,Image,CategoryName,IsActive from Product inner join Category on Product.CategoryId=Category.CategoryId where ProductName like'[%]"+SearchTextBox.Text+"[%]'

Posted 15-Jul-14 19:07pm

Member 10949397

Add a Solution

4 solutions

Solution 2

you should have to write string always in string.Format

SQL

 string query=string.Format("Select ProductName
,Description,Image
,CategoryName
,IsActive
from Product
inner join Category on Product.CategoryId=Category.CategoryId
where Product
Name like '%{0}%'",SearchTextBox.Text.Trim());

Posted 15-Jul-14 20:31pm

jo.him1988

Updated 15-Jul-14 20:33pm

v2

Comments

Member 10949397 16-Jul-14 2:47am

Thankz 4 for reply and suggestion but still it is not working

jo.him1988 16-Jul-14 3:07am

ok you have to replace your ' to '' because its throw exception

Solution 3

This will work,

declare @searchtext varchar(10)='in'
--declare @searchtext varchar(10)='%'
select * from holidaytable where name like CASE When @searchtext='%' Then '' Else '%'+@searchtext+'%' End

Posted 15-Jul-14 21:00pm

Rajesh Buddaraju

Solution 4

try this

string query=string.Format("Select ProductName
,Description,Image
,CategoryName
,IsActive
from Product
inner join Category on Product.CategoryId=Category.CategoryId
where Product
Name like '%{0}%'",SearchTextBox.Text.Replace("'","''"));

Posted 15-Jul-14 21:07pm

jo.him1988

Updated 15-Jul-14 21:09pm

v2

Comments

Member 10949397 16-Jul-14 3:41am

thanku sir ...i got it ..its working its escaping the special characters but my query like'%somevalue%' is not working properly ...thankzz once again

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prasad Avunoori · Accepted Answer · 2014-07-15T19:30:00

Solution 1

SQL

"Select ProductName
,Description,Image
,CategoryName
,IsActive
 
from Product
inner join Category on Product.CategoryId=Category.CategoryId
where ProductName like'%"+SearchTextBox.Text.Replace("%", "[%]")+"%'"

Posted 15-Jul-14 19:30pm

Prasad Avunoori

Updated 15-Jul-14 21:27pm

v2

Comments

Member 10949397 16-Jul-14 2:05am

Thanks sir for ur reply i tried this. and my query works perfectly fine . but when i enter % and ' in my search textbox no results should be displayed but whole gridview is getting displayed. i dont want any results when i type % and ' in textbox as there are no special characters added to my gridview nor database .What to do .?

Prasad Avunoori 16-Jul-14 3:28am

View my updated solution. It should work.

Member 10949397 16-Jul-14 3:49am

thankuu sir its working ....! but when i enter ' its not ... can u please help ..thankz ..!

Prasad Avunoori 16-Jul-14 4:48am

You are supposed to use one more Replace() method.

SearchTextBox.Text.Replace("%", "[%]").Replace("'","''")

Member 10949397 16-Jul-14 4:54am

Thankyou so mucchh ..!!!! my problem is solved now ..:)

Prasad Avunoori 17-Jul-14 0:01am

You are most welcome!

Member 10949397 16-Jul-14 7:50am

"Select ProductName
,Description,Image
,CategoryName
,IsActive

from Product
inner join Category on Product.CategoryId=Category.CategoryId
where ProductName like'%"+SearchTextBox.Text.Replace("%", "[%]").Replace("'","''")+"%'"