OleDbDataAdapter and column casting while importing Excel to ASP.NET

I'm importing an excel using c#. But CustomerOrderN开发者_JAVA技巧R 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?

---

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.

---

Mixed datatypes in excel are always problematic.

Heres an answer that might help
OleDB & mixed Excel datatypes : missing data

Heres another nice explanation

http://munishbansal.wordpress.com/2009/12/15/importing-data-from-excel-having-mixed-data-types-in-a-column-ssis/

---

Look at your connection String. You may need to include ISAM=1 so excel knows to handle mixed columns as text.

---

if you have a predetermined list of fields and their expected datatypes, i believe, this should be the most maintainable approach:

create a xml schema as follows:

<?xml version="1.0" encoding="utf-16"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="Table">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Field1" type="xs:int" minOccurs="0" />
              <xs:element name="Field2" type="xs:string" minOccurs="0" />
              <xs:element name="Field3" type="xs:date" minOccurs="0" />
              <xs:element name="Field4" type="xs:double" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>

and read:

string query = "SELECT Field1, Field2, Field3, Field4 FROM [" + row["TABLE_NAME"].ToString() + "]";

DataSet ds = new DataSet();
ds.ReadXmlSchema(@".\MySchema.xsd");
OleDbDataAdapter data = new OleDbDataAdapter(query, Connection);
data.Fill(ds);
ds.Tables[0].TableName = row["TABLE_NAME"].ToString().Replace("$", string.Empty);`

the catch being that date format has to be valid for the system settings is in short dates as 'dd/mm/yy'

once the type is set, you can format as required.