ADO - Execute Method

What is Execute Method?

The Execute method is used with the Command object (and sometimes with the Connection object) to run a SQL statement, stored procedure, or command against the database.

It is one of the most frequently used methods in ADO because it’s how you actually run queries.

Basic Syntax

On a Command object:

Set recordset = command.Execute(RecordsAffected, Parameters, Options)

On a Connection object:

Set recordset = connection.Execute(CommandText, RecordsAffected, Options)

Arguments

  1. CommandText / Command.CommandText → The SQL query, stored procedure, or table.

  2. RecordsAffected (Optional, ByRef) → Returns the number of rows affected (useful for INSERT, UPDATE, DELETE).

  3. Parameters (Optional) → Provides parameters for the command (usually not needed if you already set cmd.Parameters).

  4. Options (Optional) → Tells ADO how to interpret the command (matches CommandType):

    • adCmdText → SQL statement

    • adCmdTable → Table name

    • adCmdStoredProc → Stored procedure

Return Value

  • If the command returns rows (like SELECT), Execute returns a Recordset object.

  • If the command doesn’t return rows (like INSERT, UPDATE, DELETE), Execute returns Nothing, but you can still capture the number of rows affected.

Examples

1. Running a SELECT Query

Dim conn, rs
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDB;User Id=sa;Password=pass;"

Set rs = conn.Execute("SELECT * FROM Employees WHERE Department = 'Sales'")
Do Until rs.EOF
    Response.Write rs("EmployeeName") & "<br>"
    rs.MoveNext
Loop
rs.Close
conn.Close

2. Running an UPDATE and Getting Rows Affected

Dim conn, rows
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDB;User Id=sa;Password=pass;"

conn.Execute "UPDATE Employees SET Salary = Salary * 1.1 WHERE Department = 'Sales'", rows
Response.Write rows & " rows updated."
conn.Close

3. Executing a Stored Procedure with Command

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

conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDB;User Id=sa;Password=pass;"
Set cmd.ActiveConnection = conn

cmd.CommandText = "usp_GetEmployeeDetails"
cmd.CommandType = adCmdStoredProc

cmd.Parameters.Append cmd.CreateParameter("@EmpID", adInteger, adParamInput, , 101)

Set rs = cmd.Execute
Response.Write "Employee: " & rs("EmployeeName")

Key Takeaways

  • Execute is how you run SQL or stored procedures.

  • Returns a Recordset if data is selected, otherwise returns Nothing.

  • Can give you the number of rows affected.

  • Works with both Connection and Command objects (Command is better for stored procedures).