Insufficient base table information for updating or refreshing

Question

The "Insufficient base table information for updating or refreshing" error occurs when a SQL query has a semicolon on the end of the statement.

Example: "SELECT * FROM Orders WHERE OrderID = '1177';"

The cure is to remove the semicolon or set the CursorLocation to adUseServer.  When a semicolon is removed from the SQL statement, ADO makes the following essential calls to obtain schema information.

1. calls IDBCreateCommandImpl::CreateCommand
2. calls ICommandImpl::SetCommandText with "SELECT * FROM <tablename>"
3. calls ICommandPrepare::Prepare
4. calls IColumnsInfo::GetColumnInfo
5. calls IDBSchemaRowsetImpl::GetRowset (obtains primary keys information forTableName='<tablename>')

When the semicolon is present, ADO does not make these calls, hence the error.

If anyone could explain this odd behavior, I'd welcome it.

-Jack

Answer 1

You didn't mention which provider is used, but I'd think that the problem is that when checking whether the command text represents one statement versus multiple statements the provider looks for semicolons as statement separators.
In this case the provider apparently decided that the command text was for a multiple-statement batch and therefore was not updateable.

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

Thanks for the reply.

The issue is with ADO.  It makes the provider calls (or it doesn't) based on some internal logic which confounds me. The provider in this case is SQL Anywhere OLE DB. Using a debug version of the provider, I can see the calls that ADO is or is not making to the provider.

When a semicolon is absent ADO makes the sequence of calls to the provider that I described in my post.  When a semicolon is present, ADO does not make these calls.  The presence or absence of a semicolon is the only source code change to the application.  Since ADO is doing the calling, not the provider, ADO must be confused by the presence of the ";".

Searching the Internet for the "Insufficient base table information for updating or refreshing" message, you'll see that ADO has an issue with the semicolon in relation to a number of other providers and, in every case, the solution was to remove the semicolon.

--Jack