OleDbDataAdapter and column casting while importing Excel to ASP.NET

c# sql casting oledbdataadapter
uzay95 picture uzay95 · Mar 17, 2011 · Viewed 11.8k times · Source

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?

Answer

Nils Magne Lunde picture Nils Magne Lunde · Mar 17, 2011

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.

Related questions

Why is a SQL float different from a C# float

Howdy, I have a DataRow pulled out of a DataTable from a DataSet. I am accessing a column that is defined in SQL as a float datatype. I am trying to assign that value to a local variable (c# float …

Read More
How to get float value with SqlDataReader?

In my database, I have NextStatDistanceTime value as a float. When "float time = reader.GetFloat(0);" line excecuted, it gives an error of system invalid cast exception How can I get float value from sql command in this code? Here is …

Read More
Why does this cast from short to int fail?

We have some code that archives data from a Microsoft Access database into a MS SQL Server database. Assuming we have a data reader already populated from the Access table and we are adding a parameter to a SqlCommand in …

Read More