Hi,
I am facing a problem in uploading Excel data to the Database. While
uploading my code reads Excel Data using OleDbReader and store into a dataset
by looping through the OleDbReader result set. The problem is, if I give the
date as mm/dd/yyyy format it accepts, but when I give the date in dd/mm/yyyy
format, upto 8 rows of data it is reporting the date as invalid. After 8th
row even if the date is there (dd/mm/yyyy format), it is reporting like Date
field is blank (the OleDbReader itself is reading the date as blank.) I am in
a crucial situation to get a solution for this problem. Can anyone please
help me.
Thank You,
Regards,
N.Ramakrishnan
//Following is the Code I use to read Excel Data using OleDbReader
string prop = "\"Excel 8.0;IMEX=1;HDR=YES\"";
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
strConn = strConn + filePath + ";";
strConn = strConn + "Extended Properties=";
strConn = strConn + prop;
OleDbConnection xlCon = new OleDbConnection(strConn);
xlCon.Open();
string selStmt = "SELECT * FROM [" + sheetName + "]";
OleDbCommand xlCmd = new OleDbCommand(selStmt, xlCon);
xlCmd.CommandType = CommandType.Text;
OleDbDataReader xlReader = xlCmd.ExecuteReader();
//Create a structure for the dataset
for(short fldIndex = 0; fldIndex < xlReader.FieldCount; fldIndex++)
{
xlDataSet.Tables[0].Columns.Add(new DataColumn(xlReader.GetName(fldIndex),
Type.GetType("System.String")));
}
DataRow dRow;
while(xlReader.Read())
{
dRow = xlDataSet.Tables[0].NewRow();
for(short fldIndex = 0; fldIndex < xlReader.FieldCount; fldIndex++)
{
dRow[fldIndex] = Convert.ToString(xlReader[fldIndex]);
}
xlDataSet.Tables[0].Rows.Add(dRow);
}
xlReader.Close();