ADO - Displaying Data in ADO

1. Displaying Data in ADO

To display data using ADO, you generally follow these steps:

Steps:

1. Create a Connection → Connect to the database.

2. Open a Recordset → Fetch data using SQL.

3. Loop through Records → Use Do While Not rs.EOF.

4. Display Fields → Access column values using rs("FieldName").

5. Close Objects → Close recordset and connection.

2. Example: ADO Display in VB (Visual Basic)

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

' Open connection
conn.Open "Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;User ID=sa;Password=1234;"

' Open recordset
rs.Open "SELECT FirstName, LastName, Salary FROM Employees", conn, 1, 3

' Display data in Immediate Window
Do While Not rs.EOF
    Debug.Print rs("FirstName") & " " & rs("LastName") & " - " & rs("Salary")
    rs.MoveNext
Loop

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

Output Example in Immediate Window:

John Smith - 50000
Alice Johnson - 60000
David Brown - 45000

3. Example: ADO Display in ASP Classic (Web Page)

<%
Dim conn, rs
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

' Open connection
conn.Open "Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;User ID=sa;Password=1234;"

' Open recordset
rs.Open "SELECT FirstName, LastName, Salary FROM Employees", conn, 1, 3

' Display data in HTML table
Response.Write "<table border='1'>"
Response.Write "<tr><th>First Name</th><th>Last Name</th><th>Salary</th></tr>"

Do While Not rs.EOF
    Response.Write "<tr>"
    Response.Write "<td>" & rs("FirstName") & "</td>"
    Response.Write "<td>" & rs("LastName") & "</td>"
    Response.Write "<td>" & rs("Salary") & "</td>"
    Response.Write "</tr>"
    rs.MoveNext
Loop

Response.Write "</table>"

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
%>

Output Example in Browser:

4. Example: ADO Display in VBScript (Command Line)

Dim conn, rs
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

' Open connection
conn.Open "Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;User ID=sa;Password=1234;"

' Open recordset
rs.Open "SELECT FirstName, LastName, Salary FROM Employees", conn, 1, 3

' Display data in console
Do While Not rs.EOF
    WScript.Echo rs("FirstName") & " " & rs("LastName") & " - " & rs("Salary")
    rs.MoveNext
Loop

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

5. Best Practices for Displaying Data

• Always check for EOF before reading data.

• Use HTML tables in ASP for a better look.

• Use ORDER BY in SQL if you want sorted output:

  SELECT * FROM Employees ORDER BY Salary DESC;
  

• For large datasets, limit records using TOP or WHERE conditions for better performance.