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());
}
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?
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.
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 + "%'";
Re: Unable to retrieve data from database when use the Like operator in sql statement
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.
Re: Unable to retrieve data from database when use the Like operator in sql statement
Quote:
Originally Posted by
DataMiser
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
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.