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;
db.OpenEx(_T("DSN=NP0300;UID=NP0300;PWD=NP0300"));
CRecordset inputTable(&db);
inputTable.Open(
CRecordset::forwardOnly,
_T("SELECT * FROM UXTIMOB"),
CRecordset::readOnly);
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).
Bitch.
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.
inputTable.Close();
inputTable.Open(
CRecordset::forwardOnly,
_T("SELECT ") + fieldNames + _T(" FROM UXTIMOB"),
CRecordset::readOnly);
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.
- Techniques after using Swift for a month posted by sstrader on 26 August 2015 at 11:41:51 PM
- Some thoughts on Ruby after finishing a week (minus one day) of training posted by sstrader on 15 December 2011 at 8:59:30 PM
- Links on the singleton pattern posted by sstrader on 9 December 2011 at 9:19:50 AM
- Phonebot posted by sstrader on 29 October 2011 at 6:37:05 PM
- The labeled break in Java posted by sstrader on 4 September 2011 at 11:27:56 AM