How to open ADO Connection and Recordset objects by using Visual Basic .NET
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 StringDim 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 <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 = cnStrcn.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("Select * from Authors")rs.Close()cn.Close()' 2. Open Recordset through the Command.Execute method.cn.Open(cnStr)cmd.ActiveConnection = cncmd.CommandText = "Select * from Authors"rs = cmd.Executers.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 = Nothingcn = Nothing
- Modify the Connection string where indicated to properly connect to your SQL Server.
- Press the F11 key to step through the code, and notice the different ways to create a Connection or Recordset object.
rs.ActiveConnection = "Provider=SQLOLEDB;Initial Catalog=Pubs;Data Source=servername;User ID=<username>;Password=<strong password>;
For more information about the advantages of the new DataReader and DataAdapter objects in ADO.NET, visit the following Microsoft Developer (MSDN) Web site:
Article ID: 308047 - Last Review: 12/06/2015 05:42:26 - Revision: 5.0
- kbnosurvey kbarchive kbhowtomaster KB308047