After load from Execl

[ad]

I use the code below to load a Excel into dataset:
I
he data in Excel is all number, and the field type of dataset is treat as
float field.
How can I treat them as string?


//-------------------------------------------------------------------------------------
DataSet ds = new DataSet();

string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data
Source=" + savePath +
"; Extended Properties=" +
(char)34 + "Excel 8.0;IMEX=1;" + (char)34;
string sSelect = "SELECT * FROM [Data$]";
OleDbConnection objConn = new OleDbConnection(sConnectionString);
objConn.Open();
OleDbCommand objCmdSelect = new OleDbCommand(sSelect, objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
objAdapter1.Fill(ds, "XLData");
objConn.Close();

 

[Paul Clement]

¤ I use the code below to load a Excel into dataset:
¤ I
¤ he data in Excel is all number, and the field type of dataset is treat as
¤ float field.
¤ How can I treat them as string?
¤
¤
¤ //-------------------------------------------------------------------------------------
¤ DataSet ds = new DataSet();
¤
¤ string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data
¤ Source=" + savePath +
¤ "; Extended Properties=" +
¤ (char)34 + "Excel 8.0;IMEX=1;" + (char)34;
¤ string sSelect = "SELECT * FROM [Data$]";
¤ OleDbConnection objConn = new OleDbConnection(sConnectionString);
¤ objConn.Open();
¤ OleDbCommand objCmdSelect = new OleDbCommand(sSelect, objConn);
¤ OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
¤ objAdapter1.SelectCommand = objCmdSelect;
¤ objAdapter1.Fill(ds, "XLData");
¤ objConn.Close();
¤

You can use the CStr function in your SQL statement:

Select CStr(F1) AS Col1, CStr(F2) AS Col2 from [Data$]

The only other alternative is to perform the conversion in C#, after the data has been retrieved
into the DataTable.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[ad]

Sir,
Thank for your help!
But how can I perform the conversion in C#, after the data has been
retrieved
into the DataTable.?

 

[ad]

I use the code to perform the conversion, but fail with
Cannot change DataType of a column once it has data.
How can I do?

DataTable dt = ds.Tables0];
int iColCount = dt.Columns.Count;
for (int j = 0; j < iColCount; j++)
{
DataColumn myDC = dt.Columns[j];
myDC.DataType = System.Type.GetType("System.String");
}

Sir,
Thank for your help!
But how can I perform the conversion in C#, after the data has been
retrieved
into the DataTable.?

?I use the code below to load a Excel into dataset:
?I
?he data in Excel is all number, and the field type of dataset is treat
as
?float field.
?How can I treat them as string?
?
?
?
//-------------------------------------------------------------------------------------
? DataSet ds = new DataSet();
?
? string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data
?Source=" + savePath +
? "; Extended Properties=" +
? (char)34 + "Excel 8.0;IMEX=1;" + (char)34;
? string sSelect = "SELECT * FROM [Data$]";
? OleDbConnection objConn = new OleDbConnection(sConnectionString);
? objConn.Open();
? OleDbCommand objCmdSelect = new OleDbCommand(sSelect, objConn);
? OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
? objAdapter1.SelectCommand = objCmdSelect;
? objAdapter1.Fill(ds, "XLData");
? objConn.Close();
?

You can use the CStr function in your SQL statement:

Select CStr(F1) AS Col1, CStr(F2) AS Col2 from [Data$]

The only other alternative is to perform the conversion in C#, after the
data has been retrieved
into the DataTable.


Paul
~~~~
Microsoft MVP (Visual Basic)