PRB: "Query Cannot Be Updated" Error Message with OLE DB Provider for ODBC Drivers

When you use an OLE DB or an ADO application that uses the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL) to try to retrieve data from a updateable cursor, or when you try to update, to insert, or to delete data in a cursor, you may receive the following error message:

This error occurs when MSDASQL uses query-based updates and cannot find a suitable combination of columns that is can use to uniquely identify a row in the cursor.

There are many different OLE DB and ADO methods that can return this error, including the following:

• ADO: Delete and Update methods of the Recordset object
• OLE DB: IRowset::GetNextRows, IRowsetChange::InsertRow, and IRowsetUpdate::Update

NOTE: This is not a complete list, but are some common methods that can return this error.

Generally, MSDASQL uses a combination of key columns, or uses all of the columns in the cursor to uniquely identify a row. However, by default MSDASQL excludes all real, float, and double numeric values from the WHERE clause that it builds to do this identification. If your cursor contains only real, float, or double numeric columns, or contains no columns that the ODBC driver has marked as searchable, you receive the error message that is listed in the "Symptoms" section. To determine whether or not the column is searchable, MSDASQL calls the ODBC API SQLColAttributes. If the driver reports either SQL_ALL_EXCEPT_LIKE or SQL_SEARCHABLE (these values are SQL_PRED_BASIC and SQL_PRED_SEARCHABLE in ODBC 3.0), MSDASQL includes the column, unless it is also a real, float, or double column.

See the "More Information" section of this article for more information about query-based updates, and how MSDASQL decides which combination of columns to use.

You can use the following two methods to prevent this error:

1. Method 1: If you only have to read data from the cursor, make it read-only. Because no data is sent back to the server through updates, MSDASQL does not have to search for columns to uniquely identify a row on the server.
2. Method 2: Modify the cursor columns to include columns that MSDASQL can use to uniquely identify a row.

In some cases, you can use the KAGPROP_INCLUDENONEXACT property to force the provider to consider columns that, by default, it would not consider for the key. In most cases, the provider can find a combination of suitable columns because if it must, it uses all of the columns in the rowset. However, by default, all real, float, and double numeric values are excluded from the WHERE clause. If you have a table that contains only columns of this type, you can set KAGPROP_INCLUDENONEXACT to VARIANT_TRUE to allow MSDASQL to include these columns in the WHERE clause. For examples of how to use this property, see the code samples in the "More Information" section of this article.

WARNING: If you insert only new data in the rowset, setting this property has no effect on your application because the application does not use generated WHERE clauses for inserts. However, if this property is set to VARIANT_TRUE and you perform updates or deletes, unexpected behavior may occur if these numeric columns are included in the WHERE clause. Exact comparisons are not always possible with these data types, so the wrong row may be updated or deleted, or rows may not be affected at all.

This behavior is by design.

When you work with an updateable cursor, MSDASQL tries to use positioned updates instead of query-based updates. If MSDASQL uses positioned updates, the ODBC driver that is used has a pointer to the current row of the cursor, and can update the cursor directly. If MSDASQL cannot use positioned updates for any reason (for example, the ODBC driver may not support it, or may not support certain properties that MSDASQL requires in a particular scenario), it reverts to query-based updates. This means that instead of the ODBC driver having a pointer to the current row, MSDASQL must construct an UPDATE or DELETE SQL statement that contains a WHERE clause with values that uniquely identify the current row in the cursor. INSERT statements do not affect any rows that are currently in the cursor, and do not allow WHERE clauses.

Although this WHERE clause is not used for all cursor operations, it is constructed during the initialization of the class that is used for all data manipulation on an updateable cursor. At the time of this initialization, the OLE DB provider cannot anticipate which operations you may want to do in the future, and it cannot create a WHERE clause conditionally (in other words, only when it must use a WHERE clause.) For example, you may open a cursor to read data, or to do inserts. In each of these cases, you do not have to have this WHERE clause because the provider does not have to uniquely identify a row on the server. However, because you requested an updateable cursor, the provider anticipates that you may be doing updates or deletes, in which case it must have the WHERE clause. If it tries to create a WHERE clause, and cannot find any searchable columns, you receive the error message that is listed in the "Symptoms" section of this article.

Steps to Reproduce the Behavior

ADO Example

The following ADO code reproduces the problem.

NOTE: See the comments in the code for information about how to use the KAGPROP_INCLUDENONEXACT property to work around the problem. OLE DB Example

The following console application uses OLE DB to reproduce this error. See the comments about the KAGPROP_INCLUDENONEXACT for more information about how to implement this workaround.

NOTE: The code uses the Visual Studio. NET OLE DB Consumer Templates, which contain support for multiple property sets for rowsets. The consumer templates that are provided with Visual C++ 6.0 do not contain this support.

For more information about query-based updates with MSDASQL and the KAGPROP_INCLUDENONEXACT property, see the "Query-Based Updates" topic in the OLE DB documentation at the following Microsoft Developer Network (MSDN) Web site: