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

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 You receive unexpected errors when using ADO and ADO MD in a .NET Framework application

This article refers to the following Microsoft .NET Framework Class Library namespace:

System.Data.OleDb

IN THIS TASK

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.

Requirements

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

Create the Project

Follow these steps to create a new Windows Application project in Visual Basic .NET:

Start Visual Studio .NET.

On the File menu, point to New, and then click Project.

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:

On the Project menu, click Add Reference.

In the Add Reference dialog box, click the .NET tab.

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")

'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.

References

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