locked
problem in parameterized query RRS feed

  • 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

    Tuesday, November 27, 2007 10:04 AM

All replies

  • 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.

    Tuesday, November 27, 2007 10:44 AM
  • 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 

     

    Tuesday, November 27, 2007 11:58 AM
  • 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.

    Tuesday, November 27, 2007 12:20 PM
  • 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

    Tuesday, November 27, 2007 10:04 PM
  • 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.

    Wednesday, November 28, 2007 12:32 PM
  • User-1832347266 posted

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

    thanks 

     

    Thursday, November 29, 2007 10:59 AM
  • 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

    Saturday, December 1, 2007 10:15 AM
  • 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

    Tuesday, December 4, 2007 9:44 PM
  • 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);

     

    Thursday, December 6, 2007 3:18 PM
  • 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)?

     

    Thursday, December 6, 2007 3:48 PM
  • User519246680 posted

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

    Friday, December 7, 2007 3:35 PM
  • 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. 

     

    Saturday, December 8, 2007 11:59 PM
  • 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

    Sunday, November 13, 2011 10:27 AM