Asked by:
conversion failed when converting date and/or time from character string. in asp.net c#

Question
-
User63199524 posted
When importing excel column Date in to sql Table i am getting error like this :"conversion failed when converting date and/or time from character string. in asp.net c#"
My Code:
protected void btnUploadAll_Click(object sender, EventArgs e)
{
System.Data.DataTable dt = null;
HttpFileCollection filesColl = Request.Files;
foreach (string uploader in filesColl)
{
HttpPostedFile file = filesColl[uploader];
if (file.FileName != "")
{string path = string.Concat(Server.MapPath("~/ImportDocument/" + file.FileName));
file.SaveAs(path);OleDbConnection Oleconnection = new OleDbConnection("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + path + "; Extended Properties=Excel 12.0;");
Oleconnection.Open();
dt = Oleconnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);String[] excelSheets = new String[dt.Rows.Count];
int i = 0;foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}for (int j = 0; j < excelSheets.Length; j++)
{OleDbCommand cmdd = new OleDbCommand("Select StudentId,Date1,TimeIn,TimeOut,TotalTime,FirstName,LastName,ClassNumber from [" + excelSheets[j] + "]", Oleconnection);
//OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", Oleconnection);
OleDbDataAdapter OledbDA = new OleDbDataAdapter();
// Oleconnection.Close();
// Create DbDataReader to Data Worksheet
OleDbDataReader oledr = cmdd.ExecuteReader();
SqlDataReader dr = null;// SQL Server Connection String
SqlConnection sqlConnectionString = new SqlConnection("Data Source=.;Initial Catalog= DB_Student_Report_Management; Integrated Security=True");
sqlConnectionString.Open();
string ins = "insert into Attendance2 (StudentId,Date1,TimeIn,TimeOut,TotalTime,FirstName,LastName,ClassNumber) values (@C1, @C2,@C3,@C4,@C5,@C6,@C7,@C8)";
string sel = "select * from Attendance2 where Date1=@C2 and FirstName=@C6 and LastName=@C7 and ClassNumber=@C8 ";
while (oledr.Read())
{
try
{
SqlCommand cmd = new SqlCommand(sel, sqlConnectionString);
cmd.Parameters.AddWithValue("@C2", oledr["Date1"]);
cmd.Parameters.AddWithValue("@C6", oledr["FirstName"]);
cmd.Parameters.AddWithValue("@C7", oledr["LastName"]);
cmd.Parameters.AddWithValue("@C8", oledr["ClassNumber"]);
dr = cmd.ExecuteReader();
if (dr.Read())
{
dr.Close();
continue;
}
dr.Close();
SqlCommand cmd1 = new SqlCommand(ins, sqlConnectionString);
cmd1.Parameters.AddWithValue("@C1", oledr["StudentId"]);
cmd1.Parameters.AddWithValue("@C2", oledr["Date1"]); /// getting error like conversion failed when converting date and/or time from character string. in asp.net c#
cmd1.Parameters.AddWithValue("@C3", oledr["TimeIn"]);
cmd1.Parameters.AddWithValue("@C4", oledr["TimeOut"]);
cmd1.Parameters.AddWithValue("@C5", oledr["TotalTime"]);
cmd1.Parameters.AddWithValue("@C6", oledr["FirstName"]);
cmd1.Parameters.AddWithValue("@C7", oledr["LastName"]);
cmd1.Parameters.AddWithValue("@C8", oledr["ClassNumber"]);
dr = cmd1.ExecuteReader();
dr.Close();
}
catch (Exception ex)
{}
}
// Bulk Copy to SQL Server
//SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);
//bulkInsert.DestinationTableName = "Students";
//bulkInsert.WriteToServer(dr);
//Oleconnection.Close();
sqlConnectionString.Close();
Oleconnection.Close();
Array.ForEach(Directory.GetFiles((Server.MapPath("~/ImportDocument/"))), File.Delete);
Label1.ForeColor = Color.Green;
Label1.Text = "Successfully inserted";
}
}
else
{
Label1.ForeColor = Color.Red;
Label1.Text = "Please select the file.File should be only excel";
}
}
}My Excel has following column :
StudentId Photo Date1 TimeIn TimeOut TotalTime FirstName LastName ClassNumber E-mail Address 1 14-08-20 9:05 AM 12:15 PM 3h 10m Colorado Mayra 4009 nshruthi4)@gmail.com 2 14-08-20 9:02 AM 9:46 PM 12h 44m Field Joshua 4009 nshruthi4)@gmail.com 3 14-08-20 10:16 AM 12:35 PM 2h 18m HAMIDI HESSARI ZIBA 4009 nshruthi4)@gmail.com Please help me to solve this problem as soon possible :)
Thanks in Advance :)
Monday, January 5, 2015 2:29 PM
All replies
-
User269602965 posted
Try adding IMEX settings
OleDbConnection Oleconnection = new OleDbConnection("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + path + "; Extended Properties=Excel 12.0;IMEX=1;");
Use IMEX=1 to force Excel to produce text output
and then you explicitly convert the text TO_DATE (Oracle) / CONVERT (MSSQL) using the DD-MM-RR date format layout in the INSERT VALUES SQL statement.
Wednesday, January 7, 2015 7:04 PM -
User-1716253493 posted
cmd1.Parameters.AddWithValue("@C2", DateTime.ParseExact(oledr["Date1"].ToString(), "yy-MM-dd",null));
Wednesday, January 7, 2015 8:42 PM -
User-271186128 posted
Hi Shruthin,
cmd1.Parameters.AddWithValue("@C2", oledr["Date1"]); /// getting error like conversion failed when converting date and/or time from
As for this issue, I suggest you could set a break point to check the value. You could try to use the Convert method to convert the value.
Here are some articles, you could refer to them.
OLE DB Data Type Mappings: http://msdn.microsoft.com/en-us/library/cc668759(v=vs.110).aspx
Convert.ToDateTime Method: http://msdn.microsoft.com/en-us/library/system.convert.todatetime(v=vs.110).aspx
Best Regards,
DillionWednesday, January 14, 2015 5:48 AM