PRB: DataGrid Not Populated Using Jet.OLEDB.4.0 Provider and ADO Server Side Cursor

• ActiveX Data Objects (ADO), versions 2.0, 2.01, 2.1
• Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, versions 5.0, 6.0

SYMPTOMS

When using the Microsoft.Jet.OLEDB.4.0 Provider and an ADO Server side cursor bound to the DataGrid (either through the ADO DataControl or directly to the grid), the data does not display in the DataGrid.

CAUSE

The Microsoft.Jet.OLEDB.4.0 Provider does not support the DBPROP_LITERALIDENTITY property, so the provider then needs to implement IRowsetIdentity. ADO should set this property before opening the rowset. If you ask for an interface (such as IRowsetIdentity), and OLE DB has not opened the rowset to support it, you will get the E_NOINTERFACE error back even if the provider supports it.

RESOLUTION

Set the RecordsetObject.Properties("IRowsetIdentity")= true before opening the recordset.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open a new standard Visual Basic project. Form1 is created by default.



2. In Project/References, set a reference to Microsoft ActiveX Data Objects 2.x Library.



3. Drag an ADO Data Control and a DataGrid onto Form1. These can be added from Project/Components.



4. Cut and paste the following code into the General Declarations section:




Dim cn AS ADODB.Connection
Dim rs as ADODB.Recordset 

5. Cut and paste the following code into the Form_Load Subroutine: (You might need to change the path to NWind database in the strConn variable.)




    Dim strConn As String
    Dim stcnQL As String
    
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\Nwind.mdb;"
    Set cn = New ADODB.Connection
    cn.Open strConn
    
    stcnQL = "SELECT * FROM customers"
    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = cn
        .CursorLocation = adUseServer
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
'        .Properties("IRowsetIdentity") = True 'remove comment to display data

        .Open stcnQL, , , , adCmdText
    End With
   
    Set Adodc1.Recordset = rs
    
    Set DataGrid1.DataSource = Adodc1.Recordset
 

6. Run the project. You should not see any data displayed in the DataGrid.



7. Remove the comment from the code line:

   
   .Properties("IRowsetIdentity")=True 

   The data should now display in the DataGrid.

Additional query words: empty

Keywords : kbADO kbOLEDB kbVBp kbVBp500 kbVBp600 kbGrpVBDB
Version : WINDOWS:2.0,2.01,2.1,5.0,6.0
Platform : WINDOWS
Issue type : kbprb