OleDbDataAdapter and column casting while importing Excel to ASP.NET
Solution 1
Look at your connection String. You may need to include ISAM=1 so excel knows to handle mixed columns as text.
Solution 2
I'm not sure how you can force the datatype in the SQL, but you could chage the datatype afterwards in the dataset by cloning the dataset schema, and copy the data with ImportRow.
But rather you should probably change the datatype in Excel to string if you want to treat it as string.
Solution 3
Mixed datatypes in excel are always problematic.
Heres an answer that might help
OleDB & mixed Excel datatypes : missing data
Heres another nice explanation
Comments
-
uzay95 over 1 year
I'm importing an excel using c#. But CustomerOrderNR column contains these kind values:
20283 20213 20625 50749-50 30687 31975 82253
But when I execute these codes:
string QTam = @"SELECT Date, [Serial Number], [Status Code], Description, CustomerOrderNR FROM [Sheet1$]"; DataSet ds = new DataSet(); OleDbDataAdapter cmd = new OleDbDataAdapter(QTam, strConn); cmd.Fill(ds, "orders");
It is returning CustomerOrderNR column in System.Double data type. I want to change datatable column type but then it return exception like:
Cannot change DataType of a column once it has data.
Can't I change DataType of Column from Double to String in SQL statement?