Invalid Descriptor Index - ODBC - MFC -SQL 2000

I could offer up suggestion after suggestion, and then you could post "that won't work, I tried that" to each one.  That would waste everybody's time.   So, I'm kind of at a loss as to how to proceed.

-- Dan

I would proceed as follows:
1. Reduce the problem to it's simplest form. e.g.
  int main(int argc, char *argv[])
  {
    CDatabase mydb;
    // setup & connect mydb
    mydb.Open(/* your connection params here */)

    CRecordset myrs(&mydb);
    // setup myrs & open it
    myrs.Open(/* your sql here */);

   // Now add code to reproduce the error.
   // ??
  }

2. Now post the code along with some commentry saying what line the problem is on.

Sometime I find isolating the problem like this actually leads to a solution, since it becomes obvious what the error is.

I guess reading your mind would be one avenue toward defining the problem so that we could work on a solution.  I'm looking into that now.
-- Dan

Huh? Is that directed at me?

Dan,
This post was my first on EE and I am somewhat disappointed with your SECOND comment. I it common for people to ask questions "starting from Adam" style. But I appreciate abancroft reply and wouldn’t want your reply to discourage others to participate in discussions. You should keep in mind that what seem trivial to one can be a major discovery for other. So, let us douse this fire and go back to business.

After much frustration and further research - looking thru various posts - I have discovered that one must use cursor library by setting the m_bUseODBCCursorLib  to TRUE. (default FALSE ). I am no 100% convinced that this is correct, especially when the documentation states that is shoul be used only when "column binding" is used. Knowing nothing about column binding I accepted the outcome, with hesitation.

The error went away, however, after removing the code that set m_bUseODBCCursorLib to TRUE it continues to work.
Can anybody explain this??


Here is the code snippet which didn ot work with my comments:
 
// read column labes only
for( short fieldIndex = 0; fieldIndex < nFields; fieldIndex++ )
   {
     m_pSet_SQL->GetODBCFieldInfo(fieldIndex,fi);
     // OOR NOTE this line causes Invalid Descriptor Index if
     // m_bUseODBCCursorLib = FALSE; which is the default
     // but it works after set once!!
     // m_pSet_SQL->GetFieldValue(fieldIndex,varValue);
     }

GetFieldValue() eventually calls the SQLGetData() ODBC API.

The docs for SQLGetData() say that it will give the "invalid descriptor index" error when "The value specified for the argument ColumnNumber was 0, and the SQL_ATTR_USE_BOOKMARKS statement attribute was set to SQL_UB_OFF."

You are requesting the value for field index 0 (zero), which is the bookmark column.

Setting m_bUseODBCCursorLib to TRUE must turn on SQL_ATTR_USE_BOOKMARKS, thus preventing the error.

If you don#t want to use the cursor library, either:
a) Try turning on SQL_ATTR_USE_BOOKMARKS yourself; or
b) don#t request the value for column 0

Vaclav,
My objection was this text in your question:
>> I have read thru several posting here on the "Invalid Descriptor Index" problem and NONE of the solutions resolved the problem.

Surely you see that after an Expert reads that, virtually *any* amount of time spent working on your problem could be a total waste.  If you had taken a few moments to be specific "X did not work: I got error Y"  "Z failed because I must use MDAC 2.1" ...etc.  Then we could jump in with the meaningful suggestions.   For instance, note that abancroft could not even be certain that you knew how to open a database and access a recordset.  Then when you fail to respond for several days, we are even less likely to invest effort in your problem.

The Experts here do not get paid and there are plenty of questions.  I, for one, rarely invest any time at all until the Asker has responded at least once to a simple question (there are so many abandoned questions, it is just not worth it).  

If you want quick, accurate help, you will get it if you take the time to fully-describe your problem and if you provide useful snipets of code.

-=-=-=-=-=-=-=-=-=-=-=-=-==-=--=
Incidently, I did not offer the m_bUseODBCCursorLib or the "put blob items as the last fields of the recordset" suggestions becauase I assumed you had tried these two common solutions.

-- Dan

I guess I need to learn how to describe the problem without getting into describing the obvious and getting ahead of myself. I thought it was obvious that I know how to get into database, but I guess I was wrong.

I'll work on the "field 0" is a bookmark approach.

I still would like to know why the m_bUseODBCCursorLib "solution is tyied to using "column binding" - but I'll work on that one by myself too.
Thanks

This question didn't show any activity for more than 21 days. I will ask Community Support to close it unless you finalize it yourself within 7 days.
You can always request to keep this question open. But remember, experts can only help if you provide feedback to their comments.
Unless there is objection or further activity,  I will suggest to accept

    "abancroft"

comment(s) as an answer.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
========
Werner

The behavior of MFC / SQL has been modified to eliminate the "Invalid Descriptor Index" error by setting the m_bUseODBCCursorLib = TRUE;    

This does not fully qualify as a solution to the problem since it apparently masks the error, as abancroft pointed out. I believe it would be of disservice to others who struggle with this issue, and there are plenty of folks who do, to say that it is resolved.

My knowledge of SQL and current application do not call for sophisticated usage of SQL, no bookmarks and cursor, not yet. However, I do not see how retrieving "column 0" from the database would create this behavior.

I am sure there is someone on this forum who can stop us from guessing and bypassing problems.

Many thanks to everybody who participated so far. Vaclav
 

OK ...

    "refund and PAQ at zero points"

======
Werner