can't find Missing operator sql oledb vb.net

Question

When I run the below query, I get syntax error in query expression. This exact code worked when I was using sqlclient and reading from an sql server .mdf file. But now I'm reading from an ms access .mdb file and it's giving me this error of a missing operator

Dim Adapter As New OleDbDataAdapter
Dim Data As New DataTable
Dim SQL As String
Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Shantara Production IT.mdb")
Dim cmd As New OleDbCommand()
grdvbatchprodn.Visible = True
SQL = "SELECT [KN - ProductionOrderDetails].BatchNo, 
              [GN - EntityMaster].EntityName, 
              [FG - End Product Codes].ProductCode,
              SUM([KN - ProductionOrderDetails].ProductionQty) AS [Batch Total Panels],
              [KN -Special Instructions Master].SpecialInstructionDetail,
              [KN - ProductionOrderHeader].KnittBatchComplete 
        FROM [KN - ProductionOrderDetails] INNER JOIN [KN - KnittingOrder]      
            ON  [KN - ProductionOrderDetails].KnittingOrderID = [KN - KnittingOrder].KnittingOrderID
        INNER JOIN [GN - EntityMaster] 
            ON [GN - EntityMaster].EntityID = [KN - KnittingOrder].EntityID 
        INNER JOIN [KN -Special Instructions Master] 
            ON [KN -Special Instructions Master].SpecialInstructionID = [KN - KnittingOrder].SpecialInstructionID
        INNER JOIN [KN - ProductionOrderHeader] 
            ON [KN - ProductionOrderHeader].BatchNo = [KN - ProductionOrderDetails].BatchNo 
        INNER JOIN [FG - End Product Codes] 
            ON [FG - End Product Codes].ProductID = [KN - ProductionOrderHeader].ProductID
        INNER JOIN [KN - KnittingDetailsHeader] 
            ON [KN - KnittingDetailsHeader].BatchNo = [KN - ProductionOrderDetails].BatchNo
        WHERE [KN - ProductionOrderHeader].KnittBatchComplete = 0
        GROUP BY [KN - ProductionOrderDetails].BatchNo, [GN - EntityMaster].EntityName, [FG - End Product Codes].ProductCode, [KN -Special Instructions Master].SpecialInstructionDetail, [KN - ProductionOrderHeader].KnittBatchComplete
        ORDER BY [KN - ProductionOrderDetails].BatchNo, [GN - EntityMaster].EntityName;"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = SQL

        Adapter.SelectCommand = cmd
        Adapter.Fill(Data)

        grdvbatchprodn.DataSource = Data
        grdvbatchprodn.DataBind()
End Sub

The error is:

System.Data.OleDb.OleDbException occurred HResult=0x80040E14
Message=Syntax error (missing operator) in query expression '[KN - ProductionOrderDetails].KnittingOrderID = [KN - KnittingOrder].KnittingOrderID INNER JOIN [GN - EntityMaster] ON [GN - EntityMaster].EntityID = [KN - KnittingOrder].EntityID INNER JOIN [KN -Special Instructions Master] ON [KN -Speci'.
Source=Microsoft Access Database Engine StackTrace:

Thank you in Advance

Ouch....those table names make me want to take out my eyeballs and pop them. At the very least learn how to use aliases so you don't have to keep repeating those nasty things. — Sean Lange, Oct 23 '17 at 14:39
Step by step remove parts from the query. When the problem disappears, you've found the problem row. — jarlh, Oct 23 '17 at 14:40
@SeanLange I did use alliases initially but the problem is ms access doesn't support them so I had to do it this way — Deepak, Oct 23 '17 at 14:57
What??? Access supports table aliases. You just have to include the AS keyword which is optional in sql server. I have to ask why you would choose to take data from sql server and put it in Access. — Sean Lange, Oct 23 '17 at 15:02
@SeanLange thanks for the help with the 'AS' I did not know that.. because the system I'm bulding is only part of the program.. all other forms are built in the current access and I found out the problem was with multiple INNER JOINS you need to use parentheses — Deepak, Oct 23 '17 at 15:36

score 0 · Answer 1 · answered Oct 23 '17 at 15:08

Just adding a slight bit of formatting and using aliases this wall of text become pretty manageable. I would try to figure out if you can change those table names so they don't have spaces and other reserved characters. This should work just fine in Access.

SELECT pod.BatchNo, 
    em.EntityName, 
    epcProductCode,
    SUM(pod.ProductionQty) AS [Batch Total Panels],
    sim.SpecialInstructionDetail,
    poh.KnittBatchComplete 
FROM [KN - ProductionOrderDetails] as pod
INNER JOIN [KN - KnittingOrder] as ko ON  pod.KnittingOrderID = ko.KnittingOrderID
INNER JOIN [GN - EntityMaster] as em ON em.EntityID = ko.EntityID 
INNER JOIN [KN -Special Instructions Master] as sim ON sim.SpecialInstructionID = ko.SpecialInstructionID
INNER JOIN [KN - ProductionOrderHeader] as poh ON poh.BatchNo = pod.BatchNo 
INNER JOIN [FG - End Product Codes] as epc ON epcProductID = poh.ProductID
INNER JOIN [KN - KnittingDetailsHeader] kdh ON kdhBatchNo = pod.BatchNo
WHERE poh.KnittBatchComplete = 0
GROUP BY pod.BatchNo
    , em.EntityName
    , epcProductCode
    , sim.SpecialInstructionDetail
    , poh.KnittBatchComplete
ORDER BY pod.BatchNo
    , em.EntityName;

Access requires that each join clause be surrounded by parentheses, so you need to add those whether you use aliases or not. See the duplicate I marked. — D Stanley, Oct 23 '17 at 15:14
@DStanley ok. I haven't written a query in Access in...well....a REALLY long time. Rather bizarre requirement to surround a join clause in parenthesis but whatever, I will continue to not use Access. — Sean Lange, Oct 23 '17 at 15:27

can't find Missing operator sql oledb vb.net

1 Answers1