Operation must use an updateable query error message [Answered]RSS

• • Reply
  Bulldog248

  Member

  176 Points

  313 Posts

  Operation must use an updateable query error message

  Aug 03, 2015 03:21 PM|Bulldog248|LINK

  I'm using Visual Web Developer 2010 Express with SQL Server 2008 Express, VB.

  I'm getting the "Operation must use an updateable query" error message and don't have a clue what the problem is.

  The VB code:

          Dim cn As New OdbcConnection("Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\ACCESS DATABASE TEST\CRAP_HOME_USE_ACCESS-BLANK.mdb;user id=Admin; password=")
          Dim cmd1 As New OdbcCommand
          With cmd1
              .CommandText = querystring
              .Connection = cn
              .Connection.Open()
              .ExecuteNonQuery()
              .Connection.Close()
              .Dispose()
          End With
          cn.Dispose()

  The stack trace:

  [OdbcException (0x80131937): ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.]
     System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) +1232094
     System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) +1195
     System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) +67
     System.Data.Odbc.OdbcCommand.ExecuteNonQuery() +92
     AccessHomeUse.Button1_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\Crap\AccessHomeUse.aspx.vb:201
     System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118
     System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112
     System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
     System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
     System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
     System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

  The commandText (sorry, it's a long one):

  INSERT INTO [Resistors] ([PART_NUMBER], [Part Type], [Description], [Value], [PCB Footprint], [Schematic Part], [Number of Pins], [Operating Temperature Maximum], [Operating Temperature Minimum], [Package Size], [Package Height], [Package Type], [Company Part Status], [Temperature Coefficient], [Tolerance], [Rated Voltage], [Rated Power], [Material], [Number of Elements], [Rated Power Per Element], [Implementation], [Implementation Type], [PSpiceTemplate], [Device Type], [UserField01], [UserField02], [UserField03], [UserField04], [UserField05], [UserField06], [UserField07], [UserField08], [UserField09], [UserField10], [TMP Manufacturer], [TMP Manufacturer PN], [Activeparts ID], [CLASS], [LP Viewer], [RowID]) VALUES('MP010-042', 'SMD\5%\0805', 'RESISTOR, 470, 5%, 1/8W, 0805', '470', '', 'Resistor', '2', '', '', '0805', '', 'SMD', 'STL', '', '5%', '', '1/8W', '', '', '', '', '', '', '', 'A', '', '', '', 'SMT', '', '', 'NO', 'NO', '', '', '', '', '', '', '')

  Hopefully someone has an idea of what the problem is.  There are no dependencies/relationships in the Access database.

  Thanks

• • Reply
  hans_v

  All-Star

  25756 Points

  7025 Posts

  Re: Operation must use an updateable query error message

  Aug 03, 2015 05:38 PM|hans_v|LINK

  This error occurs when the user doesn't have modify rights on the folder where the mdb file is located. In .NET, when using mdb files, use the OleDb Namespace, not ODBC. Code examples:

  http://www.mikesdotnetting.com/article/26/parameter-queries-in-asp-net-with-ms-access

  and the connectionstring you need:

  http://www.mikesdotnetting.com/article/78/accessdatasource-sqldatasource-and-connecting-to-access-databases-in-asp-net

  And here are some tips on how to solve the error

  http://www.mikesdotnetting.com/article/74/solving-the-operation-must-use-an-updateable-query-error

• • Reply
  Bulldog248

  Member

  176 Points

  313 Posts

  Re: Operation must use an updateable query error message

  Aug 03, 2015 08:31 PM|Bulldog248|LINK

  Hi hans_v,

  I've changed my VB code as follows (using examples from your suggested links):

          Dim ConnString As String = Utils.GetConnString()
          Dim SqlString As String = querystring
          Using conn As New OleDbConnection(ConnString)
              Using cmd1 As New OleDbCommand(SqlString, conn)
                  cmd.CommandType = CommandType.Text
                  conn.Open()
                  cmd.ExecuteNonQuery()
              End Using
          End Using

  The sqlstring (querystring) has remained the same.

  The connectionstring is as follows:

      <add name="CRAP_HOME_USE_ACCESS" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|CRAP_HOME_USE_ACCESS-BLANK.mdb;"
        providerName="System.Data.OleDb" />

  Now, I get a different error message:

  ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
  

  Stack Trace:
  
  [InvalidOperationException: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.]
     System.Data.SqlClient.SqlConnection.GetOpenConnection(String method) +5096304
     System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command) +11
     System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) +144
     System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +105
     System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
     AccessHomeUse.Button1_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\Crap\AccessHomeUse.aspx.vb:221
     System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118
     System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112
     System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
     System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
     System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
     System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

  I think I'm getting closer to the solution but am still at a loss on what the problem is.  I would very much appreciate any ideas/suggestions offered.

  Thanks!

• • Reply
  Zhi Lv - MSF...

  All-Star

  44726 Points

  6713 Posts

  Microsoft

  Re: Operation must use an updateable query error message

  Aug 04, 2015 03:20 AM|Zhi Lv - MSFT|LINK

  Hi Bulldog248,

  Bulldog248

         Dim ConnString As String = Utils.GetConnString()
          Dim SqlString As String = querystring
          Using conn As New OleDbConnection(ConnString)
              Using cmd1 As New OleDbCommand(SqlString, conn)
                  cmd.CommandType = CommandType.Text
                  conn.Open()
                  cmd.ExecuteNonQuery()
              End Using
          End Using

  ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

  From the error message, the issue relates the connection is close.

  By default, before calling the ExecuteNonQuery method, we need to open the connection using Open () method. If you are using the above code, the connection string should be open, I suggest you could check the connection state before calling the ExecuteNonQuery method. You could refer to the following code:

         Dim ConnString As String = Utils.GetConnString()
          Dim SqlString As String = querystring
          Using conn As New OleDbConnection(ConnString)
              Using cmd1 As New OleDbCommand(SqlString, conn)
                  cmd.CommandType = CommandType.Text
                  conn.Open()
                  
                  Dim state = con.State.ToString()
  
                  cmd.ExecuteNonQuery()
              End Using
          End Using

  You could set a breakpoint to debug the code and check the state.

  Best Regards,
  Dillion

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
  
• • Reply
  Bulldog248

  Member

  176 Points

  313 Posts

  Re: Operation must use an updateable query error message

  Aug 04, 2015 08:14 AM|Bulldog248|LINK

  Dillon,

  Thanks for the suggestion.  When I look, the state is Open.  The error message says open and available, could there be an issue with availability that I'm overlooking or don't understand?

  Thanks!

• • Reply
  hans_v

  All-Star

  25756 Points

  7025 Posts

  Re: Operation must use an updateable query error message

  Aug 04, 2015 10:50 AM|hans_v|LINK

  Bulldog248

  Using cmd1 As New OleDbCommand(SqlString, conn)

  You declared the command as cmd1

  Bulldog248

  cmd.ExecuteNonQuery()

  But you execute the command cmd, which isn't declared so this command isn't attached to the connection....

  Change

  Using cmd1 As New OleDbCommand(SqlString, conn)

  to

  Using cmd As New OleDbCommand(SqlString, conn)

• • Reply
  Bulldog248

  Member

  176 Points

  313 Posts

  Re: Operation must use an updateable query error message

  Aug 04, 2015 11:23 AM|Bulldog248|LINK

  Hi hans_v

  Thanks for your good eyes!  Such a small mistake, such big problems.  That solved it!