操作必须使用可更新的查询。 ASP.NET与EXCEL DB

I am trying to add data from asp grid view to my excel sheet. I am using Excel as backend db but getting an error - "System.Data.OleDb.OleDbException: Operation must use an updateable query." I have checked for the access permissions on the file as well removed the IMEX=2 from the connection string but no success.

我正在尝试将数据从asp网格视图添加到我的Excel工作表中。我使用Excel作为后端数据库，但收到错误 - “System.Data.OleDb.OleDbException：Operation必须使用可更新的查询。”我已经检查了文件的访问权限，并从连接字符串中删除了IMEX = 2但没有成功。

Here's what I tried:

这是我尝试过的：

 private static string path = @"C:\Manish\ExcelUI\NameValuePair.xlsx"; //+ FileUpload1.FileName
    //Coneection String by default empty  
    private static string ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0; ReadOnly=False; HDR=Yes;\"";
GridViewRow row = (GridViewRow)gvExcelFile.Rows[e.RowIndex];
        TextBox tName = (TextBox)gvExcelFile.FooterRow.Cells[0].FindControl("txtName1");
    TextBox tValue = (TextBox)gvExcelFile.FooterRow.Cells[1].FindControl("txtValue1");
    string nm = tName.Text;
    string val = tValue.Text;
         conn.Open();
    OleDbCommand cmd1 = new OleDbCommand("INSERT INTO [Sheet1$] " + "([Name],[Value]) " + "VALUES(@value1, @value2)", conn);
    cmd1.Parameters.AddWithValue("@value1", nm);
    cmd1.Parameters.AddWithValue("@value2", val);
    cmd1.ExecuteNonQuery();
    conn.Close();
    gvbind();

1 个解决方案

#1

I am using excel as database. Finally the problem is resolved as far as the insert operation is concerned. The problem was i guess with the access of the folder and the drive in which the excel file was saved.

我使用excel作为数据库。最后，就插入操作而言，问题得到了解决。我猜这个问题是访问文件夹和保存excel文件的驱动器。

Now I am facing issues with the update operation. I do not have a unique(primary) key in the excel sheet. This is the error I get - "System.Data.OleDb.OleDbException: Syntax error in UPDATE statement." Please suggest changes if you find any.

现在我面临更新操作的问题。我在Excel工作表中没有唯一的（主）键。这是我得到的错误 - “System.Data.OleDb.OleDbException：UPDATE语句中的语法错误。”如果您发现任何变化，请提出建议。

Here's what I tried:

这是我尝试过的：

    GridViewRow row = (GridViewRow)gvExcelFile.Rows[e.RowIndex];
    TextBox TxtEditName = (TextBox)row.FindControl("TxtEditName");
    TextBox TxtEditValue = (TextBox)row.FindControl("TxtEditValue");

    string enm = TxtEditName.Text;
    string evl = TxtEditValue.Text;
    gvExcelFile.EditIndex = -1;
    conn.Open();

    string editquery = "UPDATE [Sheet1$] set Name=? ,Value=?";
    OleDbCommand editcmd = new OleDbCommand(editquery, conn);
    editcmd.Parameters.AddWithValue("Name", enm);
    editcmd.Parameters.AddWithValue("Value", evl);
    editcmd.ExecuteNonQuery();
    conn.Close();
    gvbind();