MSSQL ODBC getfieldvalue throwing exception

When I access my Access database using ODBC and getfieldvalue in forward field index order or backward index order I have no problems.
When I try to access the same database converted to MSSQL using a variety of converters and simply point at the new ODBC connection, DBCORE.CPP throws an error "GetFieldValue operation failed on field" if I try to fietch a field using an index less than the last field fetched.

My code (simplified) is :

    CString cstrg;

    rs.Open(CRecordset::dynaset,"SELECT * from DATAB");
    rs->GetFieldValue(5,cstrg);    \\Succeeds
    rs->GetFieldValue(7,cstrg);    \\Succeeds
    rs->GetFieldValue(4,cstrg);    \\ Fails

Is this a limitation of the MSSQL ODBC driver ?

To fetch in any other order is a real problems.

Any ideas ??


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jaime OlivaresSoftware ArchitectCommented:
First step I suggest you is to compare field type of Access and MSSQL table.
PSernzAuthor Commented:

Field types are not a problem.  These are text fields that can be viewed and changed in MS SQL using any of the query tools without a problem.  I can access all the fields ok if I access them in index order.  If I try to access a field with an index earlier than the last index I get the error.

Very strange.

AndyAinscowFreelance programmer / ConsultantCommented:
Does it still fail if you try by name instead of by ordinal value?
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

AndyAinscowFreelance programmer / ConsultantCommented:
Is the field null?  (That should not be a problem with string values but...)
PSernzAuthor Commented:
Have not tried name fetch.  Can't imagine it will make a difference.
The fields carry text.  All of the fields fail if you fetch a field with index less than the last field fetched.
PSernzAuthor Commented:
I've found the problem.
SQLGetData (used by getfieldvalue) can not fetch columns in reverse order. Documented....
Sounds incredible but true.
This means that ODBC routines used toward Access will not work for MS SQL.
Thanks Microsoft...
PAQed with points refunded (500)

Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.