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

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q139997

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.

Back to the top

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]

The presence of the brackets combined with the use of the table name qualifier causes the search for the specified field in the collection of fields maintained by the database engine to fail.

NOTE: 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.

Back to the top

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).

Back to the top

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

Back to the top

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'.

Instead, you must override one of the existing overloads of the virtual function CDaoRecordset::SetFieldValue() as follows (assuming your CDaoRecordset is named CMyDaoRecordset):

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

This override uses the StripBrackets member function of CDaoRecordset to remove the offending brackets and allows the search for the field in the field collection to succeed.

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.

Back to the top

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.

Back to the top

APPLIES TO

The AppWizard
Microsoft Visual C++ 4.0 Standard Edition

Back to the top

kbbug kbdatabase kbfix kbvc410fix kbwizard KB139997

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top