can't find Missing operator sql oledb vb.net

0

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

sql
asp.net
sql-server
vb.net
oledb
asked on Stack Overflow Oct 23, 2017 by Deepak

1 Answer

0

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;
answered on Stack Overflow Oct 23, 2017 by Sean Lange

User contributions licensed under CC BY-SA 3.0