devonmanelski.com
 
Home | I.T. Management | Business Analysis & Project Management | VB.Net | SQL Server | About |

The Recordset Object
  1. Summary
    2. The Recordset Object is one of the most straighforward ways to retrieve data from a database.
    1. Add a Reference to the most recent Microsoft ActiveX Data Objects Library (ADODB).
    2. In the click event of the btnOpenRecordset button, set a String Variable to the Connection String for the Connection Object of ActiveX Data Objects.
    3. Open a Database Connection using an ActiveX Data Objects Connection Object.
    4. Set a String Variable to the SQL of the Structured Query Language (SQL) Statement to run against the database.
    5. Open a Recordset using an ActiveX Data Objects Recordset Object.
    6. Access the Fields in the Recordset.
    7. Close the Recordset.
    8. Close the Database Connection.
  2. Add A Reference to the Most Recent ActiveX Data Objects Library (ADODB).
    1. Right Click on the References folder and choose Add Reference.
    2. Select the COM menu item on the left hand side, and add a Reference to the most recent ActiveX Data Objects library. In the above screenshot, the most recent ADO library is the Microsoft ActiveX Data Objects 6.1 Library.
  3. Sample Code
    1. In the click event of the btnOpenRecordset button, add the code to open a recordset.
      2. Private Sub btnOpenRecordset_Click(sender As Object, e As EventArgs) Handles btnOpenRecordset.Click
      On Error GoTo Errorhandler

      Dim oConnection As ADODB.Connection
      Dim oRst As ADODB.Recordset
      Dim sConnectionString As String
      Dim sSQL As String

      oConnection = New ADODB.Connection
      oRst = New ADODB.Recordset
      sConnectionString = "Provider=MSOLEDBSQL;Server=ServerName 127.0.0.1

      This IP Address is for locally installed databases.       ;Database=DatabaseName;User ID=UserName usr_SQLServerLoginName

      Always create a new, separate Login Name for each database. The sa Login Name should only be used for database administration.       ;Password= UserNamePassword Use strong passwords that have upper and lower case characters, numbers and special characters such as !, #, %, & or *. "

      sSQL = "SELECT FieldName1, FieldName2, FieldName3, FieldName4 FROM MyTable ORDER BY Field1" SELECT ID, Company, [Last Name], [First Name] FROM [Northwind].[dbo].[Customers] ORDER BY ID

      This example is for the Customers table of Microsoft's Northwind Sample Database.
      oConnection.Open(sConnectionString)

      oRst.Open(sSQL, oConnection)
      Msgbox(oRst.Fields(" FieldName3 Msgbox(oRst.Fields("LastName"))

      This example is for the Customers table of Microsoft's Northwind Sample Database.       ").Value.ToString)
      oRst.Close

      oConnection.Close

      oRst = Nothing
      oConnection = Nothing

      Exit Sub
      Errorhandler:
      Msgbox(Err.Number & "-" & Err.Source & ": " & Err.Description)
      End Sub

    Copyright © Devon Manelski