Article ID: 326166 - View products that this article applies to.
This article was previously published under Q326166
This article has been archived. It is offered "as is" and will no longer be updated.
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:
Query cannot be updated because it contains no searchable columns to use as a key
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:
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:
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.
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 BehaviorADO 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:
Contact us for more help
Connect with Answer Desk for expert help.