Syntax Error (Missing Operator) in query expression with Multiple Inner Join in C# MS Access

Question

I am using Inner Join to Join 3 Tables. But when I execute this, I got error

Syntax Error (Missing Operator) in query expression 'a.Group_ID = b.Group_ID INNER JOIN VendorList AS c ON a.Vendor_Id = c.Vendor_I'

How do I use multiple inner joins in C# with MS Access?

str = "Select a.ID, b.Group_Name, a.Voucher_No, a.Bill_No, a.Date, c.Vendor_Name, a.Amount FROM BillTable a INNER JOIN GroupName AS b ON a.Group_ID = b.Group_ID INNER JOIN VendorList AS c ON a.Vendor_Id = c.Vendor_Id ";
                da = new OleDbDataAdapter(str, cn);

Steve · Accepted Answer · 2020-07-19 08:37:13Z

4

That syntax, while accepted by any database more powerful than Access, is not quite right when you are dealing with JET Sql. In this case you need to add round brackets around each join group to help the JET parser understand the query.

So you need to write this one instead

str = @"Select a.ID, b.Group_Name, a.Voucher_No, a.Bill_No, a.[Date], c.Vendor_Name, a.Amount 
      FROM ((BillTable a INNER JOIN GroupName AS b ON a.Group_ID = b.Group_ID) 
                        INNER JOIN VendorList AS c ON a.Vendor_Id = c.Vendor_Id) ";

Note that the most external pair of round brackets are not really required. I put them there just to show how to add them if more joins are required.

Also I think that a.Date will be the cause of another exception because Date is a reserved keyword. You will get a syntax error again. You can avoid it adding square brackets around Date like in a.[Date]

edited Jul 19, 2020 at 8:37

answered Jul 18, 2020 at 9:23

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Syntax Error (Missing Operator) in query expression with Multiple Inner Join in C# MS Access

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related