CODEWITHSUNDEEP
.netms-accessoledboledbexception
Kempeth
Kempeth

Reputation: 1948

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.

Upvotes: 0

Views: 898

Answers (2)

Kempeth
Kempeth

Reputation: 1948

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...

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91356

It is certainly a confusing message. What is wrong is the extra comma:

D0.Value AS ["Value"],  <-- here

I hope those names are not real field (column) names, because Value and Time are reserved words and will cause endless problems. I suggest that at least you enclose them in square brackets, if they are real names.

Upvotes: 0

Related Questions