FIX: Visual Studio .NET Query Builder builds incorrect syntax for Jet queries that involve joins

View products that this article applies to.

This article was previously published under Q318646

SYMPTOMS

If you use the Query Builder tool as part of the Data Adapter Configuration Wizard for the OLEDBDataAdapter object, and you connect to a Microsoft Access database with the Microsoft Jet OLEDB provider, query syntax that involves more than one table join may be generated incorrectly. This will result in an error that resembles the following (table and column names are for illustration purposes only):

Syntax error (missing operator) in query expression 'Employees.EmployeeID = Orders.EmployeeID INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID

This problem occurs when there is more than one JOIN clause in the query.

Back to the top

CAUSE

The query is missing the parentheses that must be present for the Jet engine to parse the query properly.

Back to the top

RESOLUTION

Changing the query in the Query Builder or the Data Adapter Configuration Wizard does not prevent this error because the Wizard resets the query to the incorrect syntax.

However, you can edit the query that the Wizard generates in the "Windows Form Designer Generated Code" section in either of the following ways:

Add nested parentheses around the JOIN clauses; for example:

SELECT * FROM
(((Table1 INNER JOIN Table2 ON Column = Column) INNER JOIN
Table3 ON Column = Column) INNER JOIN Table4 ON Column = Column) ...

Change the query to specify its joins in the WHERE clause as follows:

SELECT * FROM Table1, Table2, Table3, Table4
WHERE Table1.Column = Table2.Column
AND Table2.Column = Table3.Column
AND Table3.Column = Table4.Column
AND ...

To make such a change, follow these steps:

Open the code window of the form with the DataAdapter control that contains the SELECT command that contains joins.
Expand the region with the label "Windows Form Designer generated code".
Find the statement that assigns the command text to the Command object used by the DataAdapter; for example:
Me.OleDbSelectCommand1.CommandText = "SELECT Customers.Address, Customers.City,...
Edit the SELECT statement specified in one of the two ways shown earlier.

Note that reinvoking the Data Adapter Configuration Wizard or the Query Builder on this OleDbDataAdapter object may remove these changes.

Back to the top

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article. This bug was corrected in Visual Studio .NET 2003.

Back to the top

MORE INFORMATION

Steps to Reproduce Behavior

Open Visual Studio .NET and create a new Microsoft Visual Basic or Microsoft C# Windows Application. Form1 is created by default.
Drag a new OLEDBDataAdapter from the Data components on the toolbox to Form1.
In the Wizard dialog box, click Next, and then click New Connection to open the UDL dialog box.
Click the Provider tab, click the Jet 4.0 OLEDB Provider, and then click Next.
On the Connection tab, click the Browse button to locate and select your copy of the Northwind database. Click OK.
Click Next, and make sure that Use SQL Statements is selected.
Click Next, click the Query Builder button, and then add the Customers, Orders, and Order Details tables. In the Query Builder window, note these tables with their relationships defined by lines connecting them. If the relationship is missing between Orders and Order Details, remove the Order Details table from the query and then add the table again. To do this, right-click the top pane in the Query Builder, click Add Table, and select the Order Details table.
Add a few fields to the query by selecting the boxes next to column names in each of the three tables.
In the query text box underneath the field columns grid, note the query that uses two INNER JOIN clauses. Click anywhere in the whitespace in the top pane of the Query Builder window, and then click Run. An error that resembles the one shown in the "Symptoms" section of this article appears.