急,求助: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
[解决办法]