c# - Different LIKE behaviour between my application and the Access query wizard

Question

I am executing following query from my web application and access 2007 query wizard. And I am getting two different result.

SELECT R.Rept_Name, D.Dist_Name,S.State_Name FROM (tblReporter AS R LEFT JOIN tblDist AS D ON R.Dist_Id=D.Dist_Id) LEFT JOIN  tblState AS S ON S.State_Id=R.State_Id WHERE R.Rept_Name LIKE '*Ra*' ORDER BY R.Rept_Name;

Result from web application is with 0 rows and from query wizard 2 rows.If I remove where condition than both result are same. Please help me what is wrong with query. If any other info require please tell me.

Web application code ...

public DataTable getRept(string rept, string mobno)
{
    DataTable dt = new DataTable();
    using (OleDbConnection conn = new OleDbConnection(getConnection()))
    {
        using (OleDbCommand cmd = conn.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT R.Rept_Name, D.Dist_Name,S.State_Name FROM (tblReporter AS R LEFT JOIN tblDist AS D ON R.Dist_Id=D.Dist_Id) LEFT JOIN  tblState AS S ON S.State_Id=R.State_Id WHERE R.Rept_Name LIKE '*" + rept + "*'  ORDER BY R.Rept_Name;";
            conn.Open();
            using (OleDbDataReader sdr = cmd.ExecuteReader())
            {
                if (sdr.HasRows)
                    dt.Load(sdr);
            }

        }
    }
    return dt;
}

Answer 1

You are getting tripped up by the difference in LIKE wildcard characters between queries run in Access itself and queries run from an external application.

When running a query from within Access itself you need to use the asterisk as the wildcard character: LIKE '*Ra*'.

When running a query from an external application (like your C# app) you need to use the percent sign as the wildcard character: LIKE '%Ra%'.