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

SUMMARY

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.

rs.ActiveConnection = "Provider=SQLOLEDB;Initial Catalog=Pubs;Data Source=servername;User ID=<username>;Password=<strong password>;