• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

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"];
                cn.Open();
                OracleDataAdapter da = new OracleDataAdapter("Select * from FAC_1 Where   FACName = :1", cn);
                OracleParameter pa = new OracleParameter();
                pa.OracleDbType = OracleDbType.Varchar2;
             
                    pa.Value = txtFactoryName.Text;
                da.SelectCommand.Parameters.Add(pa);
                DataSet ds = new DataSet();
                da.Fill(ds);

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

I got an Error


Thanks
0
ABUZYED
Asked:
ABUZYED
  • 2
  • 2
  • 2
  • +4
1 Solution
 
Shinesh PremrajanTechnical ManagerCommented:
Is should  be something like, you missed the single quotes around the %.

  LIKE '% :1%' "

Hope this clarifies..
0
 
Om PrakashCommented:
"Select * from FAC_1 Where FACName  LIKE '%1%'"
0
 
Reza RadCommented:
>>I got an Error
what Error ?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
appariCommented:
try like this

OracleDataAdapter da = new OracleDataAdapter("Select * from FAC_1 Where   FACName like '%' + :1 + '%' ", cn);
0
 
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.
0
 
ABUZYEDAuthor Commented:
All the solutions are not correct :(

I didn't receive any error but I didn't get any return data.
0
 
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.


0
 
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%
0
 
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%')"
0
 
ABUZYEDAuthor Commented:
Guys

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:
http://support.microsoft.com/kb/307514

Thanks for your comments
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now