Getting Data from Excel Spreadsheet

[jlrosenberg24]

I am trying to read data from an Excel table into a DataSet however I get an error: "No value given for one or more required parameters." I've done some research and learned that this error usually occurs when a required value is empty ("" in the case of a string) or null. Below is my code.

[COLOR=#000000][FONT=Consolas]cnExcel [/FONT][/COLOR][COLOR=#000000][FONT=Consolas]=[/FONT][/COLOR][COLOR=#2B91AF][FONT=Consolas]New[/FONT][/COLOR][COLOR=#2B91AF][FONT=Consolas]System[/FONT][/COLOR][COLOR=#000000][FONT=Consolas].[/FONT][/COLOR][COLOR=#2B91AF][FONT=Consolas]Data[/FONT][/COLOR][COLOR=#000000][FONT=Consolas].[/FONT][/COLOR][COLOR=#2B91AF][FONT=Consolas]OleDb[/FONT][/COLOR][COLOR=#000000][FONT=Consolas].[/FONT][/COLOR][COLOR=#2B91AF][FONT=Consolas]OleDbConnection[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]([/FONT][/COLOR][COLOR=#000000][FONT=Consolas] _[/FONT][/COLOR]
              [COLOR=#800000]"provider=Microsoft.Jet.OLEDB.4.0; "[/COLOR] & _
              [COLOR=#800000]"data source="[/COLOR] & [COLOR=#2B91AF]FileName[/COLOR] & [COLOR=#800000]"; "[/COLOR] & _
              [COLOR=#800000]"Extended Properties=Excel 8.0;"[/COLOR])
cdImport = [COLOR=#2B91AF]New[/COLOR] [COLOR=#2B91AF]System[/COLOR].[COLOR=#2B91AF]Data[/COLOR].[COLOR=#2B91AF]OleDb[/COLOR].[COLOR=#2B91AF]OleDbDataAdapter[/COLOR]( _
              [COLOR=#800000]"select * from ["[/COLOR] & cSheetName & [COLOR=#800000]"$] order by StoreID, ItemID"[/COLOR], cnExcel)
dsImport = [COLOR=#2B91AF]New[/COLOR] [COLOR=#2B91AF]System[/COLOR].[COLOR=#2B91AF]Data[/COLOR].[COLOR=#2B91AF]DataSet[/COLOR]
cdImport.[COLOR=#2B91AF]Fill[/COLOR](dsImport) 
[COLOR=#000000][FONT=Consolas]cnExcel[/FONT][/COLOR][COLOR=#000000][FONT=Consolas].[/FONT][/COLOR][COLOR=#2B91AF][FONT=Consolas]Close[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]()
[/FONT][/COLOR]

The error is occuring on cdImport.Fill(dsImport) presumably because there is nothing to fill dsImport with.
After doing some debugging I find that the Server Version field of cnExcel is: error: an excpetion of type: {System.InvalidOperationException} occured
What am I doing wrong?

 

[jmcilhinney]

I would suggest that you first just create the connection and call Open and Close on it to confirm that that part works. I suspect that it may not be. You should visit www.connectionstring.com and compare your connection string to what they recommend for Excel.

Can I also confirm that you're using an XLS file and not an XLSX?

If connection does work then I would suggest removing your ORDER BY clause and see if the query works then. The thing is, if your connection is even working, you're not specifying that the sheet contains column headers so it probably wouldn't know what StoreID and ItemID mean.

 

[jlrosenberg24]

I would suggest that you first just create the connection and call Open and Close on it to confirm that that part works. I suspect that it may not be. You should visit www.connectionstring.com and compare your connection string to what they recommend for Excel.

Can I also confirm that you're using an XLS file and not an XLSX?

If connection does work then I would suggest removing your ORDER BY clause and see if the query works then. The thing is, if your connection is even working, you're not specifying that the sheet contains column headers so it probably wouldn't know what StoreID and ItemID mean.

Hi, thanks for the response.

I have confirmed that my connection string is correct per the website. I can open and close the connection without any problems.

You are correct in that I am using an XLS file.

The default behavior is that that "HDR=YES" or that the sheet contains column headers. To be sure though, I have tested the program with that added in, with no success. I have also tested the program without the ORDER BY clause with no success.