proplem with LIKE in Oracle Parameter

Hi guys

I am trying to fill my dataset by using OracleDataAdapter and when I use the below code it is working fine:

                cn.ConnectionString = ConfigurationSettings.AppSettings["ConnectionString"];
                OracleDataAdapter da = new OracleDataAdapter("Select * from FAC_1 Where   FACName = :1", cn);
                OracleParameter pa = new OracleParameter();
                pa.OracleDbType = OracleDbType.Varchar2;
                    pa.Value = txtFactoryName.Text;
                DataSet ds = new DataSet();

but when I change the statement to  
"Select * from FAC_1 Where            FACName  LIKE % :1%"

I got an Error

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shinesh PremrajanEngineering ManagerCommented:
Is should  be something like, you missed the single quotes around the %.

  LIKE '% :1%' "

Hope this clarifies..
Om PrakashCommented:
"Select * from FAC_1 Where FACName  LIKE '%1%'"
Reza RadConsultant, TrainerCommented:
>>I got an Error
what Error ?
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

try like this

OracleDataAdapter da = new OracleDataAdapter("Select * from FAC_1 Where   FACName like '%' + :1 + '%' ", cn);
slightwv (䄆 Netminder) Commented:
I believe the correct answer is the first post (http:#29172768).

I just wanted to point out you have a space after the first % and the bind variable.  That can mess with the results.
ABUZYEDAuthor Commented:
All the solutions are not correct :(

I didn't receive any error but I didn't get any return data.
Om PrakashCommented:
Does your data contains "1" in FACName column?

Select * from FAC_1 Where FACName  LIKE '%1%';
This will return values if FACName contails 1.

Vadim RappCommented:
Try this:

new OracleDataAdapter("Select * from FAC_1 Where   FACName = '" + txtFactoryName.Text + "'", cn);

and throw away the parameter-related code.

Another way: remove the space from LIKE % :1%, make it

LIKE %:1%
slightwv (䄆 Netminder) Commented:
>>I didn't receive any error but I didn't get any return data.

What is the value of the parameter and what is the data in the table you expect back?

It might be a case problem:

"Select * from FAC_1 Where   lower(FACName)  LIKE lower('%:1%')"
ABUZYEDAuthor Commented:

I found the solution finally .The code should be like below:

 "Select * from FAC_1 Where   FACName  LIKE  :1"

 pa.Value ="%" +  txtFactoryName.Text + "%" ;

I used the below link for fixing this issue:

Thanks for your comments

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.