Microsoft KB Archive/308047

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

Article ID: 308047

Article Last Modified on 3/29/2007

-

APPLIES TO


 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft Visual Basic .NET 2002 Standard Edition

-



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





IN THIS TASK
SUMMARY
 * Create ADO Connection and Recordset Objects in Visual Basic .NET

REFERENCES



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.

back to the top

Create ADO Connection and Recordset Objects in Visual Basic .NET
 Create a new Visual Basic .NET Windows Application project. Form1 is created by default. From the Project menu, click Add Reference. On the COM tab, click Microsoft ActiveX Data Objects 2.x Library, and then click Select.  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   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 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 = &quot;Provider=SQLOLEDB;Initial Catalog=Pubs;Data Source=servername;&quot; & _ &quot;User ID= ;Password= ;&quot; ' 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   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(&quot;Select * from Authors&quot;) rs.Close cn.Close

' 2. Open Recordset through the Command.Execute method. cn.Open(cnStr) cmd.ActiveConnection = cn cmd.CommandText = &quot;Select * from Authors&quot; rs = cmd.Execute rs.Close cn.Close

' 3. Open Recordset without a Connection object. rs.Open(&quot;Select * from Authors&quot;, cnStr) rs.Close

' Release the objects to free memory. rs = Nothing cn = Nothing </li> Modify the Connection string where indicated to properly connect to your SQL Server.</li> Press the F11 key to step through the code, and notice the different ways to create a Connection or Recordset object.</li></ol>

NOTE: When you set the Recordset object's ActiveConnection property to an actual string (as opposed to a Connection object), as follows rs.ActiveConnection = &quot;Provider=SQLOLEDB;Initial Catalog=Pubs;Data Source=servername;User ID= ;Password= ; the following build error occurs in Visual Basic .NET:

Value of type 'String' cannot be converted to 'ADODB.Connection'.

back to the top

<div class="references_section">