[atsukoarai86]

Hello Everyone,

I am connecting to a MS Access database using the DriverManager class and JDBC-ODBC Bridge driver. I am having trouble with a query using the "LIKE" clause in MS Access. When I run the query in Access, it returns results as it ought to, but when I attempt to use this string of SQL in a prepared statement, it returns no records.

Here is my code:

if(getRecordCount(rs)==0){
            System.out.println("No records restrieved for :\n" + strSQL + "\nReassigning query expression");
            strSQL = "SELECT * FROM tblNAmes WHERE " + c + " LIKE '*"+ crit + "*' ORDER BY " + c + ";";
            System.out.println("New Statement: " + strSQL);
            System.out.println("Assigning result set");
            ps = conn.prepareStatement(strSQL, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = ps.executeQuery();
        }
        int count = getRecordCount(rs);
        System.out.println("Records Retrieved: " + count);

Here is the output I receive:

Quote:

Clicked Search.
Parsing search criteria
Column name: strFirstName
Criteria: a
Passing column and criteria to private search method
Assigning SQL Statement: SELECT * FROM tblNames WHERE strFirstName = 'a' ORDER BY strFirstName;
Preparing statement...
Assigned ResultSet
Records retrieved: 0
No records restrieved for :
SELECT * FROM tblNames WHERE strFirstName = 'a' ORDER BY strFirstName;
Reassigning query expression
New Statement: SELECT * FROM tblNAmes WHERE strFirstName LIKE '*a*' ORDER BY strFirstName;
Assigning result set
Records Retrieved: 0
Columns in Result Set: 7
Preparing to loop
Moved to beginning of result set; Current record: 0
BUILD SUCCESSFUL (total time: 54 seconds)

All of my other query expression (not using "LIKE") work perfectly fine. Does anyone see any glaring errors in this code? I have only recently become familiarizing myself with these tools, so any assistance would be greatly appreciated.

Thank you in advance!

[atsukoarai86]

fifteen people have looked at this thread and NO ONE has any idea what might be a solution?

Back to the old drawing board.

[OpenLink]

The most likely problem is your LIKE wildcard.

Instead of

SELECT * FROM tblNAmes WHERE strFirstName LIKE '*a*' ORDER BY strFirstName;

try

SELECT * FROM tblNAmes WHERE strFirstName LIKE '%a%' ORDER BY strFirstName;

.

Hope that helps,

Ted
OpenLink Software
Technical Support