The Parameters collection, which consists of Parameter objects, provides parameter information and data for the Command object. You use the Parameters collection and Parameter objects when the query in the Command object requires parameters.
A Parameter object can serve as an input parameter, an output parameter that holds data, or a stored procedure return value. The Refresh method on the Parameters collection can force providers to update parameter information, but this operation can be time-consuming.
When dealing with long data types, the AppendChunk method can be used to write data in chunks.
This example shows the creation of a parameter (input) for a stored procedure. The Transact-SQL syntax for the stored procedure follows:
USE PUBS
GO
DROP PROC myADOParaProc
GO
CREATE PROC myADOParaProc
@type char(12)
AS
SELECT * FROM titles WHERE type = @type
GO
The myADOParaProc stored procedure performs a SELECT query against the titles table of the pubs database, taking one @type input parameter in its WHERE clause. The data type for the @type parameter is char, and its size is 12.
The Microsoft® Visual Basic® code follows:
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim prm As ADODB.Parameter
Dim fld As ADODB.Field
Dim provStr As String
' Connect using the SQLOLEDB provider.
cn.Provider = "sqloledb"
' Specify connection string on Open method.
provStr = "Server=MyServer;Database=pubs;Trusted_Connection=yes"
cn.Open provStr
' Set up a command object for the stored procedure.
Set cmd.ActiveConnection = cn
cmd.CommandText = "myADOParaProc"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 15
' Set up a new parameter for the stored procedure.
Set prm = Cmd.CreateParameter("Type", adWChar, adParamInput, 12, "Business")
Cmd.Parameters.Append prm
' Create a recordset by executing the command.
Set rs = cmd.Execute
Set Flds = rs.Fields
' Print the values for all rows in the result set.
While (Not rs.EOF)
For Each fld in Flds
Debug.Print fld.Value
Next
Debug.Print ""
rs.MoveNext
Wend
' Close recordset and connection.
rs.Close
cn.Close
The myADOParaProc stored procedure expects an input parameter whose data type is char and whose size is 12. The CreateParameter method is used to create a Parameter object with the following characteristics: the data type is adWChar for null-terminated Unicode character string, parameter type is adParamInput for input parameter, and data length is 12. This Parameter object is also given the name Type. The data value Business (one of the possible values of Type in the titles table) is hard-coded.
After the parameter is specified, the Append method adds the Parameter object to the Parameters collection. The myADOParaProc stored procedure is executed, and a Recordset object is created. After that, the values for the fields of each row in the recordset are printed, and the Connection and Recordset objects are closed.