How to open ADO Connection and Recordset objects by using Visual Basic .NET

Article translations Article translations
Article ID: 308047 - View products that this article applies to.
This article was previously published under Q308047
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
Expand all | Collapse all

On This Page

Summary

This article demonstrates different ways to create ActiveX Data Objects (ADO) Connection and Recordset objects in Visual Basic .NET. Note that these objects are ADO objects and not ADO.NET objects.


Create ADO Connection and Recordset Objects in Visual Basic .NET

  1. Create a new Visual Basic .NET Windows Application project. Form1 is created by default.
  2. From the Project menu, click Add Reference.
  3. On the COM tab, click Microsoft ActiveX Data Objects 2.x Library, and then click Select.
  4. Add the following code to the General Declarations section of Form1:
    Dim cn As New ADODB.Connection()
    Dim rs As New ADODB.Recordset()
    Dim cnStr As String
    Dim cmd As New ADODB.Command()
    					
  5. The following code illustrates two different methods to open an ADO Connection object. Each of these methods connect to a Microsoft SQL Server Pubs database and can be added to the Form1 Load event.

    Note User ID <user name> must have permissions to perform these operations on the database.
    ' Modify this connection string to reflect your server and logon information.
    ' Store the connection to a variable to be used throughout this example.
    cnStr = "Provider=SQLOLEDB;Initial Catalog=Pubs;Data Source=servername;" & _
       "User ID=<username>;Password=<strong password>;"
            
    ' 1. Connect through the Connectionstring property.
    cn.ConnectionString = cnStr
    cn.Open()
    cn.Close()
    
    ' 2. Connect through the Connection object's Open method.
    cn.Open(cnStr)
    cn.Close()
    					
  6. The following code illustrates three different ways to open an ADO Recordset object. Each of these methods connect to a Microsoft SQL Server Pubs database and can be added to the Form1 Load event:
    ' 1. Open Recordset through the Execute method of the Connection object.
    cn.Open(cnStr)
    rs = cn.Execute("Select * from Authors")
    rs.Close()
    cn.Close()
    
    ' 2. Open Recordset through the Command.Execute method.
    cn.Open(cnStr)
    cmd.ActiveConnection = cn
    cmd.CommandText = "Select * from Authors"
    rs = cmd.Execute
    rs.Close()
    cn.Close()
    
    ' 3. Open Recordset without a Connection object.
    rs.Open("Select * from Authors", cnStr)
    rs.Close()
    
    ' Release the objects to free memory.
    rs = Nothing
    cn = Nothing     
    					
  7. Modify the Connection string where indicated to properly connect to your SQL Server.
  8. Press the F11 key to step through the code, and notice the different ways to create a Connection or Recordset object.
NOTE: When you set the Recordset object's ActiveConnection property to an actual string (as opposed to a Connection object), as follows
rs.ActiveConnection = "Provider=SQLOLEDB;Initial Catalog=Pubs;Data Source=servername;User ID=<username>;Password=<strong password>;
				
the following build error occurs in Visual Basic .NET:
Value of type 'String' cannot be converted to 'ADODB.Connection'.

References

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
168336 How To Open ADO Connection and Recordset Objects
 

For more information about the advantages of the new DataReader and DataAdapter objects in ADO.NET, visit the following Microsoft Developer (MSDN) Web site:
ADO.NET for the ADO Programmer
http://msdn2.microsoft.com/en-us/library/ms973217.aspx

Properties

Article ID: 308047 - Last Review: November 15, 2012 - Revision: 5.0
Applies to
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • Microsoft Visual Basic .NET 2002 Standard Edition
Keywords: 
kbhowtomaster KB308047

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com