ADO - CommandText

What is CommandText?

The CommandText property of the Command object in ADO specifies what command will be executed against the data source.

  • It could be a SQL query (like SELECT, INSERT, UPDATE, DELETE).

  • It could be the name of a stored procedure.

  • It could even be a table name (if you set the CommandType accordingly).

Syntax

Command.CommandText = "SQL query or stored procedure name"

Example:

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 = "SELECT * FROM Employees WHERE Department = 'Sales'"
cmd.CommandType = 1   ' 1 = adCmdText (SQL command)

Set rs = cmd.Execute

Values of CommandText depend on CommandType

  • adCmdText (1) → CommandText contains a SQL statement.
    Example: "SELECT * FROM Products WHERE Price > 100"

  • adCmdTable (2) → CommandText contains a table name.
    Example: "Employees"

  • adCmdStoredProc (4) → CommandText contains the name of a stored procedure.
    Example: "usp_GetEmployeeDetails"

Key Points

  1. Always set CommandType correctly so ADO knows how to interpret CommandText.

  2. You can use parameters with CommandText by adding Parameter objects.

  3. Useful when you want to reuse commands or execute stored procedures.

  4. Provides better performance with stored procedures than writing inline SQL.