ADO - ADO Command Object

1. What is the ADO Command Object?

The ADO Command Object is used to execute SQL statements or stored procedures against a database.
It is more secure, flexible, and powerful than using Connection.Execute or Recordset.Open.

Key Uses:

  • Execute SELECT, INSERT, UPDATE, DELETE queries.

  • Call stored procedures.

  • Use parameters for secure, dynamic queries.

  • Improve performance with precompiled queries.

2. Creating an ADO Command Object

Basic Syntax

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

Then associate it with a Connection:

Set cmd.ActiveConnection = conn

3. Common Properties of Command Object

Property Description
ActiveConnection Associates the command with a database connection.
CommandText SQL query or stored procedure name.
CommandType Specifies type of command: 1 = adCmdText → SQL statement 2 = adCmdTable → Table name 4 = adCmdStoredProc → Stored procedure
Parameters Collection of input/output parameters.
Prepared When set to True, speeds up execution of repeated queries.

4. Common Methods

Method Description
Execute Runs the command and optionally returns a recordset.
CreateParameter Creates a parameter object for dynamic queries.
Parameters.Append Adds a parameter to the command.

5. Example 1: Using Command Object to Run a SELECT Query (VB)

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

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

' Configure command
Set cmd.ActiveConnection = conn
cmd.CommandText = "SELECT FirstName, LastName, Salary FROM Employees WHERE Salary > 50000"
cmd.CommandType = 1 ' adCmdText

' Execute command
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

6. Example 2: Using Command Object with Parameters (Secure Queries)

VB Example

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

' Open connection
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 = "SELECT * FROM Employees WHERE Department = ? AND Salary > ?"
cmd.CommandType = 1 ' adCmdText

' Add parameters
cmd.Parameters.Append cmd.CreateParameter("Department", 200, 1, 50, "IT")   ' adVarChar
cmd.Parameters.Append cmd.CreateParameter("Salary", 3, 1, , 60000)         ' adInteger

' Execute command
Set rs = cmd.Execute

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

Why use parameters?

  • Prevents SQL Injection.

  • Allows dynamic queries.

  • Improves security and performance.

7. Example 3: Using Command Object with Stored Procedures

Stored Procedure in SQL Server

CREATE PROCEDURE GetHighSalaryEmployees
    @MinSalary INT
AS
BEGIN
    SELECT FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > @MinSalary
END

VB Example

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 cmd.ActiveConnection = conn
cmd.CommandText = "GetHighSalaryEmployees"
cmd.CommandType = 4 ' adCmdStoredProc

' Add parameter
cmd.Parameters.Append cmd.CreateParameter("MinSalary", 3, 1, , 60000) ' adInteger

' Execute stored procedure
Set rs = cmd.Execute

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

8. Example 4: ADO Command Object in ASP Classic

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

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

Set cmd.ActiveConnection = conn
cmd.CommandText = "SELECT * FROM Employees WHERE Department='Finance'"
cmd.CommandType = 1 ' adCmdText

Set rs = cmd.Execute

Do While Not rs.EOF
    Response.Write rs("FirstName") & " " & rs("LastName") & "<br>"
    rs.MoveNext
Loop

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

9. Best Practices

  • Use Command Object instead of inline SQL for better security.

  • Always use parameters for user inputs.

  • Use CommandType = adCmdStoredProc for stored procedures.

  • Set cmd.Prepared = True if running the same query multiple times.

  • Always close connections and release objects.

 diagram? It’ll make the concept clearer.