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
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;
User contributions licensed under CC BY-SA 3.0