dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7

Thread: Unable to retrieve data from database when use the Like operator in sql statement

  1. #1
    Join Date
    Aug 2011
    Posts
    3

    Unable to retrieve data from database when use the Like operator in sql statement

    Hi,I am having difficulties in retrieving data from database when i am using the LIKE clause in my sql statement.What I actually want is when the user enters a name in the search function,names that are similar to the user input will be retrieve out from the database.But unfortunately,I keep retrieving null.My code looks some thing like this.
    I am using microsoft access for my database.

    public class MemberDetails{

    String name;

    public MemberDetails(String name){
    this.name = name;
    }

    public ArrayList<String> getMemberSearchResults() {
    ArrayList<String> hi = new ArrayList<String>();
    ResultSet rs = null;
    DBController db = new DBController();
    db.setUp("IT2297_Project");
    String dbQuery = "SELECT alumni_Name FROM AlumniMembers WHERE alumni_Name
    LIKE '*" + name + "*'";
    dbQuery += " Order by alumni_Name";
    rs = db.readRequest(dbQuery);
    try {
    while (rs.next()) {
    name = rs.getString("alumni_Name");
    hi.add(name);
    }
    } catch (Exception e) {
    e.printStackTrace();
    }
    System.out.println(dbQuery);
    db.terminate();
    return hi;
    }

    public static void main(String args[]) {
    MemberDetails md = new MemberDetails("p");
    System.out.println(md.getMemberSearchResults());
    }

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,562

    Re: Unable to retrieve data from database when use the Like operator in sql statement

    Run your code in Debugger, copy the resulting query string and test it in MS Access with your currently used DB. What is the result of query?
    And does the query look like?
    Victor Nijegorodov

  3. #3
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,330

    Re: Unable to retrieve data from database when use the Like operator in sql statement

    Looking at the query string I would expect it to work assuming that name contains a valid string that appears in the data and that all the object names are correct.

    As suggested copy the query into access and test it or add a msgbox to display the query string so you can see the actual string being passed at runtime.
    Always use [code][/code] tags when posting code.

  4. #4
    Join Date
    Feb 2008
    Location
    Bangalore
    Posts
    149

    Re: Unable to retrieve data from database when use the Like operator in sql statement

    Instead
    Code:
    String dbQuery = "SELECT alumni_Name FROM AlumniMembers WHERE alumni_Name
    LIKE '*" + name + "*'";
    use

    Code:
    String dbQuery = "SELECT alumni_Name FROM AlumniMembers WHERE alumni_Name
    LIKE '%" + name + "%'";
    Encourage the efforts of fellow members by rating

    Lets not Spoon Feed and create pool of lazy programmers

    - ComIT Solutions

  5. #5
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,330

    Re: Unable to retrieve data from database when use the Like operator in sql statement

    Like '*A*' is correct for Access

    Like '&#37;A%' does not work in Access, or at least it does not work in Access 97 2000 or 2002 have not tested with newer versions but would expect them to be the same.
    Always use [code][/code] tags when posting code.

  6. #6
    Join Date
    Feb 2008
    Location
    Bangalore
    Posts
    149

    Re: Unable to retrieve data from database when use the Like operator in sql statement

    Quote Originally Posted by DataMiser View Post
    Like '*A*' is correct for Access

    Like '%A%' does not work in Access, or at least it does not work in Access 97 2000 or 2002 have not tested with newer versions but would expect them to be the same.
    I didn't know that! Thanks
    Encourage the efforts of fellow members by rating

    Lets not Spoon Feed and create pool of lazy programmers

    - ComIT Solutions

  7. #7
    Join Date
    Apr 2002
    Location
    Egypt
    Posts
    2,210

    Re: Unable to retrieve data from database when use the Like operator in sql statement

    * is used when executing the query from Ms access or DAO. While % works with OLEDB.
    Not sure about ODBC or JDBC.
    Hesham A. Amin
    My blog , Articles


    <a rel=https://twitter.com/HeshamAmin" border="0" /> @HeshamAmin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)