BUG: Wizards Create Invalid Column Names for QueryDef Join

• AppWizard and ClassWizard included with: - Microsoft Visual C++, 32-bit Edition, versions 4.0, 4.1, 4.2,

       4.21, 5.0
  

SYMPTOMS

If you use AppWizard or ClassWizard to create a MFC DAO recordset based on a predefined query (QueryDef) that joins tables that contain columns with identical names, the application generates an exception when it attempts to open the recordset. The exception indicates that:

   '[table.column]' isn't a valid parameter name.

CAUSE

The exception is thrown due to the incorrect manner in which the wizards add delimiting brackets to the standard table_name.column_name references in your CDaoRecordset-derived class's DoFieldExchange.

The wizards generate the following syntax:

   [table.column]

   [table].[column]

RESOLUTION

You must manually modify the CDaoRecordset-derived class's DoFieldExchange to correct those column references that use the incorrect syntax. Follow this procedure:

1. Do not add qualifiers to any columns that are not duplicates. Qualifying columns where no ambiguity exists will result in errors. For complete information, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q139994

      TITLE     : PRB: Specifying Table Name with Column Name in MFC DAO
   
   

2. Modify the wizard-generated code to delimit the table and column names correctly. The exact form of the final column reference is described in detail in the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q139997

      TITLE     : FIX: "Item not found in this collection" with MFC DAO Join
   
      NOTE: The content of Q139997 has been copied into the "References"
      section of this article for your convenience
   
   

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

REFERENCES

The complete text of the following article in included in this article for your convenience:

********************* Beginning of Included Article *********************

ARTICLE-ID: Q139997

TITLE     : FIX: "Item not found in this collection" with MFC DAO Join

• AppWizard and ClassWizard included with: Microsoft Visual C++, 32-bit Edition, version 4.0

SYMPTOMS

When you try to modify a record in an MFC DAO application that uses a recordset based on a join and that was created by AppWizard or ClassWizard, the application may throw an exception on the update. Specifically, the exception indicates:

   Item not found in this collection.

CAUSE

When the wizards generate references to columns that have names that appear in multiple tables of the join, the wizards will precede the column name with the name of the table as a qualifier. Both the table name qualifier and column name are enclosed in brackets in the DFX functions of the CDaoRecordset-derived class's DoFieldExchange. For example:

   [Table].[Column]

Note that the presence of brackets around unqualified column names does not interfere with the field name search and is thus acceptable. This explains why some fields will be updatable while attempts to update others throws the exception; the updatable fields are likely unique and therefore unqualified.

RESOLUTION

To work around this problem, use one of the following two techniques depending on the names of the affected tables and columns (the ones for which table qualifiers are necessary).

If Table and Column Names Contain No Spaces

Remove the brackets that enclose the table and column names. The resulting reference should be of this form:

   Table.Column

If Table and/or Column Names Contain Spaces

Don't remove the bracket delimiters or your application will throw an exception that indicates:

   Syntax error in query expression 'table name.column name'.

   void CMyDaoRecordset::SetFieldValue(LPCTSTR lpszName,
                                  const COleVariant& varValue)
   {
        CString strNewName(lpszName);
        StripBrackets(strNewName, strNewName.GetBuffer(0));
        strNewName.ReleaseBuffer();
        CDaoRecordset::SetFieldValue(strNewName, varValue);
   }

NOTE: You can easily add this override to your application by using the Visual C++ ClassView Add Member Function command. Please refer to the online documentation and Help files for more details about this command.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. This bug was corrected in Visual C++ 4.1.

********************* End of Included Article *********************