Article ID: 310349 - Last Review: June 15, 2006 - Revision: 3.2

How to use the OleDbDataAdapter to fill a DataSet object from an ADO Recordset object in Visual Basic .NET

View products that this article applies to.

This article was previously published under Q310349

Caution ADO and ADO MD have not been fully tested in a Microsoft .NET Framework environment. They may cause intermittent issues, especially in service-based applications or in multithreaded applications. The techniques that are discussed in this article should only be used as a temporary measure during migration to ADO.NET. You should only use these techniques after you have conducted complete testing to make sure that there are no compatibility issues. Any issues that are caused by using ADO or ADO MD in this manner are unsupported. For more information, see the following article in the Microsoft Knowledge Base:

840667 (http://support.microsoft.com/kb/840667/ ) You receive unexpected errors when using ADO and ADO MD in a .NET Framework application

SUMMARY

This step-by-step article demonstrates how to use the OleDbDataAdapter class to obtain rows from an ADO Recordset object and insert them into a DataSet object. Note that you cannot do the reverse; that is, you cannot obtain rows from a DataSet object and insert them into an ADO Recordset object.

Back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:

Microsoft Windows XP Professional, Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows NT 4.0 Server
Microsoft Visual Studio .NET
Microsoft OLE DB Provider for Jet

Back to the top

Create the Project

Follow these steps to create a new Windows Application project in Visual Basic .NET:
1. Start Visual Studio .NET.
2. On the File menu, point to New, and then click Project.
3. In the New Project dialog box, click Visual Basic Projects under Project Types, click Windows Application under Templates, and then click OK.
Follow these steps to add a reference to ADODB in your project:
1. On the Project menu, click Add Reference.
2. In the Add Reference dialog box, click the .NET tab.
3. Click adodb from the Component Name list, click Select, and then click OK.
  
  NOTE: This adds a reference to a primary interop assembly (PIA) so that you can use earlier versions of Microsoft ActiveX Data Objects (ADO) in a Visual Studio .NET application. For more information, see the REFERENCES section.
Add a Button control and a DataGrid control to the form.
Switch to the Code window, and then add the following code to the top of the Code window:
Imports System.Data.OleDb

Add the following code to the Button1_Click event:

        'This code uses the Northwind.mdb database to create an ADODB Recordset. 
        'You must modify the path so that it points to the database on your system.
        'You can also use a Recordset that is returned from another location or component.

        Dim MyRs As New ADODB.Recordset()
        Dim MyCn As New ADODB.Connection()
        MyCn.Open("Provider=Microsoft.jet.oledb.4.0;data source=d:\northwind.mdb")

        MyRs.Open("Select * from Customers", MyCn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)

        'Create and fill the DataSet from the Recordset. Populate the grid from the DataSet. 
        Dim myDA As OleDbDataAdapter = New OleDbDataAdapter()
        Dim myDS As DataSet = New DataSet()
        myDA.Fill(myDS, MyRs, "MyTable")
        DataGrid1.DataSource = myDS.Tables(0)

        'Close ADODB objects.
        'Note that the OleDbDataAdapter.Fill overload that takes
        'a DataTable and an ADO object implicitly calls Close on 
        'the ADO object when the Fill operation is complete.

        MyRs = Nothing
        MyCn.Close()
        MyCn = Nothing

Make sure to change the location of the Northwind database in the code to point to the Northwind database on your system.
Press F5 to run the code.

Back to the top

REFERENCES

For additional information about the ADODB primary interop assembly, click the article number below to view the article in the Microsoft Knowledge Base:

318559 (http://support.microsoft.com/kb/318559/EN-US/ ) INFO: Using the Primary Interop Assembly for ADO (ADODB) in Visual Studio .NET

For more information, see the following topic in the Visual Studio .NET Help documentation:

OleDbDataAdapter.Fill Method
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.fill.aspx (http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.fill.aspx)

For general information about ADO.NET or Visual Basic .NET, see the following MSDN newsgroups:

microsoft.public.dotnet.framework.adonet (http://go.microsoft.com/fwlink/?linkid=5819)

microsoft.public.dotnet.languages.vb (http://go.microsoft.com/fwlink/?linkid=5820)

Back to the top