Article ID: 288772
Article Last Modified on 11/5/2003
APPLIES TO
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.5 Service Pack 1
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
This article was previously published under Q288772
SYMPTOMS
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.
CAUSE
OCXs are not supplying pre-Update notifications, such as intrinsic controls.
RESOLUTION
Issue a recordset.move 0 or use a databinding collection.
STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.
MORE INFORMATION
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
- Create a Standard EXE project in Visual Basic. Form1 is created by default.
- On the Project menu, click Components, and then register the following controls with the project:Microsoft Windows Common Controls-2 6.0.
- 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.
- 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
- Frame1 with two options buttons inside:
Add the following code to the form.
Note You must change User ID =<UID> and 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
- Modify the General Declarations sections as indicated in the code comments.
- Test the preceding code by running it with either Microsoft SQL Server or Microsoft Access. Click Open Database.
- Change the Country name only, and then click Update. Note that the Country name is updated.
- 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.
- Change both the Country and the date, click Update, and note that the error message cited in the "Symptoms" section is displayed.
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.
REFERENCES
For additional information about OCX update problems, click the article number below to view the article in the Microsoft Knowledge Base:
195638 PRB: IRowsetNotify Error with ADO Data Control and ADO Recordset
Keywords: kbbug kbdatabase kbctrl kbpending KB288772