ADO Parameters Collection

[Previous] [Next]

Each Command object exposes a Parameters collection that you can use for parameterized queries. The Parameters collection exposes most of the same properties and methods as all standard collections, but there are two methods on the Parameters collection worth discussing—Append and *Refresh. Each of these methods can be used to populate the Parameters collection. Generally, you'll use either one or the other in your applications.

Important Methods of the Parameters Collection
Method Name	Description
Append	Appends a Parameter object to the Parameters collection
Refresh	Refreshes the Parameters collection

Append Method

Use the Append method to add a Parameter object to the Parameters collection. By populating the Parameters collection on your own, you can achieve better performance than if you have ADO ask the OLE DB provider to supply that information. Append takes one parameter:

• Object This value specifies the Parameter object that you want to append to the Parameters collection.

There are two main ways to use the Append method on the Parameters collection. You can use a Parameter object variable as the Object parameter:

 Set param = cmd.CreateParameter(...) cmd.Parameters.Append param 

However, I prefer to combine the two lines of code and avoid using the object variable:

 cmd.Parameters.Append cmd.CreateParameter(...) 

We'll talk more about populating the Parameters collection on your own at the end of the chapter.

Refresh Method

If you call the Refresh method on the Parameters collection, ADO will ask the OLE DB provider for parameter information about the query that the Command object will execute. The information includes the number of parameters as well as their data type, direction, and size. This functionality is optional for OLE DB providers: some provide all or part of this information; others provide none.