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

SUMMARY

Requirements

• 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

Create the Project

1. 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.
2. 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.
3. Add a Button control and a DataGrid control to the form.
4. Switch to the Code window, and then add the following code to the top of the Code window:

   Imports System.Data.OleDb
   					

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

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