Data type mismatch for datetime format

Nov 13, 2011 at 4:18 PM

I keep getting this error :

"String was not recognized as a valid DateTime."

 when I try to import my spreadsheet into list. There is column with date dd/mm/yyyy format in excel sheet.

So I open the "SpreadSheet.aspx" located in "_layouts/SpreadSheet/SpreadSheet.aspx" and go to the this code :

    private void insertXlDataToSPList(String XlSPLists)
    {
        System.Data.DataSet dsXldata = new System.Data.DataSet();
        dsXldata = getXldata();
      
        String[] XlSpArray = XlSPLists.Split(';');
        SPList List = getSPList(spListGuid);
        int xlrow = 0;
        bool isRowEmpty;
        foreach (System.Data.DataRow dr in dsXldata.Tables[0].Rows)
        {
            xlrow += 1;
            isRowEmpty = true;
            try
            {
                SPListItem listitems = List.Items.Add();

                foreach (string myString in XlSpArray)
                {
                    string[] values = myString.Split('-');


                    if (listitems != null)
                    {
                        spColumnName = values[0];
                        xlColumnName = values[1];

                        if (dr[values[1]].ToString().Trim() != String.Empty)
                        {
                            isRowEmpty = false;
                            if (listitems.Fields[values[0]].GetType().Name == "SPFieldDateTime")
                            {
                                DateTime tempDateTime;
                                if (DateTime.TryParse(dr[values[1]].ToString(), out tempDateTime))
                                    listitems[values[0]] = tempDateTime.ToShortDateString();
                            }
                            else
                                listitems[values[0]] = dr[values[1]];  //values[1] --> Excelcolumn and values[0]---> SPfield name
                        }
                    }

                }
                if (!isRowEmpty) listitems.Update();
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message); //---Add this code to catch whats the problem.
	       Response.End();
                litValidationMessage.Text = " No. of Rows copied to SpList: " + (xlrow - 1) + "<br>" +
                                            " Type mismatch between the columns:" + " " + spColumnName + " - " + xlColumnName + " at row " + (xlrow + 1);
                ClientScript.RegisterStartupScript(GetType(), "key", "document.getElementById('light').style.display='block';document.getElementById('fade').style.display='block';", true);
                throw ex;


            }
        }
    }
What could be the problem as it mention this error message?
How do I fix this? I already check & keep the format between ExcelSheet & Sharepoint List is same.