当我插入inot SQL我有这个错误：-error.conversion从字符串转换日期和/或时间时失败 [英] When I insert inot SQL I have this error :-error.conversion failed when converting date and/or time from character string

问题描述

我在excel表中的列包含类似的日期格式（

i have column in excel sheet contain date format like that(

31.12.2016

）



我怎么样我在sql中选择它后插入这种格式

这是我在mvc5中的代码。

)

How i insert this format after i select it into table in sql
this is my code in mvc5.

[HttpPost]
public ActionResult Import(HttpPostedFileBase FileUpload)
{
    //bool fileChoosed = false;
    DataSet ds = new DataSet();
    if (FileUpload != null)
    {
        if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        {
            string filename = FileUpload.FileName;
            string targetpath = Server.MapPath("~/fileUpload/");

            FileUpload.SaveAs(targetpath + filename);

            string pathToExcelFile = targetpath + filename;
            var connectionString = "";
            if (filename.EndsWith(".xls"))
            {
                connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", pathToExcelFile);
            }
            else if (filename.EndsWith(".xlsx"))
            {
                connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", pathToExcelFile);
            }

            OleDbConnection excelConnection = new OleDbConnection(connectionString);
            excelConnection.Open();
            DataTable dt = new DataTable();

            dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                return null;
            }

            String[] excelSheets = new String[dt.Rows.Count];
            int t = 0;
            //excel data saves in temp file here.
            foreach (DataRow row in dt.Rows)
            {
                excelSheets[t] = row["TABLE_NAME"].ToString();
                t++;
            }
            OleDbConnection excelConnection1 = new OleDbConnection(connectionString);


            string query = string.Format("Select * from[{0}]", excelSheets[0]);
            using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
            {
                dataAdapter.Fill(ds);
            }
            excelConnection.Close();
            if (System.IO.File.Exists(pathToExcelFile))
            {
                System.IO.File.Delete(pathToExcelFile);
            }

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {


                                    string conn = ConfigurationManager.ConnectionStrings["WWMCONNDB"].ConnectionString;
                                    SqlConnection con = new SqlConnection(conn);
                                    // //if customer code found
                                    string s1 = @"SELECT COUNT(*) FROM DATA_CUSTOMERS WHERE customer_id = @customer_id";
                                    SqlCommand sCommand = new SqlCommand(s1, con);
                                    sCommand.Parameters.AddWithValue("@customer_id", ds.Tables[0].Rows[i][11]);
                                    con.Open();
                                    int records = (int)sCommand.ExecuteScalar();
                                    con.Close();
                                    if (records == 0)
                                    {
                                        string s2 = @"SELECT COUNT(*) FROM DATA_NOTIFICATIONS WHERE notification_id = @notification_id";
                                        SqlCommand sCommand2 = new SqlCommand(s2, con);
                                        sCommand2.Parameters.AddWithValue("@notification_id", ds.Tables[0].Rows[i][1].ToString());
                                        con.Open();
                                        int records2 = (int)sCommand.ExecuteScalar();
                                        con.Close();
                                        if (records2 == 0)
                                        {
                                            string Insertquery = "Insert into DATA_CUSTOMERS(customer_id,customer_name,region_id,city_name,address,tele1,tele2) Values('" +
                                            ds.Tables[0].Rows[i][11] + "','" + ds.Tables[0].Rows[i][6].ToString() +
                                            "','" + ds.Tables[0].Rows[i][3] + "', '" +
                                            ds.Tables[0].Rows[i][4].ToString() + "','" +
                                            ds.Tables[0].Rows[i][5].ToString() + "','" +
                                            ds.Tables[0].Rows[i][7].ToString() + "','" +
                                            ds.Tables[0].Rows[i][8].ToString() + "'); insert into DATA_NOTIFICATIONS(notification_id,notificationtype_id,customer_id,notification_date,problem_desc) Values('" +
                                            ds.Tables[0].Rows[i][1] + "','" + ds.Tables[0].Rows[i][0] + "','" +
                                            ds.Tables[0].Rows[i][11] + "','" +
                                            //DateTime.ParseExact(ds.Tables[0].Rows[i][9].ToString(), new string[] { "dd/MM/yyyy", "dd.MM.yyyy" }, null, DateTimeStyles.AssumeLocal) + "','" + problem here //
                                            ds.Tables[0].Rows[i][10].ToString() + "');";
                                            con.Open();
                                            SqlCommand cmd = new SqlCommand(Insertquery, con);
                                            cmd.ExecuteNonQuery();
                                            con.Close();
                                            //fileChoosed = true;
                                            TempData["Success"] = "Succes";
                                        }

            }
        }

    }



    return View();
}

我尝试过：



我如何转换日期格式（

What I have tried:

How i convert date format (

31.12.2016

）

推荐答案

将你的日期值分配给一个变量



string datefromexel ='你从exel字段的日期';

assign your date value to a variable

string datefromexel='your date from exel field';

DateTime dt=DateTime.ParseExact(datefromexel, "dd/MM/yyyy", CultureInfo.InvariantCulture);

这篇关于当我插入inot SQL我有这个错误：-error.conversion从字符串转换日期和/或时间时失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！