PRB: WillExecute Event Cannot Be Used to Change CursorType

PSS ID Number: Q173463

Article Last Modified on 01-11-2001

• Microsoft Visual Basic Learning Edition for Windows 5.0, 6.0
• Microsoft Visual Basic Professional Edition for Windows 5.0, 6.0
• Microsoft Visual Basic Enterprise Edition for Windows 5.0, 6.0

Symptoms

If the WillExecute event is used to set the CursorType property, the resultset for the cursor may contain nothing if the CursorType has not been set before the WillExecute event fires. Setting a Remote Data Control resultset equal to the query resultset will cause the error:

"runtime error" 40516 Invalid Object"

Cause

The CursorType property is set but will only affect subsequent queries. The CursorType property is not set for the query that caused the WillExecute event to fire.

Resolution

Set the CursorType and LockType properties before running the query.

Status

This behavior is by design.

More Information

The purpose of the WillExecute event is to allow modifications of a query's SQL or to allow a query to be cancelled. All query properties can be modified here, but changing some properties will not affect the current query that caused the event to fire. CursorType and LockType are two properties that cannot be changed for the current query. Changing other properties, such as SQL and maxrow, will affect the resultset returned by the current query that caused the event to fire.

Steps to Reproduce Behavior

1. Create a new project. Select Microsoft Remote Data Object 2.0 under project references.



2. Under project components, check Microsoft Databound Grid and Microsoft RemoteData Control 2.0.



3. Add a Remote Data Control, a Databound grid and a CommandButton to the form. Set the Datasource property of the grid to MSRDC1.



4. Put the following code in the Declarations section of the form:
   
   NOTE: Before running the code, change the server in the connect statement to a server near you.

         Option Explicit
         Dim rs As rdoResultset
         Dim WithEvents cn As rdoConnection
         Dim SQL As String
   
         Private Sub cn_WillExecute(ByVal Query As RDO.rdoQuery, _
                Cancel As Boolean)
           MsgBox Query.CursorType
           Query.CursorType = rdOpenStatic
           MsgBox Query.CursorType   'the cursortype has been changed
         End Sub
   
         Private Sub Form_Load()
           Set cn = New rdoConnection
   
           cn.Connect = "ucn.Connect = "uid=sa;pwd=;server=margerys;" _
           & "driver={SQL & "driver={SQL Server};database=pubs;" _
           & "DSN='';" & "DSN='';"
   
           cn.CursorDriver = rdUseOdbc
           cn.EstablishConnection rdDriverNoPrompt
           SQL = "Select *  From titles "
           '
           ' Openresultset cause the WillExecute event to fire.
           '
           Set rs = cn.OpenResultset(SQL)
           '
           'The next command will fail because a remote data control
           '  cannot be assigned to nothing. rs is nothing because
           '  because the CursorType has not been set for the current query.
           '
           'Set rs = cn.OpenResultset(SQL,rdopenforwardonly)
           '
           'Uncomment the above line and comment the first 'set rs' and
           '  the problem will persist because a remote data control cannot
           '  be assigned to a forwardonly cursor.
           '
           Set MSRDC1.Resultset = rs
         End Sub
   
         Private Sub quit_Click()
           rs.Close
           Set rs = Nothing
           cn.Close
           Set cn = Nothing
           Unload Me
         End Sub 




5. In the code above, if you switch openstatic and openforwardonly, note that the code will run and the grid will be filled. You can also experiment with the LockType property, changing it from rdConcurReadOnly to rdConcurRowVer, and testing to note changes when you can edit the grid.

References

For information on WillExecute, please see the following:

Visual Basic Online Books, and the following article in the Microsoft Knowledge Base: