OleDbConnection to MS Access: SQL query like clause with escape characters and wildcards

Question

There must be some very obvious answer but i just can not see it nor find solution from web.

I try to count from db table how many rows contains file path LIKE "path".

In Access Settings-table I have rows where path field (short text type) containing string:

\\server\dir\something\

I want to count the rows where field begins with "\server\dir..".

After failing this with MS Access I started testing with mariaDB & heidiSQL and had some trial and error before getting valid answer with this query:

SELECT COUNT(*) FROM `Settings` WHERE `path` LIKE "%\\\\\\\\server\\\\dir\\\\%"
-> returns Count(*) = 3

In C# i get same return with this (found again with trial and error):

string query = @"SELECT COUNT(*) FROM Settings WHERE path LIKE '%\\\\\\\\server\\\\dir\\\\%'";
var Test = MySqlHelper.ExecuteScalar(connString, query);
-> returns Count(*) = 3

Now I cant get the same work with C# and Access using OleDb library:

string query = @"SELECT COUNT(*) FROM Settings WHERE path LIKE '*\\\\\\\\server\\\\dir\\\\*'";
OleDbConnection connection = new OleDbConnection(databsefile);
OleDbCommand command = new OleDbCommand(query, connection);
connection.Open();
var Test = command.ExecuteScalar();
-> returns Count(*) = 0

I have also tried queries (with C# and Access using OleDb library):

string query = @"SELECT COUNT(*) FROM Settings WHERE path LIKE '*\\\\server\\dir\\*'";
-> return count(*) = 0
string query = @"SELECT COUNT(*) FROM Settings WHERE path LIKE '*\\server\dir\*'";
-> return count(*) = 0

This works in MS Access 2013 giving valid result:

SELECT COUNT(*) FROM Settings WHERE path LIKE '*\\server\dir\*'
->returns Count(*) = 3

EDIT: Changed db table name table -> Settings

Answer 1

So Ms Access use * as a wildcard like T_D indicated and which is what my valide query use in MS Access. BUT like HansUp indicated C# OleDb API still uses ANSI wild cards.

But small correction even to that answer would be to escape the query in c#. So query returning valid answer is:

string query = "SELECT COUNT(*) FROM Settings WHERE path LIKE '%\\\\server\\dir\\%'";
OR
string query = @"SELECT COUNT(*) FROM Settings WHERE path LIKE '%\\server\dir\%'";
-> returns Count(*) = 3

HansUp also pointed that using ALIKE instead of LIKE % also works in MS Access so that way query is compatible with ANSI wildcard %

So here is my working solution (with command parameters):

string destFolder = @"\\server\\dir\";
string query = "SELECT COUNT(*) FROM Settings WHERE path LIKE @destFolder;";

using (OleDbConnection connection = new OleDbConnection(mdbfile))
{
    using (OleDbCommand command = new OleDbCommand(query, connection))
    {
        command.Parameters.AddWithValue("@destFolder", "%" + destFolder + "%");
        try
        {
            connection.Open();
            if ((int)command.ExecuteScalar() == 3)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        catch (OleDbException ex)
        {
            //handle OleDb error
            return false;
        }
    }
}

Answer 2

You reported this query works correctly in Access:

SELECT COUNT(*) FROM table WHERE path LIKE '*\server\dir*'

With your c# code, you are using OleDB to interface with the Access database. And OleDb requires ANSI wildcards: % and _ instead of * and ?.

So, since the above query worked inside Access, this one should work from c#:

SELECT COUNT(*) FROM [table] WHERE path LIKE '%\server\dir%'

Queries run with OleDb also seem to be more vulnerable to trouble with reserved words. So I enclosed table in square brackets to avoid trouble.

If you want a query which will always work the same inside or outside of Access, use ALike instead of Like. ALike signals the database engine to expect ANSI wild cards:

SELECT COUNT(*) FROM [table] WHERE path ALIKE '%\server\dir%'

Answer 3

When you use the @ before the string, you do not need to escape the backslashes again. And the placeholder for LIKEs is %, not *.

So try:

string query = @"SELECT COUNT(*) FROM table WHERE path LIKE '%\\\\server\\dir\\%'";

To avoid all that escaping though, consider using SQL parameters.

PS: table is a reserved keyword. You should not name a table table, this will always cause issues.