Please help - Syntax error in query expression

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Please help - Syntax error in query expression

Author

Topic

bszom
Starting Member

9 Posts

Posted - 2006-09-14 : 09:53:49

Hi there,

I'm busy developing an app in C#, and can't figure out what is causing this error. The exact error as the JIT compiler reports it:

System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression 's.RepID = r.RepID INNER JOIN Products AS p ON s.ProductID = p.ProductID INNER JOIN Gifts AS g ON s.GiftID = g.GiftID'.

Here is the code block:

OleDbDataAdapter adapter = new OleDbDataAdapter(@"SELECT r.Surname AS Rep, p.Name AS Product, g.Name AS Gift, s.Quantity, s.Discount, s.DateOfSale, s.DateOfDelivery, s.DateOfEntry, s.Paid, s.Delivered, s.Cancelled, s.Notes
FROM Sales AS s
INNER JOIN Reps AS r ON s.RepID = r.RepID
INNER JOIN Products AS p ON s.ProductID = p.ProductID
INNER JOIN Gifts AS g ON s.GiftID = g.GiftID
WHERE s.SaleID = " + this.vsSale.SelectedValue, conn);

Any help would be much appreciated! :)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-14 : 10:11:01

I don't see any kind of error in the code you provided. Can you check whether same query runs in the SQL Query Analyzer?

Harsh Athalye
India.
"Nothing is Impossible"

bszom
Starting Member

9 Posts

Posted - 2006-09-14 : 10:19:53

Unfortunately it's an Access DB. I do have SQL Server - pasting the query into the query analyzer and parsing it produces no errors.
Very weird. Also, in the error message the query starts with s.repID ... etc., this is after the first join. It is as if the first part of the query does not exist.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-14 : 10:56:34

quote:
Originally posted by bszom

Unfortunately it's an Access DB. I do have SQL Server - pasting the query into the query analyzer and parsing it produces no errors.
Very weird. Also, in the error message the query starts with s.repID ... etc., this is after the first join. It is as if the first part of the query does not exist.

Can you check whether query runs in Access properly? Also many times error messages are not very reliable in detecting the position of actual error, so don't think that the part shown in the error message is only the problem area!!

Harsh Athalye
India.
"Nothing is Impossible"

bszom
Starting Member

9 Posts

Posted - 2006-09-14 : 11:19:46

It turns out Access wants table joins to be bracketed in the following weird way:

SELECT Reps.Surname, Products.Name, Gifts.Name, Sales.Quantity, Sales.Discount, Sales.DateOfSale, Sales.DateOfDelivery, Sales.DateOfEntry, Sales.Paid, Sales.Delivered, Sales.Cancelled, Sales.Notes
FROM Reps INNER JOIN (Products INNER JOIN (Gifts INNER JOIN Sales ON Gifts.GiftID = Sales.GiftID) ON Products.ProductID = Sales.ProductID) ON Reps.RepID = Sales.RepID
WHERE (((Sales.SaleID)=1));

Why it can't just work with a query that works in SQL is beyond me..

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-14 : 11:35:37

May be Access wants query to look more readable

BTW, you have an extra opening brace in the Where clause:

WHERE (((Sales.SaleID)=1))

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources