FIX: Recordset.Update Causes Error Message: "Cursor operation conflict" to Occur When You Use Server Side Cursors and an INSTEAD OF Trigger

• Use ADO client-side cursors (adUseClient). -or-
  
  
• Perform the update by using an ADODB.Command object with an UPDATE SQL statement. For example:

  cmd.CommandText = "UPDATE MyTestTable set namefield='asdasdf' where [id] = 1"
  cmd.Execute
  					

Back to the top

Steps to Reproduce the Behavior

1. Use Query Analyzer to open a connection to the SQL Server 2000 pubs database, and then run the following Transact-SQL statement:

   USE Pubs
   GO
   Create Table TestTable ([ID] int NOT NULL, [NameField] varchar(50) NULL, RowGUID uniqueidentifier Default(NewID()))
   GO
   Insert Into TestTable ([ID], [NameField]) Values (1, 'test1')
   GO
   Insert Into TestTable ([ID], [NameField]) Values (2, 'test2')
   GO
   Insert Into TestTable ([ID], [NameField]) Values (3, 'test3')
   GO
   Create Trigger t_Test On TestTable Instead Of Update
   As
   WAITFOR DELAY '000:00:05'
   GO
    
   						

2. Create a Microsoft Visual Basic 6.0 Standard EXE and set a reference to ADO 2.6 (or ADO 2.5 or ADO 2.7).
3. Put a command button, named Command1, on a form, and then add the following code to the form:

   Dim conn As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   Dim strSQL As String
   
   Private Sub Command1_Click()
   
      conn.Open "Provider=SQLOLEDB;User ID=MyID;Initial Catalog=pubs;Data
      Source=MyServer;Password=MyPassword"
      rs.ActiveConnection = conn
      rs.CursorLocation = adUseServer
      rs.CursorType = adOpenStatic
      rs.LockType = adLockOptimistic
      strSQL = "Select * From TestTable Where [ID] = 1"
      rs.Open strSQL
      rs("NameField") = "incorrectValue"
   
      rs.Update   ' Error 'Cursor operation conflict' occurs here.
   
      rs.Close
      conn.Close
    
   End Sub
    
   					

   The error message shown in the "Symptoms" section occurs when rs.Update executes.

Back to the top