Found very strange behavior of MS Access database when running queries from c#. It appears that if query in MS Access contains "like" statement it will not return its result to Oledb. If anyone knows how to solve this I'll be very grateful. c# code:
DataTable GetAccessData(string FileName, string Password)
{
Query = "select * from [qry_1]";
DataTable DT = access2dt
(
FileName,Password,Query);
MessageBox.Show(Query);
if (DT == null || DT.Rows.Count <= 1) throw new Exception("Data not found");
else return DT;
}
public static DataTable access2dt(string filename, string password, string query)
{
string conString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Jet OLEDB:Database Password=" + password;
OleDbConnection mycon;
mycon = new OleDbConnection(conString);
if (mycon.State == ConnectionState.Closed)
{
mycon.Open();
}
try
{
OleDbCommand accessCommand = new OleDbCommand();
System.Data.DataTable dt = new System.Data.DataTable();
accessCommand = new OleDbCommand(query, mycon);
using (OleDbDataReader myReader = accessCommand.ExecuteReader())
{
DataTable myTable = new DataTable();
myTable.Load(myReader);
return myTable;
}
}
finally
{
mycon.Close();
}
}
And query [qry_1] saved in MS Access database looks like this:
SELECT tbl_MAPPING_GICM_AoA.[Type of expense]
FROM tbl_USGAAP_ALL_TRANS, tbl_MAPPING_GICM_AoA
WHERE tbl_USGAAP_ALL_TRANS.[Account]=[tbl_MAPPING_GICM_AoA].[Natural Account]
AND tbl_USGAAP_ALL_TRANS.[Created By] NOT IN (SELECT [Created by / User ID] FROM tbl_GICM_AoA_EXCL_FACTOR1)
AND tbl_USGAAP_ALL_TRANS.[Je Source] NOT IN (SELECT [JE SOURCE] FROM tbl_GICM_AoA_EXCL_FACTOR2)
AND tbl_USGAAP_ALL_TRANS.[Je Category] NOT IN (SELECT [JE CATEGORY] FROM tbl_GICM_AoA_EXCL_FACTOR3)
AND (tbl_USGAAP_ALL_TRANS.[Account] LIKE '4*' OR tbl_USGAAP_ALL_TRANS.[Account] LIKE '5*' OR tbl_USGAAP_ALL_TRANS.[Account] LIKE '6*' )
So when i run it in Access it works just fine but when i call this query from c# i get "Data not found" exception. If I remove the last string (with LIKE) in query everything starts working
