2 August 2004

"Invalid column number" error with CRecordset

I recently was performing the simplest of database queries with CRecordset and ran into a snag. Snags are OK, mind you, as long as the answers out there somewhere.

They weren't, but they are now:

The problem occurred with the following idiot-proof code:

   // Open the database and read all records from the input table.
   CDatabase db;
   CRecordset inputTable(&db);

The query appears OK, but as I'm iterating through the results (and there were results) CRecordset throws everytime on the second call to GetFieldValue(). It didn't matter what field or whether I accessed it by name or index. On the second call, CDBException gets thrown with the message "Invalid column number <x>" (where x is the column index).


I hit the newsgroups first. A paltry 22 hits with few suggestions.

First, specify all of the columns in the SELECT clause.

   // Get the names of the fields.
   CString fieldNames;
   for (int index = 0; index < inputTable.GetODBCFieldCount(); ++index)
      CODBCFieldInfo fieldinfo;
      inputTable.GetODBCFieldInfo(index, fieldinfo);
      fieldNames += fieldinfo.m_strName + _T(", ");
   fieldNames.TrimRight(_T(", "));

   // Requery with the field names.
      _T("SELECT ") + fieldNames + _T(" FROM UXTIMOB"),

Nope. Same results.

Then, someone said that upgrading their Oracle ODBC driver (that's what I'm using) fixed it. I downloaded the newest, but that didn't fix it either.

I tested the code on another table and it worked--the difference was that the other table had a PRIMARY KEY specified. So, I rebuilt with a PRIMARY KEY and reimported. That failed also and in the same manner.

Finally the simple fix (and this is stupid but hindsight etc. etc.): change CRecordset::forwardOnly to CRecordset::snapshot. Tada. Well, tada without knowing why. I understand the differences but don't see the cause.

[ posted by sstrader on 2 August 2004 at 4:20:16 PM in Programming ]