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

[sabo111]

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());
}

[VictorN]

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?

[DataMiser]

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.

[ComITSolutions]

Instead

String dbQuery = "SELECT alumni_Name FROM AlumniMembers WHERE alumni_Name
LIKE '*" + name + "*'";

use

String dbQuery = "SELECT alumni_Name FROM AlumniMembers WHERE alumni_Name
LIKE '%" + name + "%'";

[DataMiser]

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.

[ComITSolutions]

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

[hspc]

* is used when executing the query from Ms access or DAO. While % works with OLEDB.
Not sure about ODBC or JDBC.