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 :)
