This is a famous problem faced by developers which connecting to MS Access database from c# code using OLEDB. The problem is when using LIKE to filter records. E.g. when you try EmpName LIKE ‘*John*’, the C# dataset will not get filled with any record. While if you run your whole query with that LIKE statement directly inside MS Access, it will return the desire result, i.e. LIKE will work as expected. This is confusing because the wild card operator in MS Access is ‘*’ as documented on MSDN.
There are two possible causes:
- The SQL written using C# and .net objects doesn’t understand ‘*’ characters
- The connection string that you use may not be appropriate and may not allow this kind of wild characters
The first cause is most likely while some people have also found the second cause to be the problem.
The solution is very simple – as long as you know what it is :). To solve this issue, just replace the ‘*’ by ‘%’ and it should work.
Also make sure the connection string you use looks like this: Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\[db]. mdb
where [db] is your actual file name