Querying MS Access from C# on stored file paths

[Dobedani]

Hi folks,
I'm storing some particulars about file usage in my MS Access
database, incl. the full paths of those files. I tried to query the
database in my C# code. My idea was to retrieve from the database by
means of SQL when a file was used the last time, but I only know the
directory it is in, so in my code I tried to construct a WHERE clause
as follows: String whereClause = "WHERE Filename LIKE '" + dirpath +
"*'";

Of course I'm working on Windows and I don't want to think of
portability now. Obviously, the variable dirpath contains double
backslashes. FYI: MS Access stores the file paths with single
backslashes. When I try a query as described from within MS Access, I
get the desired result as long as the path is in lowercase.
However, it looks like MS Access cannot make any match when I try to
send the same query via OLEDB. I have tried it with double
backslashes, with single backslashes as part of the SQL in verbatim
form and with double backslashes as part of the SQL in verbatim form.

For the mean time, I am retrieving all records from the table and I
make the match in the code. Nevertheless I don't accept that this is
not possible by means of SQL. Does anybody have any idea how it could
be done in that way? TIA

Kinds regards,
Dobedani

 

[Alberto Poblacion]

[...] in my code I tried to construct a WHERE clause
as follows: String whereClause = "WHERE Filename LIKE '" + dirpath +
"*'";

Have you tried using a "%" instead of the "*"?

[...] Obviously, the variable dirpath contains double
backslashes.

No, the _variable_ should not contain double backslashes. You only
double them when you write a string literal in your source code. Once the
string literal is assigned to a variable, the value inside the variable will
only contain single backslashes.