ADO Collections Append Method

Appends an object to a collection.

Append Method Applies To

ADO Parameters Collection

Append Method Syntax

collection.Append object

Append Method Parameters

object

An object variable representing the object to be appended.

Append Method Remarks

Use the Append method on a collection to add an object to that collection. This method is available only on the Parameters collection of a ADO Command Object. You must set the ADO Parameter Object Type Property of an ADO Parameter Object before appending it to the Parameters collection. If you select a variable-length data type, you must also set the ADO Parameter Object Size Property to a value greater than zero.

By describing the parameter yourself, you can minimize calls to the provider and consequently improve performance when using stored procedures or parameterized queries. However, you must know the properties of the parameters associated with the stored procedure or parameterized query you wish to call. Use the CreateParameter method to create Parameter objects with the appropriate property settings and use the Append method to add them to the Parameters collection. This lets you set and return parameter values without having to call the provider for the parameter information. If you are writing to a provider that does not supply parameter information, you must manually populate the Parameters collection using this method to be able to use parameters at all.

Append Method Examples

This Visual Basic example uses the Append and CreateParameter methods to execute a stored procedure with an input parameter.

Public Sub AppendX()

Dim cnn1 As ADODB.Connection

Dim cmdByRoyalty As ADODB.Command

Dim prmByRoyalty As ADODB.Parameter

Dim rstByRoyalty As ADODB.Recordset

Dim rstAuthors As ADODB.Recordset

Dim intRoyalty As Integer

Dim strAuthorID As String

Dim strCnn As String

` Open connection.

Set cnn1 = New ADODB.Connection

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

cnn1.Open strCnn

cnn1.CursorLocation = adUseClient

` Open command object with one parameter.

Set cmdByRoyalty = New ADODB.Command

cmdByRoyalty.CommandText = "byroyalty"

cmdByRoyalty.CommandType = adCmdStoredProc

` Get parameter value and append parameter.

intRoyalty = Trim(InputBox("Enter royalty:"))

Set prmByRoyalty = cmdByRoyalty.CreateParameter("percentage", _

adInteger, adParamInput)

cmdByRoyalty.Parameters.Append prmByRoyalty

prmByRoyalty.Value = intRoyalty

` Create recordset by executing the command.

Set cmdByRoyalty.ActiveConnection = cnn1

Set rstByRoyalty = cmdByRoyalty.Execute

` Open the Authors table to display author names.

Set rstAuthors = New ADODB.Recordset

rstAuthors.Open "authors", cnn1, , , adCmdTable

` Print current data in the recordset, adding

` author names from Authors table.

Debug.Print "Authors with " & intRoyalty & " percent royalty"

Do While Not rstByRoyalty.EOF

strAuthorID = rstByRoyalty!au_id

Debug.Print " " & rstByRoyalty!au_id & ", ";

rstAuthors.Filter = "au_id = '" & strAuthorID & "'"

Debug.Print rstAuthors!au_fname & " " & rstAuthors!au_lname

rstByRoyalty.MoveNext

Loop

rstByRoyalty.Close

rstAuthors.Close

cnn1.Close

End Sub