ADO - Parameters Collection in ADO

Parameters Collection in ADO

The Parameters Collection belongs to the Command object.
It holds one or more Parameter objects, each representing an input, output, or return value for the command (often a stored procedure).

This is how you pass values safely instead of concatenating them into SQL (which can cause errors or even SQL injection).

Key Points

  1. A Command can have zero or more parameters.

  2. Each Parameter object has properties like Name, Type, Direction, Size, and Value.

  3. You append parameters to the Parameters collection before executing the command.

  4. Works with both stored procedures and parameterized SQL queries.

Basic Syntax

cmd.Parameters.Append cmd.CreateParameter(Name, Type, Direction, Size, Value)

Example 1: Passing Parameters to a Stored Procedure

Suppose we have a stored procedure in SQL Server:

CREATE PROCEDURE usp_GetEmployeeByID
  @EmpID INT
AS
BEGIN
  SELECT * FROM Employees WHERE EmployeeID = @EmpID
END

ADO VBScript / Classic ASP code:

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_GetEmployeeByID"
cmd.CommandType = adCmdStoredProc

' Append parameter (Name, Type, Direction, Size, Value)
cmd.Parameters.Append cmd.CreateParameter("@EmpID", adInteger, adParamInput, , 101)

Set rs = cmd.Execute

Example 2: Using Parameters with SQL Text

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

cmd.Parameters.Append cmd.CreateParameter("Dept", adVarChar, adParamInput, 50, "Sales")

Set rs = cmd.Execute

Here, the ? acts as a placeholder, and ADO replaces it with the parameter value.

Parameter Properties

  • Name → The parameter’s name ("@EmpID").

  • Type → Data type (e.g., adInteger, adVarChar).

  • Direction → Input/output (adParamInput, adParamOutput, adParamInputOutput, adParamReturnValue).

  • Size → For variable-length types (e.g., VARCHAR(50) → Size = 50).

  • Value → The actual data passed or returned.

Example 3: Getting Output Parameter

Stored procedure:

CREATE PROCEDURE usp_GetTotalEmployees
  @Dept NVARCHAR(50),
  @Total INT OUTPUT
AS
BEGIN
  SELECT @Total = COUNT(*) FROM Employees WHERE Department = @Dept
END

ADO code:

cmd.CommandText = "usp_GetTotalEmployees"
cmd.CommandType = adCmdStoredProc

cmd.Parameters.Append cmd.CreateParameter("@Dept", adVarChar, adParamInput, 50, "Sales")
cmd.Parameters.Append cmd.CreateParameter("@Total", adInteger, adParamOutput)

cmd.Execute
Response.Write "Total Employees in Sales: " & cmd.Parameters("@Total").Value

In short:

  • The Parameters Collection lets you pass and retrieve values safely when executing commands.

  • It’s essential for working with stored procedures and parameterized queries in ADO.