ADO cursorType/lockType changes automatically

• Johnny Doyle

  Right there with Babe

  Points: 745

  More actions

  January 27, 2004 at 6:40 am

  #65379

  I don't know if this is the correct forum for this query (apologies if it isn't), but I'm experiencing a problem when opening an ADODB.Recordset that selects records from a SQL view that uses a table-type UDF in its definition.

  When I open keyset/lockOptimistic recordsets that select records from other views it works fine, but if the view definition uses a table-type UDF the cursorType changes to forwardOnly, and the lockType changes to readOnly.

  Eg (simplified version - the UDF does do a lot more really):

  CREATE FUNCTION dbo.myUDF()

  RETURNS @results TABLE (id integer)

  AS

  BEGIN

      INSERT @results (id) VALUES(1)

      RETURN

  END

  CREATE VIEW dbo.myView

  AS

      SELECT myTable.*

      FROM myTable

      WHERE myTable.id IN (SELECT id FROM dbo.myUDF())

  Does anyone know how I can open a keysey/lockOptimistic ADO recordset that selects records from such a view WITHOUT it automatically changing to forwardOnly/lockReadOnly? CursorLocation is server-side if that helps.

  Thanks

• Johnny Doyle

  Right there with Babe

  Points: 745

  More actions

  January 27, 2004 at 9:02 am

  #491773

  More information :

  • CursorLocation is adUseServer (recordset works fine if opened as a client-side cursor, but I really need it to be server-side)
  • SQLOLEDB is the provider for my ADO connection

  Thanks