ODBC SQLFetch problem

Hi,

I have ODBC code reading data from a XLS file.

It opens the file, reads the sheets and columns available, then presents a dialog to allow the user to select which columns are to be used to load the data.

All of this works, almost.

It is a simple "Select A,B,C FROM Sheet1" using SQLExecDirect

When using SQLFetch it always skips the first row of data.

Thanks, any ideas would be great.

Keith

Keith,

If you are using Fast Forward Only AutoFetch cursors, then as an optimization the driver might fill the SQL_ATTR_ROW_ARRAY_SIZE number of rows to your application buffers as part of SQLExecDirect execution. I do know that this is indeed the case with SQL Native Client (SQLNCLI) ODBC driver, not sure about the excel driver though. Can you check to see if that is the case in your scenario. Hope this helps.

Thanks

Waseem

|||

Thanks for the response,

I have played with different row sizes does not change the results.

I have also tried different files with different row counts, It always miss only the first row.

I have also added blank rows above the first and it still misses the first row of data.

Keith

|||

Does SQLExecDirect fetch the 1st row for you? Can you check the bind buffers to see they are filled with column data from the 1st row after SQLExecDirect before calling SQLFetch?

One more thing you might try is to move SQLBindCol after SQLExecDirect to see if that changes anything.

|||

Hi,

SQLExecDirect does not change the bind buffers.

Have moved the SQLBindCol calls before and and after the SQLExecDirect call both have the same effect. I also used SQLGetData instead of SQLBindCol and they also always miss the first row of data.

Keith

|||

Turns out that the Excel driver expects the first row to be the column names. There is supposed to be an option to turn this behavior off, but it has not worked in the past. I am not sure if it has been fixed. Here is the link to more information. http://support.microsoft.com/kb/288343/

If you have a row with column names in it, you can just include that in your range and you will be all set, otherwise, you should try the option, if that doesn't work, use the Jet engine as suggested in the above link.

Thanks

|||Thank you, adding headers does make it read all the lines. I had tried blank rows that has no effect. This is a reader for a user supplied file I have no control over.