日期:2014-05-18  浏览次数:20849 次

急,求助:Excel导入SQL SERVER 2005我这代码错在哪?
C# code

protected void ButtonUserOK_Click(object sender, EventArgs e)
        {
            string saveDir = @"\Uploads\";
            string appPath = Request.PhysicalApplicationPath;
            if (FileUploadUser.HasFile)
            {
                string fileName = Server.HtmlEncode(FileUploadUser.FileName);
                string extension = System.IO.Path.GetExtension(fileName);
                if (extension == ".xls")
                {
                    string savePath = appPath + saveDir + fileName;
                    FileUploadUser.SaveAs(savePath);
                    string ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\教学工作量管理系统.mdf;Integrated Security=True;User Instance=True";
                    using (SqlConnection conn = new SqlConnection(ConnectionString))
                    {
                        string sql = "select * into 个人信息表 from opendatasource('Microsoft.Jet.OLEDB.4.0','Data Source=" + savePath + ";Extended Properties=Excel 8.0')...[Sheet1$]";
                        SqlCommand cmd = new SqlCommand(sql, conn);
                        try
                        {
                            conn.Open();
                            cmd.ExecuteNonQuery();
                            Response.Write("<script> window.alert('导入新系部人员信息成功!');</script>");
                            conn.Close();
                        }
                        catch (System.Exception ex)
                        {
                            Response.Write("<script> window.alert('导入新系部人员信息失败!出错信息:" + ex.Message + "');</script>");
                            conn.Close();
                        }
                        conn.Close();
                    }
                }
                else
                {
                    Response.Write("<script>window.alert('只能导入EXCEL文件!');</script>");
                    return;
                }
            }
            else
            {
                Response.Write("<script>window.alert('文件数据上传失败!');</script>");
                return;
            }
        }



貌似总是到了“cmd.ExecuteNonQuery();”就挂了...

------解决方案--------------------
应该是sql语句不对
 string sql = "select * into 个人信息表 from opendatasource('Microsoft.Jet.OLEDB.4.0','Data Source=" + savePath + ";Extended Properties=Excel 8.0')...[Sheet1$]";

Data Source=后面有双引号,如下
SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
------解决方案--------------------
探讨

引用:

应该是sql语句不对
string sql = "select * into 个人信息表 from opendatasource('Microsoft.Jet.OLEDB.4.0','Data Source=" + savePath + ";Extended Properties=Excel 8.0')...[Sheet1$]";

Data Source=……