ADO - ADO Query

1. What is an ADO Query?

In ADO, a query is an SQL statement executed against a database using the Connection or Command object.
You can use queries to:

  • Select data (SELECT)

  • Insert data (INSERT)

  • Update data (UPDATE)

  • Delete data (DELETE)

  • Execute stored procedures

2. Ways to Execute Queries in ADO

There are two main ways to run SQL queries:

(a) Using Connection.Execute()

  • Best for action queries (INSERT, UPDATE, DELETE).

  • Returns a Recordset only when used with SELECT.

Syntax:

Set rs = Connection.Execute(SQLQuery)

(b) Using Recordset.Open()

  • Best for SELECT queries when you need to navigate records.

  • Requires an existing Connection object.

Syntax:

rs.Open SQLQuery, Connection, CursorType, LockType

(c) Using Command Object

  • Best for stored procedures and parameterized queries.

  • More secure and efficient.

Syntax:

cmd.CommandText = "SQLQuery"
cmd.CommandType = adCmdText
Set rs = cmd.Execute

3. Example 1: SELECT Query (VB)

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;"

' Execute SELECT query
rs.Open "SELECT FirstName, LastName, Salary FROM Employees WHERE Salary > 50000", conn, 1, 3

' Display results
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

4. Example 2: INSERT Query

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

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

' Insert a new employee
conn.Execute "INSERT INTO Employees (FirstName, LastName, Salary) VALUES ('John','Doe',55000)"

MsgBox "Record Inserted Successfully!"

conn.Close
Set conn = Nothing

5. Example 3: UPDATE Query

conn.Execute "UPDATE Employees SET Salary = 65000 WHERE FirstName='John' AND LastName='Doe'"
MsgBox "Salary Updated Successfully!"

6. Example 4: DELETE Query

conn.Execute "DELETE FROM Employees WHERE Salary < 30000"
MsgBox "Low salary records deleted!"

7. Example 5: ADO Query in ASP Classic

<%
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;"

' Execute query
rs.Open "SELECT FirstName, LastName, Salary FROM Employees WHERE Department='HR'", conn, 1, 3

' Display results 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
%>

8. Example 6: Using Command Object for Stored Procedures

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

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

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

' Set up command
Set cmd.ActiveConnection = conn
cmd.CommandText = "GetHighSalaryEmployees"
cmd.CommandType = 4  ' adCmdStoredProc

' Execute stored procedure
Set rs = cmd.Execute

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

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

9. Best Practices

  • Use parameterized queries to prevent SQL Injection.

  • Always close Recordsets and Connections.

  • Use Command object for stored procedures.

  • Use ORDER BY in SQL instead of Recordset.Sort for better performance.

  • Use TOP or WHERE to limit fetched records for efficiency.