当我将数据从excel上传到sql表列时，它会产生如下错误。 [英] When I am uploading data from excel to sql table columns it is generating an error like below.

问题描述

Microsoft Office Access数据库引擎无法打开或写入文件''。它已经由另一个用户专门打开，或者你需要获得查看和写入数据的权限。





可能是什么原因造成的这个？





这是我的代码：

The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.


What may be the cause for this?


This was my code:

SqlConnection con=new SqlConnection();
       con.ConnectionString  = ConfigurationManager.ConnectionStrings["db"].ToString();
       con.Open();
        string path = fileuploadExcel.PostedFile.FileName;
       
       string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
        excelConnection.Open();
        OleDbCommand cmd = new OleDbCommand("Select [Question],[Choice1],[Choice2],[Choice3],[Choice4],[Ans],[Category] from [Sheet1$]", excelConnection);
       
OleDbDataReader dReader;
dReader = cmd.ExecuteReader(); SqlBulkCopy sqlBulk = new SqlBulkCopy(con);
 

    sqlBulk.ColumnMappings.Add("Question", "Qn");
    sqlBulk.ColumnMappings.Add("Choice1", "option1");
    sqlBulk.ColumnMappings.Add("Choice2", "option2");
 
    sqlBulk.ColumnMappings.Add("Choice3", "option3");
    sqlBulk.ColumnMappings.Add("Choice4", "option4");
    sqlBulk.ColumnMappings.Add("Ans", "ans");
    sqlBulk.ColumnMappings.Add("Category", "Q_Type");
    sqlBulk.DestinationTableName = "Questions";
    sqlBulk.WriteToServer(dReader);

excelConnection.Close();

[/ EDIT]

以上内容来自OP的评论 - Maciej Los

推荐答案

，excelConnection）;

OleDbDataReader dReader;
dReader = cmd.ExecuteReader（）; SqlBulkCopy sqlBulk = new SqlBulkCopy（con）;


sqlBulk.ColumnMappings.Add（ 问题， Qn）;
sqlBulk.ColumnMappings.Add（ Choice1， option1）;
sqlBulk.ColumnMappings.Add（ Choice2， option2）;

sqlBulk.ColumnMappings。添加（ Choice3， < span class =code-string> option3）;
sqlBulk.ColumnMappings.Add（ Choice4， option4）;
sqlBulk.ColumnMappings.Add（ Ans， ans）;
sqlBulk.ColumnMappings.Add（ Category， Q_Type）;
sqlBulk.DestinationTableName = 问题;
sqlBulk.WriteToServer（dReader）;

excelConnection.Close（）;

", excelConnection); OleDbDataReader dReader; dReader = cmd.ExecuteReader(); SqlBulkCopy sqlBulk = new SqlBulkCopy(con);   sqlBulk.ColumnMappings.Add("Question", "Qn"); sqlBulk.ColumnMappings.Add("Choice1", "option1"); sqlBulk.ColumnMappings.Add("Choice2", "option2");   sqlBulk.ColumnMappings.Add("Choice3", "option3"); sqlBulk.ColumnMappings.Add("Choice4", "option4"); sqlBulk.ColumnMappings.Add("Ans", "ans"); sqlBulk.ColumnMappings.Add("Category", "Q_Type"); sqlBulk.DestinationTableName = "Questions"; sqlBulk.WriteToServer(dReader); excelConnection.Close();

[/ EDIT]

以上内容来自OP的评论 - Maciej Los

它适用于此路径：C：\Program Files（x86）\ Common Files \ microsrosoft shared\DevServer



意味着你需要关注



你需要删除只读复选框检查你的文件夹的属性，如果这也没有工作尝试使您的特定路径的Drive允许权限允许完全控制属性中的用户权限。

Its working for this path : C:\Program Files (x86)\Common Files\microsoft shared\DevServer

Means you need to follow

You need to remove Read Only Check box check from properties of your folder and if that also doesn't work try to make that Drive allow permission for your particular path allowed to full control in user permissions in properties.

这篇关于当我将数据从excel上传到sql表列时，它会产生如下错误。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！