使用ASP.NET上传Excel工作表并存储mysql数据库时出错 [英] Error in uploading a excel sheet and store mysql database using asp.net

问题描述

错误::
命令执行期间遇到致命错误.

Error::
Fatal error encountered during command execution.

Con.Open();
        string path = fileuploadExcel.PostedFile.FileName;
       // fileuploadExcel.SaveAs(Server.MapPath("orders.xlsx"));
        
        string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("orders.xlsx") + ";Extended Properties=Excel 12.0;Persist Security Info=False";
        
        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
        
        DataTable dt = new DataTable();
        excelConnection.Open(); 
        try
        {           

             dt.Columns.AddRange(new DataColumn[11] { new DataColumn("Date", typeof(DateTime)),
                new DataColumn("Ref_No", typeof(string)),
                new DataColumn("Partys_Name",typeof(string)),
               new DataColumn("Pending", typeof(float)),
        new DataColumn("<30 days ", typeof(float)),
        new DataColumn("30to60days", typeof(float)),
        new DataColumn("60 to 90 days", typeof(float)),
        new DataColumn("90 to 120 days", typeof(float)),
        new DataColumn("(> 120 days )", typeof(float)),
        new DataColumn("Post-Dated", typeof(string)),
        new DataColumn("Final", typeof(float))});
             OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelConnection);
             OleDbDataReader dReader;
             dReader = cmd.ExecuteReader();

            // create a connection string with your sql database

            //insert excel data in student table
             MySqlCommand cm = new MySqlCommand("insert into tbl_upload values(@Date,@Ref_No,@Partys_Name,@Pending,@<30 days,@30to60days,@60 to 90 days,@90 to 120 days,@(> 120 days ),@Post-Dated,@Final)", Con);

            cm.Parameters.AddWithValue("Date", "Date");

            cm.Parameters.AddWithValue("Ref_No", "Ref_No");

            cmd.Parameters.AddWithValue("Partys_Name", "Partys_Name");

            cm.Parameters.AddWithValue("<30 days ", "less30days");
             cm.Parameters.AddWithValue("30to60days", "bet30to60days");
             cm.Parameters.AddWithValue("60 to 90 days", "bet60to90days");
             cm.Parameters.AddWithValue("90 to 120 days", "bet90to120days");
             cm.Parameters.AddWithValue("(> 120 days )", "greater120days");
             cm.Parameters.AddWithValue("Post-Dated", "Posted_Dated");
            cm.Parameters.AddWithValue("Final", "Final");

            int i = cm.ExecuteNonQuery();

            if (i > 0)

            {

                Label2.Text = "Data inserted successfully";

            }           

        }       

            catch (Exception ex)

        {
                Label2.Text =ex.Message;
            }
        excelConnection.Close();
        Con.Close();
    }

推荐答案

"，excelConnection)； OleDbDataReader dReader; dReader = cmd.ExecuteReader(); // 与您的sql数据库创建连接字符串 // 在学生表中插入excel数据 MySqlCommand cm = 新 MySqlCommand(" ，骗局)； cm.Parameters.AddWithValue(" ，" 日期")； cm.Parameters.AddWithValue(" ，" Ref_No")； cmd.Parameters.AddWithValue(" ，" Partys_Name")； cm.Parameters.AddWithValue(" ，" )； cm.Parameters.AddWithValue(" ，" bet30to60days")； cm.Parameters.AddWithValue(" ，" )； cm.Parameters.AddWithValue(" ，" )； cm.Parameters.AddWithValue(" ， 更大的120天")； cm.Parameters.AddWithValue(" ， " )； cm.Parameters.AddWithValue(" ，" 最终")； int i = cm.ExecuteNonQuery(); 如果(i > 0 ) { Label2.Text = " ; } } 捕获(例外) { Label2.Text = ex.Message; } excelConnection.Close(); Con.Close(); }

", excelConnection); OleDbDataReader dReader; dReader = cmd.ExecuteReader(); // create a connection string with your sql database //insert excel data in student table MySqlCommand cm = new MySqlCommand("insert into tbl_upload values(@Date,@Ref_No,@Partys_Name,@Pending,@<30 days,@30to60days,@60 to 90 days,@90 to 120 days,@(> 120 days ),@Post-Dated,@Final)", Con); cm.Parameters.AddWithValue("Date", "Date"); cm.Parameters.AddWithValue("Ref_No", "Ref_No"); cmd.Parameters.AddWithValue("Partys_Name", "Partys_Name"); cm.Parameters.AddWithValue("<30 days ", "less30days"); cm.Parameters.AddWithValue("30to60days", "bet30to60days"); cm.Parameters.AddWithValue("60 to 90 days", "bet60to90days"); cm.Parameters.AddWithValue("90 to 120 days", "bet90to120days"); cm.Parameters.AddWithValue("(> 120 days )", "greater120days"); cm.Parameters.AddWithValue("Post-Dated", "Posted_Dated"); cm.Parameters.AddWithValue("Final", "Final"); int i = cm.ExecuteNonQuery(); if (i > 0) { Label2.Text = "Data inserted successfully"; } } catch (Exception ex) { Label2.Text =ex.Message; } excelConnection.Close(); Con.Close(); }

您忘记为"Pending"参数添加"Parameters.AddWithValue"行，我可以看到此处和此处为字符串添加了一些空格...似乎您需要验证是否需要这些代码并相应地更新代码.

完成后...让我们知道它是否有效(或新的错误消息).谢谢.

You forgot to add "Parameters.AddWithValue" line for "Pending" parameter and I can see some whitespaces added here and there for strings...It seems you need to verify if those needed and update code accordingly.

Once that done...let us know if it works (or new error message). Thanks.

这篇关于使用ASP.NET上传Excel工作表并存储mysql数据库时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！