The given value of type string from the data source cannot be converted to type datetime of the specified target column.

See more:

ASP.NET

Hey i am trying to import data from excel to sql server , it works fine till i am not passing date but now i want to pass the date to sql server it provides error as The given value of type String from the data source cannot be converted to type datetime of the specified target column.
Anyone has logic or please suggest me what can i do to ..



My DataTime Column Name Is--RegDate, LRegDate ,DOB, CreatedDate,UpdatedDate

What I have tried:

using System;
using System.Data;
using System.IO;
using System.Data.OleDb;
using System.Configuration;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
OleDbConnection Econ;
SqlConnection con;

string constr, Query, sqlconn;
protected void Page_Load(object sender, EventArgs e)
{

}
private void ExcelConn(string FilePath)
{

constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", FilePath);
//constr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"");
Econ = new OleDbConnection(constr);

}
private void connection()
{
sqlconn = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
con = new SqlConnection(sqlconn);

}


private void InsertExcelRecords(string FilePath)
{
string path = string.Concat(Server.MapPath("~/Excel/" + FileUpload1.FileName));
FileUpload1.SaveAs(path);
ExcelConn(path);

Query = string.Format("Select IDRId, Reg_Type,RegUnder,RegNo,RegDate,CourseId,Other_Course,LRegDate,CouncilId,FirstName,MiddleName,LastName,Full_Name,Category,Other_Category,FatherName, MotherName, Gender, Domicile,DOB,BirthPlace,ResidentialAddress, ProfessionalAddress,CurrentCity,CurrentState,ResidentialCity,Mobile,OfficialTelephone,Email ,Adhar_Card ,PanNo, IsDeleted,CreatedDate,UpdatedDate FROM [{0}]", "Sheet1$");

OleDbCommand Ecom = new OleDbCommand(Query, Econ);
Econ.Open();

DataSet ds = new DataSet();
OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
Econ.Close();
oda.Fill(ds);
DataTable Exceldt = ds.Tables[0];
connection();
//creating object of SqlBulkCopy
SqlBulkCopy objbulk = new SqlBulkCopy(con);
//assigning Destination table name
objbulk.DestinationTableName = "IDRDetails";
//Mapping Table column
objbulk.ColumnMappings.Add("IDRId", "IDRId");
objbulk.ColumnMappings.Add("Reg_Type", "Reg_Type");
objbulk.ColumnMappings.Add("RegUnder", "RegUnder");
objbulk.ColumnMappings.Add("RegNo", "RegNo");
objbulk.ColumnMappings.Add("RegDate", "RegDate");
objbulk.ColumnMappings.Add("CourseId", "CourseId");
objbulk.ColumnMappings.Add("Other_Course", "Other_Course");
objbulk.ColumnMappings.Add("LRegDate", "LRegDate");
objbulk.ColumnMappings.Add("CouncilId", "CouncilId");
objbulk.ColumnMappings.Add("FirstName", "FirstName");
objbulk.ColumnMappings.Add("MiddleName", "MiddleName");
objbulk.ColumnMappings.Add("LastName", "LastName");
objbulk.ColumnMappings.Add("Full_Name", "Full_Name");
objbulk.ColumnMappings.Add("Category", "Category");
objbulk.ColumnMappings.Add("Other_Category", "Other_Category");
objbulk.ColumnMappings.Add("FatherName", "FatherName");
objbulk.ColumnMappings.Add("MotherName", "MotherName");
objbulk.ColumnMappings.Add("Gender", "Gender");
objbulk.ColumnMappings.Add("Domicile", "Domicile");
objbulk.ColumnMappings.Add("DOB", "DOB");
objbulk.ColumnMappings.Add("BirthPlace", "BirthPlace");
objbulk.ColumnMappings.Add("ResidentialAddress", "ResidentialAddress");
objbulk.ColumnMappings.Add("ProfessionalAddress", "ProfessionalAddress");
objbulk.ColumnMappings.Add("CurrentCity", "CurrentCity");
objbulk.ColumnMappings.Add("CurrentState", "CurrentState");
objbulk.ColumnMappings.Add("ResidentialCity", "ResidentialCity");
objbulk.ColumnMappings.Add("Mobile", "Mobile");
objbulk.ColumnMappings.Add("OfficialTelephone", "OfficialTelephone");
objbulk.ColumnMappings.Add("Email", "Email");
objbulk.ColumnMappings.Add("Adhar_Card", "Adhar_Card");
objbulk.ColumnMappings.Add("PanNo", "PanNo");
objbulk.ColumnMappings.Add("IsDeleted", "IsDeleted");
objbulk.ColumnMappings.Add("CreatedDate", "CreatedDate");
objbulk.ColumnMappings.Add("UpdatedDate", "UpdatedDate");
//inserting Datatable Records to DataBase
con.Open();
objbulk.WriteToServer(Exceldt);
con.Close();

}
protected void Button1_Click(object sender, EventArgs e)
{
string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);
InsertExcelRecords(CurrentFilePath);
}
}