Article ID: 318646
Article Last Modified on 9/15/2005
APPLIES TO
- Microsoft Visual Studio .NET 2002 Professional Edition
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):
This problem occurs when there is more than one JOIN clause in the query.
CAUSE
The query is missing the parentheses that must be present for the Jet engine to parse the query properly.
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.
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.
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.
Additional query words: query builder missing operator expression multiple
Keywords: kbbug kbpending kbvs2002sp1sweep KB318646
