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

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

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "Provider=MSDASQL;Data Source=myDsn;User ID=myUid;Password=myPwd;"

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("Include SQL_FLOAT, SQL_DOUBLE, and SQL_REAL in QBU where clauses") = True
rs.Open "SELECT * FROM testTable", , 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.

#include <atldbcli.h>
#include <assert.h>
#define DBINITCONSTANTS
#include <msdasql.h>

void PrintErrorInfo(void);

int _tmain(int argc, _TCHAR* argv[])
{
	HRESULT hr = CoInitialize( NULL );
	CDataSource ds;
	hr = ds.OpenFromInitializationString(OLESTR("Provider=MSDASQL;Data Source=myDSN;User ID=myUid;Password=myPwd;"));
	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<CDynamicAccessor,CRowset> tbl;
	hr = tbl.Open( sess, _T("testTable"), psArray, 1); //change the last parameter to 2 if using KAGPROP_INCLUDENONEXACT
	if (hr == DB_E_NOTABLE)
	{
		printf("Table not found.\n");
		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("Successfully inserted data\n");
		
	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"Error Message: %s\n", myErrStr);	
}