Operation must use an updateable Query

Operation must use an updateable Query

• Lucian

  The answer is on the IMEX=1. When I deleted the portion IMEX=1 from the connection string it works fine. I am not sure what is the default value of IMEX.

  string excelConn = @"Provider=Microsoft.Jet.OLEDB.4.0;" +

  @"Data Source=" + _strExcelFilename + ";" +

  @"Extended Properties='Excel 8.0;IMEX=1;HDR=No;'" ;

  I should have sent the whole code including the connection string and you would have spotted the problem right away. Thanks Adamus.

  
  

• D.A.P.

  I am using OLEDB (not ODBC) with C# trying to write into Excel in local machine. Giving full control to the Excel file doesn't solve the problem. I don't use ASP or IIS at all. Thanks for your help Adamus but I haven't got any luck yet.
  

• DGK

  I also get this error with an Access database.

  Oddly, all works fine with classic ASP, but in .NET I get this error.

  In .Net I use :

  Dim ObjConnexion As New OleDbConnection(sCon)
  Dim ObjCommand As New OleDbCommand(sql, ObjConnexion)

  ObjConnexion.Open()
  
  Dim ObjTrans As OleDbTransaction
  ObjTrans = ObjConnexion.BeginTransaction(IsolationLevel.ReadCommitted)
  ObjCommand.Transaction = ObjTrans

  Try

  Dim retval As Integer = ObjCommand.ExecuteNonQuery()
  ObjTrans.Commit()

  Return retval

  Catch ex As Exception
  ObjTrans.Rollback()
  Throw (ex)

  Finally
  ObjConnexion.Close()

  End Try

  In ASP classic :

  set cnBD = server.CreateObject("ADODB.Connection")
  cnBD.open strConnection
  set rs = Server.CreateObject("ADODB.Recordset")
  rs.open strSql, cnBD, adOpenStatic
  

  The sql is :

  Insert into tbSaVilles (fdSubdivision, fdVille) Values ('3171', 'COOKSHIRE QC') ;
  

  Thank you for any help!

  
  

  
  

  
  
  

• Krampster

  Try this:

  @"UPDATE [Sheet1] SET Sheet1.Range("A1").Value = 'ABC'"

  Just a stab in the dark.

  Adamus

  
  
  

• Lucky:)

  I do not think INSERT allows to insert records into specified range inside of the Excel. I could be wrong, but I believe you could add new records to the end of the existing data. In this case your INSERT statement would look like

  INSERT INTO MySheetName (Column1) VALUES ('ABC')

  
  
  

• Patrick Burleson

  Issue
  

  The following error: 80004005 – Operation must use an updateable query – occurs on a page when an event is updating or inserting into a recordset.
  

  Solution
  

  There are several things that may cause this ODBC error. Many of the possible reasons are listed in the table below.
  

  	The permissions that are set on the directory the database is in. IUSR privileges must be set to "read/write".
  	The permissions on the database itself do not have full read/write privileges in effect.
  	This error can also occur when the database is located outside of the inetpub/wwwroot directory. Though the information is still able to be viewed and searched, it cannot be updated unless it is in the wwwroot directory.

  
  
  

• Gho5t

  I got syntax error when I played with it or its variations. Thanks.
  

• alejack-sw

  try to insert or update one field rather than multiple ones. ie

  Insert into tbSaVilles (fdSubdivision) Values ('3171')

  
  

• Jesus Ponce

  I just wanted to refresh this topic. All the help I've found up until this thread regarding the error was about permissions, which was not the problem. I also removed the 'IMEX=1' from my connection string and the problem was resolved.

  
  

• Stalwart team member

  Actually INSERT INTO seems to also act as UPDATE when connecting to Excel. It overwrites the existing data which doesn't sound safe.

  I was able to replicate the error in VB.NET and also able to resolve with some trial and error. See my posting above for the code.

  Adamus

  
  
  

• nonsence

  OK. I'm going to try to replicate this.

  In the meantime, let me ask an obvious question...Is the .xls document open or closed

  Adamus

  
  
  

• Mike 2^2

  I got it working in VB.NET but the syntax for the SQL query will be the same.

  Also...the .xls must be closed or you'll receive a general exception.

  Dim myConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

  "Data Source=F:\Documents and Settings\Adam\Desktop\MyOleDBTest.xls;" & _

  "Extended Properties=""Excel 8.0;HDR=Yes;"";"

  Dim myConnection As New System.Data.OleDb.OleDbConnection(myConnectionString)

  Dim myInsertQuery As String = "INSERT INTO [Sheet1$A1:A1] values ('MyTest')"

  Dim myCommand As New System.Data.OleDb.OleDbCommand(myInsertQuery)

  myCommand.Connection = myConnection

  myConnection.Open()

  myCommand.ExecuteNonQuery()

  myCommand.Connection.Close()

  Adamus

  
  
  
• Operation must use an updateable Query