SQL Error

Posts: 1031 · Posted: Thu Jan 06, 2005 8:49 pm

TABLE STRUCTURE

Code:

Product
ProductID, VendorID, CategoryID, Product, Version, Notes

License
LicenseID, ProductID, CpuID, License, Notes

CPU
CpuID, Serial, Desc, Assigned

SELECT Product.ProductID, Product.Product, License.License, CPU.Desc, CPU.Assigned
FROM Product
INNER JOIN License ON License.ProductID = Product.ProductID
INNER JOIN CPU ON CPU.CPUID = License.CPUID
WHERE [Product].CategoryID=1 AND [Product].VendorID=1

This is with an access DB using ASP.NET... wtf's wrong with that?

Here's the error

Code:

System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression 'License.ProductID = Product.ProductID INNER JOIN CPU ON CPU.CPUID = License.CPUID'.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()
at ASP.index_aspx.Search(Object Sender, EventArgs e) in c:\Inetpub\inventory\Index.aspx:line 99

Posts: 746 · Posted: Thu Jan 06, 2005 9:29 pm

Try enclosing your CPU.Desc in brackets ala CPU.[Desc]

After all - Desc is a SQL reserved word...

Posts: 1031 · Posted: Thu Jan 06, 2005 9:51 pm

ahhhh yes!!! Good eye!!! Eye didn't see that Wink

Posts: 1031 · Posted: Thu Jan 06, 2005 9:52 pm

****... still didn't work. Same error, same place.

Posts: 746 · Posted: Fri Jan 07, 2005 6:42 am

Access may be different but I recreted your table structure and that query in SQLServer and that fixed it for me...without the [] I got your error...with em it was ok...I dunno what else to tell you.

Posts: 1031 · Posted: Fri Jan 07, 2005 1:40 pm

Yeah... unfortunately I don't have the option of MSSQL or I would have used that... GRRRR!!!

I hate access... I don't know why it hates me though...

Posts: 1031 · Posted: Fri Jan 07, 2005 2:25 pm

Okay... fixed it... this is the most retarded "answer" i've found to date... I had to wrap a section of my FROM clause and my first (but not the second) INNER JOIN in parentheses...

Code:

SELECT Product.ProductID, Product.Product, License.License, [CPU].[Desc], [CPU].Assigned
FROM (Product
INNER JOIN License ON License.ProductID = Product.ProductID)
INNER JOIN [CPU] ON [CPU].CPUID = License.CPUID
WHERE [Product].CategoryID=1 AND [Product].VendorID=1

*kicks access*