Selecting individual fields from an Excel spreadsheet through oledb

Question

Hi All

I am currently attempting to read values from an excel spreadsheet to be manipulated in code and eventually stored in a db. My current select statement reads SELECT * FROM [sheet1$] and works just fine, however, the application I am writing will be operating asyncronously and there may be many instances open at once, all reading different spreadsheets. Because of this I am trying to limit my select statement in the event that somebody sends me 500 spreadsheets with 500 fields each when I only actually need 30 or so. So, I am trying to be specific in my select statement thus SELECT name, address1, address2 from [sheet1$] but when I execute this command through oledb I am getting a No value given for one or more required parameters OleDbException. If I replace my specified fields with * it works fine. I know the fields I am requesting exist as I created the spreadsheet myself. Does anybody have any ideas what might be going wrong?

TYIA

Paul

John Penn · Answer

Try adding the clause HDR=Yes to your connection string.

Paul Hardy · Answer

Yes it does have header information and my connection string is formatted appropriately. If I do a select * I can pull back the individual fields by name using reader[FieldName] I just can't seem to specify them in the select statement itself. It's most perplexing.

John Penn · Answer

The first row of the spreadsheet should contain the names of the fields. Is this the case with your spreadsheet?