Mixed Datatype problem in Excel

[monajoshi]

Dear All

I Have written this piece of code to fill the dataset with the values in Excel

Dim DS As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:quote.XLS; " & _
"Extended Properties=Excel 8.0;IMEX=1;HDR=Yes")
 
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [pr$]", MyConnection)

DS = New System.Data.DataSet

MyCommand.Fill(DS)

Problem:

My Excel file contains all text values.While editing my Excel file the numeric data which was text changes to numeric.

After which when i execute this code it gives an error in the fill statement,An unhandled exception of type "'System.Data.OleDb.OleDbException' occurred in system.data.dll without any additional statement"
due to the mixed datatype in Excel file eventhough i have mentioned the parameter IMEX=1

Please Help

 

[zekeman]

I'm new here but here is a long shot found with google

http://blogs.acceleration.net/ryan/archive/2005/01/11/477.aspx

also see the site
http://www.connectionstrings.com

 

[JohnH]

Yes, at least the connection strings path is wrong and the three part extended properties must be quoted.

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:[B][COLOR=darkgreen]\[/COLOR][/B]quote.XLS; " & _
"Extended Properties=[COLOR=darkgreen][B]""[/B][/COLOR]Excel 8.0;IMEX=1;HDR=Yes[COLOR=darkgreen][B]""[/B][/COLOR]")