problem in parameterized query

Question

User-1832347266 posted

Hi,

I have the following code and trying to bind to datagrid.Though data is present in the database, dataset is returning 0 rows.here is the code

 

OracleConnection oc= null;

StringBuilder sb= new StringBuilder();

DataSet ds = new DataSet();

 

oc= new OracleConnection("connectionstring");

sb.Append("select  field1 from test1 where ");

sb.Append("upper(field2)=:stField1 ");

sb.Append(" and upper(field2) LIKE :stField2 || '%'");

OracleParameter op1 = new OracleParameter();

op1.OracleDbType = OracleDbType.Varchar2;

op1.ParameterName = "stField1";

op1.Value = "abc";

OracleParameter op2 = new OracleParameter();op2.OracleDbType = OracleDbType.Varchar2;op2.ParameterName = "stField2";

op2.Value = "AA";

OracleCommand Command = new OracleCommand(sb.ToString(),oc);

Command.Parameters.Add(op1);

Command.Parameters.Add(op2);

oc.Open();

OracleDataAdapter Adapter = new OracleDataAdapter(Command);

Adapter.Fill(ds);

Dataset is not filled,though data exists in the database

Any problem with the sql query above.

thanks

Answer 1

User519246680 posted

As debug information, write out Command.CommandText.  It should be a valid PL/SQL statement.  Take this statement and run it in Oracle and see if you get data back.  If not, we know it's the query.  If so, we know it's something else.  Post what is contained in the Command.CommandText and what you find out about whether it returns data or not.

Answer 2

User-1832347266 posted

this is the statement when I observed in quickwatch

select field1 from test1 where upper(field2)=:stField1  and upper(field2) LIKE :stField2 || '%'.

As it contains bind variables,I think dynamically it executes.

any solution.

thanks in advance 

 

Answer 3

User519246680 posted

Okay, here's my next theory.

First, make sure your connection is closed.  It's not related, but it might cause problems later on.

Second, your query equates to:  select field1 from test1 where upper(field2) = 'abc' and upper(field2) LIKE 'AA' || '%'

If you are using the UPPER() function, the value 'abc' will never be true, so you will never get any results.  Also, the second criterium will only be true if the field2 starts with 'AA'.  If this statement were to ever equate to true, field2 would have to be equal to both 'abc' and 'AA%', which I believe is impossible. Both criteria are looking at field2.  Perhaps the first criterium should evaluate field1?

I don't know if these were intentional or not, but I suggest you look at them.

Answer 4

User-1832347266 posted

I have changed first criteria to

upper(field1) = 'ABC' .

Second one is the problem I am getting,How do I manipulate that query from .net to make it as 'AA%'  ,while executed in oracle

thanks

Answer 5

User519246680 posted

Syntactically, your query was/is correct.  This would be your current query (after dynamically populating parameters):

select field1 from test1 where upper(field1) = 'ABC' and upper(field2) LIKE 'AA' || '%'

This would return field1 where field1 = 'ABC' and field2 is like 'AA%'.  As a test, I ran a simple query just to make sure the concatenation wasn't messing anything up.  My query was:

select * from tab where tname like 'ACU' || '%'

This returned the one table I was expecting as output.  This leads me to believe there is nothing wrong with your query.  If you are not getting any results now, it is likely because no data matches the criteria.

Answer 6

User-1832347266 posted

Data is existing in the database,but still not retrieving.Can anybody help out?

thanks 

 

Answer 7

User-611445092 posted

I don't see any syntax issue in your query:

select field1 from test1 where upper(field2) = :stField1 and upper(field2) LIKE :stField2 || '%'

Can you please change your query withouy appending the "%" and pass the parameter after appending the '%' to it as follows:

                           op2.Value = "AA%";

And you looks as : select field1 from test1 where upper(field2) = :stField1 and upper(field2) LIKE :stField2

Answer 8

User-1832347266 posted

tried this option also previously,but same problem.If I use it in the query directly then it is working

select field from test1 where upper(field2) = :stField1 and upper(field3) like 'AA%'.

If I substitute the variable as below then it is not working.

op1.value = "AA%";

thanks

Answer 9

User519246680 posted

Try this and tell me if you get any results:

 

OracleConnection oc= null; 
StringBuilder sb= new StringBuilder();

DataSet ds = new DataSet();

oc= new OracleConnection("connectionstring"); 
sb.Append("select  field1 from test1 where ");

sb.Append("upper(field2)=:stField1 "); 
//sb.Append(" and upper(field2) LIKE :stField2 || '%'");
sb.Append(" and upper(field2) LIKE :stField2");  //  changed this line

OracleParameter op1 = new OracleParameter(); 
op1.OracleDbType = OracleDbType.Varchar2;

op1.ParameterName = "stField1"; 
op1.Value = "abc"; 


OracleParameter op2 = new OracleParameter();op2.OracleDbType = OracleDbType.Varchar2;op2.ParameterName = "stField2"; 
//op2.Value = "AA";
op2.Value = "AA%";  //  changed this line also

OracleCommand Command = new OracleCommand(sb.ToString(),oc);

Command.Parameters.Add(op1);
Command.Parameters.Add(op2);

oc.Open();

OracleDataAdapter Adapter = new OracleDataAdapter(Command); 
Adapter.Fill(ds);

 

Answer 10

User1187105292 posted

Do you really want the value in the field2 column to be BOTH = to the first parameter and like the second parameter?

Or is that a typo (and possibly the problem)?

 

Answer 11

User519246680 posted

That was a typo on my part.  Second should be 'field3'.

Answer 12

User1187105292 posted

If you go into sqlplus (or toad or sqlnavigator, whatever oracle programming tool you use) and type in this query, how many records get returned?

select field1 from test1 where upper(field1) = 'ABC' and upper(field2) LIKE 'AA' || '%'

If you get none, no data matches your criteria.

If you get some, then something is wrong in the .net code. 

 

Answer 13

User976879263 posted

Did you get the answer for your question?

If yes. then please tell me the solution.

I am also stuck with the same problem

 

Thanks in advance,

Ankit