ADO - CommandType in ADO

CommandType in ADO

The CommandType property of the Command object tells ADO how to interpret the CommandText.
Without it, ADO may have to guess what you mean (SQL query? Stored procedure? Table name?), which can cause errors or slower execution.

Available CommandType Values

(ADO constants from the CommandTypeEnum)

adCmdText (1)

CommandText is a raw SQL statement.

Example:

cmd.CommandText = "SELECT * FROM Employees WHERE Department='Sales'"
cmd.CommandType = adCmdText

adCmdTable (2)
- CommandText is a table name.
- ADO will automatically generate a SELECT * FROM TableName.
- Example:
```
cmd.CommandText = "Employees"
cmd.CommandType = adCmdTable
```

adCmdStoredProc (4)

CommandText is the name of a stored procedure.

Example:

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

adCmdUnknown (8) (default if not set)
- ADO doesn’t know the type — it has to figure it out automatically, which may hurt performance.
adCmdFile (256)
- CommandText is a file name containing a persisted command (like a saved recordset in an .adtg file).
adCmdTableDirect (512)
- CommandText is a table name, but unlike adCmdTable, this fetches data directly from the provider without generating SQL.
- Can give faster results, but fewer features.

Why is CommandType Important?

Performance: Setting it explicitly avoids extra parsing by ADO.
Clarity: Makes code more readable and predictable.
Functionality: Stored procedures won’t work unless you specify adCmdStoredProc.

Example showing Stored Procedure with Parameters:

Dim cmd, conn, 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

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

Set rs = cmd.Execute