locked
conversion failed when converting date and/or time from character string. in asp.net c# RRS feed

  • 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,
    Dillion

    Wednesday, January 14, 2015 5:48 AM