Help and Support
 

powered byLive Search

How to use ADO Recordsets in Visual Basic .NET

View products that this article applies to.
Article ID:315974
Last Review:November 28, 2007
Revision:5.0
This article was previously published under Q315974
On This Page

SUMMARY

This article explains how to create a small console application that uses COM Interop to create an earlier version (legacy) ADO RecordSet, convert it to an ADO.NET DataSet, and then display the record count. You will see how easy it is to use COM components from within Microsoft Visual Studio .NET.

Back to the top

Requirements

You need the following elements to perform the procedures in this article:
•A Microsoft Windows 2000 Professional (or Windows 2000 Server) or Microsoft Windows XP Professional-based system with the .NET Framework installed.
•A general familiarity with ADO and ADO.NET.

Back to the top

Use COM components from Visual Studio .NET

1.Start Visual Studio .NET.
2.Click New Project, click Visual Basic Projects, and then select Console Application. Name the application ComDemo, and then click OK.
3.When the Project is created, ensure that the Solution Explorer is visible. If it is not, press CTRL+ALT+L.
4.Before you add code to Sub Main(), add a reference to the COM component that you will be using. In the Solution Explorer, right-click References under ComDemo, and then click Add Reference. On the COM tab, select Microsoft ActiveX Data Objects 2.5 Library. Click Select. Your selection should be displayed in the Selected Components list box. Click OK. You should now see ADODB listed under References in the Web application.
5.Module1.vb should be open in the editor window. If it is not, double-click this file in the Solution Explorer. Now that you have a reference to a legacy ADO component, its full capabilities are at your disposal. Additionally, Visual Studio .NET provides full IntelliSense support for COM objects.
6.The first few lines of code create and open a connection. Type the following immediately below the Sub Main() line of the module:

Note Uid <user name> must have the appropriate permissions to perform these operations on the database.

Dim cn As New ADODB.Connection()
cn.ConnectionString = "provider=sqloledb;server=localhost;database=northwind;uid=<username>"
cn.Open()
7.Next, create an instance of an ADO RecordSet, setting the cursor and lock properties. To do this, add the following to the existing code: 
Dim rs As New ADODB.RecordSet()
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
NOTE: This should be very familiar code if you have experience with legacy ADO. The difference is that you are now working with it from within .NET.
8.Open a RecordSet by passing in an ad hoc SQL statement and the connection object:
rs.Open("select * from products", cn)
9.Disconnect the RecordSet, and then close out the connection:
rs.ActiveConnection = Nothing
cn.Close()
10.You now have a disconnected RecordSet. To make this a little more interesting, and to make the RecordSet fully usable within a .NET application, convert it to an ADO.NET DataSet by using the OleDbDataAdapter class:
Dim da As New System.Data.OleDb.OleDbDataAdapter()
Dim ds As New DataSet()
da.Fill(ds, rs, "products")
11.Finally, add the last two lines of code to write the total number of rows in the DataSet to the console:
Console.Write("There are " & ds.Tables(0).Rows.Count.ToString & " total products.")
Console.ReadLine()

Back to the top

Complete code display (Module1.vb)

Module Module1

    Sub Main()
        Dim cn As New ADODB.Connection()
        cn.ConnectionString = "provider=sqloledb;server=(localhost);database=northwind;uid=<username>"
        cn.Open()

        Dim rs As New ADODB.RecordSet()
        rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
        rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
        rs.Open("select * from products", cn)
        rs.ActiveConnection = Nothing
        cn.Close()

        Dim da As New System.Data.OleDb.OleDbDataAdapter()
        Dim ds As New DataSet()
        da.Fill(ds, rs, "products")

        Console.Write("There are " & ds.Tables(0).Rows.Count.ToString & " total products.")
        Console.ReadLine()
    End Sub

End Module

Back to the top

Verify that it works

1.Press F5 to run the application in debug mode.
2.After a brief pause you should see the following:
There are 77 total products.
NOTE: This number may vary if you have altered the Northwind database.
3.Press ENTER to exit the console application and return to Visual Studio .NET.

Back to the top

Troubleshooting

You may have to modify the connection string to run this application -- specifically the server name. Also, although provider=sqloledb is not normally needed for .NET applications, in this case you do need it because .NET defaults to ODBC for legacy ADO.

Back to the top

REFERENCES

For more information about exposing COM components to the .NET Framework, visit the following Microsoft Web site:
http://msdn2.microsoft.com/en-us/library/z6tx9dw3(vs.71).aspx (http://msdn2.microsoft.com/en-us/library/z6tx9dw3(vs.71).aspx)
For more information about advanced COM Interop, visit the following Microsoft Web site:
http://msdn2.microsoft.com/en-us/library/bd9cdfyx(vs.71).aspx (http://msdn2.microsoft.com/en-us/library/bd9cdfyx(vs.71).aspx)

Back to the top

APPLIES TO
•Microsoft Visual Basic .NET 2002 Standard Edition
•Microsoft Visual Basic .NET 2003 Standard Edition

Back to the top

Keywords: 
kbhowtomaster kbinfo KB315974

Back to the top

Article Translations

 

Other Support Options

  • Need More Help?
    Contact a Support professional by Email, Online or Phone.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.