Microsoft KB Archive/326166

= PRB: &quot;Query Cannot Be Updated&quot; Error Message with OLE DB Provider for ODBC Drivers =

Article ID: 326166

Article Last Modified on 5/12/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q326166



SYMPTOMS
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



CAUSE
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 &quot;Symptoms&quot; 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 &quot;More Information&quot; section of this article for more information about query-based updates, and how MSDASQL decides which combination of columns to use.



RESOLUTION
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 &quot;More Information&quot; 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.



STATUS
This behavior is by design.



MORE INFORMATION
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 &quot;Symptoms&quot; 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. Dim conn As ADODB.Connection Set conn = New ADODB.Connection conn.Open &quot;Provider=MSDASQL;Data Source=myDsn;User ID=myUid;Password=myPwd;&quot;

Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.ActiveConnection = conn 'in some cases, uncommenting the following line will prevent the error 'rs.Properties(&quot;Include SQL_FLOAT, SQL_DOUBLE, and SQL_REAL in QBU where clauses&quot;) = True rs.Open &quot;SELECT * FROM testTable&quot;,, adOpenStatic, adLockOptimistic rs.AddNew rs!col1 = 1.234 rs.Update

rs.Close conn.Close 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.


 * 1) include 
 * 2) include 
 * 3) define DBINITCONSTANTS
 * 4) include 

void PrintErrorInfo(void);

int _tmain(int argc, _TCHAR* argv[]) {   HRESULT hr = CoInitialize( NULL ); CDataSource ds; hr = ds.OpenFromInitializationString(OLESTR(&quot;Provider=MSDASQL;Data Source=myDSN;User ID=myUid;Password=myPwd;&quot;)); if (FAILED(hr)) {PrintErrorInfo; return -1;}

CSession sess; hr = sess.Open( ds ); if (FAILED(hr)) {PrintErrorInfo; return -1;} CDBPropSet psArray[2]; psArray[0].SetGUID(DBPROPSET_ROWSET); bool bRetVal; bRetVal = psArray[0].AddProperty( DBPROP_IRowsetChange, true ); bRetVal = psArray[0].AddProperty( DBPROP_UPDATABILITY, DBPROPVAL_UP_INSERT );

//The following property can be used in some cases to prevent the error. //psArray[1].SetGUID(DBPROPSET_PROVIDERROWSET); //bRetVal = psArray[1].AddProperty(KAGPROP_INCLUDENONEXACT, true);

CTable tbl; hr = tbl.Open( sess, _T(&quot;testTable&quot;), psArray, 1); //change the last parameter to 2 if using KAGPROP_INCLUDENONEXACT if (hr == DB_E_NOTABLE) {       printf(&quot;Table not found.\n&quot;); return -1; }   else if (FAILED(hr)) {       PrintErrorInfo; return -1; }

bRetVal = tbl.SetValue(1, 1.234 ); bRetVal = tbl.SetStatus(1, DBSTATUS_S_OK );

hr = tbl.Insert;

if (FAILED(hr)) PrintErrorInfo; else printf(&quot;Successfully inserted data\n&quot;); return 0; }

void PrintErrorInfo(void) {   CDBErrorInfo myErrorInfo; ULONG numRec = 0; BSTR myErrStr,mySource; LCID lcLocale = GetUserDefaultLCID; myErrorInfo.GetErrorRecords(&numRec); myErrorInfo.GetAllErrorInfo(0,lcLocale,&myErrStr,&mySource); wprintf(L&quot;Error Message: %s\n&quot;, myErrStr); }

