ADO - ADO Parameter Direction and Parameter Creation

In ADO (ActiveX Data Objects), parameters are used to pass values between an application and a database command, especially when executing stored procedures or parameterized SQL statements. A parameter represents a value that is supplied to a database operation or returned from it. Using parameters makes database operations more structured and can also help prevent SQL injection when values are passed separately from the SQL command itself.

1. What Is an ADO Parameter?

An ADO parameter is an object that contains information about a value used by a Command object. A parameter can contain details such as its name, data type, size, direction, and value.

For example, suppose a stored procedure searches for an employee using an employee ID. Instead of directly placing the ID into the SQL statement, an application can create a parameter:

@EmployeeID = 101

ADO sends the parameter value separately to the database.

The main properties of an ADO Parameter object include:

  • Name – identifies the parameter.

  • Type – specifies the data type.

  • Direction – specifies whether the parameter is used for input, output, or both.

  • Size – specifies the maximum size for variable-length data.

  • Value – contains the actual value.

  • Precision – specifies precision for numeric values.

  • NumericScale – specifies the scale of numeric values.

These properties allow the application and database to understand how the parameter should be handled.

2. Why Parameter Direction Is Important

Parameter direction tells ADO how a parameter participates in a database operation.

For example, an application may send a customer ID to a stored procedure and receive the customer's name in return. The customer ID is an input parameter, while the customer name is an output parameter.

ADO provides four important parameter directions:

  1. adParamInput

  2. adParamOutput

  3. adParamInputOutput

  4. adParamReturnValue

Understanding these directions is important when working with stored procedures and commands.

3. Input Parameters

An input parameter is used to send a value from the application to the database.

For example, consider a stored procedure that retrieves an employee based on an employee ID:

CREATE PROCEDURE GetEmployee
    @EmployeeID INT
AS
BEGIN
    SELECT EmployeeID, EmployeeName, Department
    FROM Employees
    WHERE EmployeeID = @EmployeeID
END

The application supplies the employee ID.

In ADO, the parameter can be created as:

Set param = cmd.CreateParameter( _
    "@EmployeeID", _
    adInteger, _
    adParamInput)

param.Value = 101
cmd.Parameters.Append param

Here:

  • @EmployeeID is the parameter name.

  • adInteger specifies the data type.

  • adParamInput specifies that the value is being sent to the database.

  • 101 is the actual value.

Input parameters are commonly used for search conditions, filtering, inserting records, and updating records.

4. Output Parameters

An output parameter allows the database to send a value back to the application.

For example:

CREATE PROCEDURE GetEmployeeName
    @EmployeeID INT,
    @EmployeeName VARCHAR(100) OUTPUT
AS
BEGIN
    SELECT @EmployeeName = EmployeeName
    FROM Employees
    WHERE EmployeeID = @EmployeeID
END

The application supplies the employee ID, while the database returns the employee name through the output parameter.

An ADO parameter can be created as:

Set param = cmd.CreateParameter( _
    "@EmployeeName", _
    adVarChar, _
    adParamOutput, _
    100)

cmd.Parameters.Append param

After the command is executed, the returned value can be accessed through:

employeeName = cmd.Parameters("@EmployeeName").Value

The Size argument is particularly important for variable-length data such as VARCHAR, because it tells ADO how much space should be allocated for the parameter.

5. Input/Output Parameters

An input/output parameter performs both functions.

The application initially sends a value to the database, and the database can modify that value and return the result.

For example:

CREATE PROCEDURE UpdateEmployeeName
    @EmployeeID INT,
    @EmployeeName VARCHAR(100) OUTPUT
AS
BEGIN
    UPDATE Employees
    SET EmployeeName = @EmployeeName
    WHERE EmployeeID = @EmployeeID

    SET @EmployeeName = UPPER(@EmployeeName)
END

The application provides the employee name, and the database returns the modified value.

In ADO:

Set param = cmd.CreateParameter( _
    "@EmployeeName", _
    adVarChar, _
    adParamInputOutput, _
    100)

param.Value = "John Smith"

cmd.Parameters.Append param

After execution:

result = cmd.Parameters("@EmployeeName").Value

The parameter therefore acts in both directions.

6. Return-Value Parameters

A return-value parameter is used to receive the value returned by a stored procedure through its RETURN statement.

For example:

CREATE PROCEDURE CheckEmployee
    @EmployeeID INT
AS
BEGIN
    IF EXISTS
    (
        SELECT 1
        FROM Employees
        WHERE EmployeeID = @EmployeeID
    )
        RETURN 1
    ELSE
        RETURN 0
END

The procedure returns 1 when the employee exists and 0 otherwise.

ADO can create a return-value parameter:

Set param = cmd.CreateParameter( _
    "ReturnValue", _
    adInteger, _
    adParamReturnValue)

cmd.Parameters.Append param

After executing the command:

result = cmd.Parameters("ReturnValue").Value

The important distinction is that a return-value parameter receives the value from the stored procedure's RETURN statement, whereas an output parameter receives a value assigned to an OUTPUT parameter.

7. Creating Parameters with CreateParameter

The CreateParameter method of the ADO Command object is commonly used to create a parameter.

Its general structure is:

Set parameter = command.CreateParameter( _
    Name, _
    Type, _
    Direction, _
    Size, _
    Value)

Not every argument is mandatory.

For example:

Set p = cmd.CreateParameter( _
    "@Age", _
    adInteger, _
    adParamInput)

p.Value = 30

cmd.Parameters.Append p

The parameter is first created and then added to the command's Parameters collection using Append.

8. Parameters Collection

Every ADO Command object has a Parameters collection.

This collection contains the parameters associated with the command.

For example:

cmd.Parameters.Append p

Multiple parameters can be added:

cmd.Parameters.Append pEmployeeID
cmd.Parameters.Append pDepartment
cmd.Parameters.Append pSalary

They can later be accessed by name:

cmd.Parameters("@EmployeeID").Value

or by index:

cmd.Parameters(0).Value

Using parameter names is generally easier to understand because it clearly identifies the purpose of each parameter.

9. Parameter Data Types

The parameter's Type property specifies the type of data it represents.

Some commonly used ADO data types include:

ADO Type Typical Use
adInteger Integer values
adSmallInt Small integer values
adBigInt Large integer values
adDecimal Decimal numbers
adNumeric Numeric values
adVarChar Variable-length character data
adChar Fixed-length character data
adVarWChar Unicode variable-length text
adDate Date and time values
adBoolean True/False values
adLongVarChar Large text values

The selected ADO type should be compatible with the corresponding database column or stored procedure parameter.

10. Importance of Parameter Size

The Size property is particularly important for variable-length parameters.

For example:

Set pName = cmd.CreateParameter( _
    "@Name", _
    adVarChar, _
    adParamInput, _
    100)

pName.Value = "David"

Here, the parameter can contain up to 100 characters.

For numeric types such as adInteger, a size value is generally not required in the same way as it is for variable-length character types.

11. Setting Parameter Values

A parameter's value can be assigned through its Value property:

p.Value = 500

For text:

p.Value = "Bangalore"

For dates:

p.Value = #08/18/2026#

The value should be compatible with the parameter's declared data type.

12. Complete Example

The following example demonstrates an input parameter being used with a stored procedure:

Dim cmd As ADODB.Command
Dim pEmployeeID As ADODB.Parameter

Set cmd = New ADODB.Command

Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "GetEmployee"

Set pEmployeeID = cmd.CreateParameter( _
    "@EmployeeID", _
    adInteger, _
    adParamInput)

pEmployeeID.Value = 101

cmd.Parameters.Append pEmployeeID

Set rs = cmd.Execute

The sequence is:

  1. Create a Command object.

  2. Associate it with a database connection.

  3. Specify that the command represents a stored procedure.

  4. Specify the stored procedure name.

  5. Create the parameter.

  6. Specify its data type.

  7. Specify its direction.

  8. Assign its value.

  9. Append it to the Parameters collection.

  10. Execute the command.

13. Parameter Direction Comparison

Direction Data Flow Common Purpose
adParamInput Application → Database Supplying search or update values
adParamOutput Database → Application Receiving calculated or generated values
adParamInputOutput Application ↔ Database Sending a value and receiving a modified value
adParamReturnValue Database → Application Receiving a stored procedure return value

14. Advantages of Using ADO Parameters

Parameters provide several important advantages.

Better security: Parameterized commands reduce the risk of SQL injection because user-supplied values are handled as parameter values rather than being directly concatenated into SQL statements.

Better organization: Parameters clearly separate SQL commands from the values being supplied to them.

Support for stored procedures: Parameters are essential when working with stored procedures that accept input or produce output values.

Type handling: The parameter's data type allows ADO and the database provider to handle values more appropriately.

Output handling: Applications can receive calculated values, generated identifiers, status codes, and other information through output and return-value parameters.

15. Common Mistakes

One common mistake is forgetting to append a newly created parameter to the Parameters collection:

Set p = cmd.CreateParameter("@ID", adInteger, adParamInput)
p.Value = 101

The parameter exists, but it has not yet been associated with the command. It should be appended:

cmd.Parameters.Append p

Another common mistake is specifying an inappropriate data type or insufficient size for a parameter. For example, a text parameter may need an appropriate size when using adVarChar.

It is also important to distinguish between adParamOutput and adParamReturnValue. An output parameter corresponds to an output parameter defined by the stored procedure, while a return-value parameter receives the value explicitly returned by the procedure's RETURN statement.

Conclusion

ADO parameter direction and parameter creation provide a structured way for applications to exchange data with database commands. The CreateParameter method creates parameter objects, while the Direction property determines whether information moves into the database, out of the database, or in both directions. The four major directions are adParamInput, adParamOutput, adParamInputOutput, and adParamReturnValue.

Understanding these parameter types is especially important when developing ADO applications that use stored procedures. Proper parameter creation, data-type selection, size specification, value assignment, and inclusion in the Parameters collection make database operations more reliable, maintainable, and secure.