Search code examples
.netms-accessoledboledbexception

OleDB + Jet: Syntax Error in Join Operation

I've built a complex query for my access database and in access it works like a charm. But when I try to run the same query in my program via OleDB I get the exception "Syntax Error in Join Operation". No further info.

SELECT
    MainTable.Main_dbID,
    D0.Kvp_Value AS ["Value"]
FROM (MainTable
INNER JOIN (
    SELECT Main_dbID, Kvp_Value
    FROM KVPTable
    WHERE Kvp_Code IN (1, 2, 4)
) AS D0
ON D0.Main_dbID = MainTable.Main_dbID)
WHERE Main_Time <= ? AND Main_Time >= ?;

I've broken the query down as far as I felt it to be still representative. The reason why I'm using a subselect instead of a direct join is that I need to join to my Key Value Table multiple times to put different keys into different columns. I couldn't get that to work any other way.

I'm working with C# 2.0 and Visual Studio 2008 in case that's relevant.

Solution

  • Found the problem. The way I entered the query broke it...

    EDIT: As you can see I distributed the query over multiple lines so it's a bit easier to read. When I pasted it into my source code this happened:

    "SELECT FooField" +
"FROM BarTable;";

    which results in "SELECT FooFieldFROM BarTable;" - so obviously not valid syntax it's painful. I just didn't realize it first...