Append Method Examples

You are here: ADO Component Reference > ADO Collections > ADO Collections Methods > ADO Collections Append Method > 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