BUG: Error Message Received When You Use the DTPicker to Update Table

When you use the DTPicker to update a table, you may receive the following error message if an intrinsic control is also updating the table: If just the DTPicker is doing an update, no error is returned, but the table is not updated.

OCXs are not supplying pre-Update notifications, such as intrinsic controls.

Issue a recordset.move 0 or use a databinding collection.

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

OCX controls (DTPicker) in general wait for a pre-Update notification before they issue an IRowset::SetData for the Provider to update the data. This is not supported in providers because it would cause re-reentrancy problems during pending updates. As a result, it waits for an event that does not occur when using server-side cursors or it issues a re-entrancy error message.

The client-side cursors allow an IRowset::SetData within the client's update call; as a result, it works against client-side cursors. Additionally, because all providers support pre-Move notifications, an operation such as Move(0) also fires the event that causes the IRowset::SetData to happen when using server-side cursors.

Steps to Reproduce Behavior

1. Create a Standard EXE project in Visual Basic. Form1 is created by default.
2. On the Project menu, click Components, and then register the following controls with the project:Microsoft Windows Common Controls-2 6.0.
   
   
3. On Project References, select Microsoft ActiveX Data Objects 2.6 Library. Be sure to deselect Microsoft ActiveX Data Objects 2.5 if it is selected.
4. On the default form (Form1), add the following controls:
   • Frame1 with two options buttons inside:
     Name=Option1
     caption= Access
     
     Name=Option2
     caption= SQL Server
     
   • Two command buttons:
     
     Name=cmdopen
     caption = Open Database
     
     Name= cmdupdate
     Caption = Update
     
   • One text box:
     
     Name= text1
     text=
     
   • One DTPicker:
     
     Name= DTPicker1
5. Add the following code to the form.
   
   Note You must change User ID =<UID> and password =<strong password> to the correct values before you run this code. Make sure that <UID> has the appropriate permissions to perform this operation on the database.

   Option Explicit
   ' For SQL Server, change the Data source,
   ' Initial catalogue, user name and password here
   Const SQLConnect As String = "Data Source = margerysp2w2k; Initial Catalog=northwind;user id=<uid>;password=<strong password>;"
   'For Access, change the path to correctly point to the Northwind.MDB
   Const AccessConnect As String = "Data Source=E:\Office2000sr1\Office\Samples\northwind.MDB"
   Const TableName As String = "orders"
   'Providers
   Const AccessProvider As String = "Microsoft.Jet.OLEDB.4.0"
   Const SQLProvider As String = "SQLOLEDB"
   Dim Cn As ADODB.Connection
   Dim RS As ADODB.Recordset
   
   Private Sub cmdopen_Click()
   Set Cn = New ADODB.Connection
   'Set up connection depending on option chosen
   If Option1.Value Then   ' Access is selected by default
   With Cn
     .CursorLocation = adUseServer
     .ConnectionString = AccessConnect
     .Provider = AccessProvider
     .Open
   End With 'Access
   Else
   With Cn
     .ConnectionString = SQLConnect
     .Provider = SQLProvider
     .Open
   End With 'SQL Server
   End If
   '   Open the record set, and bind
   Set RS = New ADODB.Recordset
   RS.Open "Select * from " & TableName, Cn, adOpenKeyset, adLockOptimistic, adCmdText
   '   Now bind
   With Text1
     .DataField = "shipcountry"
     Set .DataSource = RS
   End With
   With DTPicker1
      .DataField = "ShippedDate"
      Set .DataSource = RS
   End With 'datepicker
   'Bind using the BindingCollection.
   'Set bc = New BindingCollection
   'Set bc.DataSource = RS
   'bc.Add DTPicker1, "value", "ShippedDate"
   'bc.Add Text1, "Text", "ShippedCountry"
   End Sub
   
   'Bind using the BindingCollection.
   'Set bc = New BindingCollection
   'Set bc.DataSource = RS
   'bc.Add DTPicker1, "value", "ShippedDate"
   'bc.Add Text1, "Text", "ShippedCountry"
   
   Private Sub cmdupdate_Click()
   
   With RS
      .Update
      .Close
   End With 'RS
   Cn.Close
   End Sub
   
   Private Sub Form_Load()
    Option1.Value = True
   End Sub
   					

6. Modify the General Declarations sections as indicated in the code comments.
7. Test the preceding code by running it with either Microsoft SQL Server or Microsoft Access. Click Open Database.
8. Change the Country name only, and then click Update. Note that the Country name is updated.
9. Change just the date in the DTPicker, and then click Update. Note that the datebase is updated and closed. Click Open Database again, and note that the date is not changed.
10. Change both the Country and the date, click Update, and note that the error message cited in the "Symptoms" section is displayed.
11. Uncomment the followng lines:

    Set bc = New BindingCollection
    Set bc.DataSource = RS
    bc.Add DTPicker1, "value", "ShippedDate"
    bc.Add Text1, "Text", "ShippedCountry"
    						

    Comment out the following lines:

    'With Text1
    '  .DataField = "ShippedCountry"
    '  Set .DataSource = RS
    'End With
    'With DTPicker1
    '   .DataField = "ShippedDate"
    '   Set .DataSource = RS
    'End With 'datepicker
    						

    and repeat step 9. Both fields should be updated successfully.

For additional information about OCX update problems, click the article number below to view the article in the Microsoft Knowledge Base: