DevGuru ADO Command::Parameters Collection



			COLLECTION: Command::Parameters Collection The Parameters Collection is a collection of the Parameter objects associated with a specific Command object. Each Parameter object provides detailed information about a single parameter used in a stored procedure or a parameterized query. Only the Command object has a Parameters Collection. Note that not all providers support stored procedures or parameterized queries, nor do all providers return parameters to the Parameters Collection. For such providers, the Parameters Collection will be left empty and the collection will have to be manually populated. If the provider will allow, you can populate the Parameters Collection by using the Refresh method. In fact, if you try to access this collection while it is empty or before you have called Refresh for the first time, ADO will automatically call Refresh to populate the collection. It is more efficient to provide the parameters, rather than having to call and obtain this information from the provider. (Anything you can do to reduce calls to the provider will improve performance.) You can add Parameter objects using the Append property. The collection starts numbering (indexing) with the number zero. The Parameters Collection has two properties and three methods. PROPERTIES Count Property The Count property returns a long value that is the number of items in the collection. The counting starts at zero. You can use this value to loop through the collection by iterating from zero to the value of Count minus one. Code (VBScript): intCountNumber = objCommand.Parameters.Count You can also use the VB/VBScript For Each ... Next statement. Code (VBScript): For Each objParameter In objCommand.Parameters ' place code here to manipulate each item in collection Next Item Property The Item property is used to return a specific member of the Parameters Collection. The Index parameter is the position (ordinal) number. Code (VBScript): MyParameter = objCommand.Parameters.Item(5) Or: MyParameter = objCommand.Parameters(5) METHODS The Append method is used to add (append) a Parameter object to the Parameters Collection. Before appending, make sure to set the Type property for the Parameter object. You will also need to set the Size property for variable-length data types. In the example, note the use of the CreateParameter method which is used to create a Parameter object and to set the Name, Type and Direction. (It can also be used to set the Size and Value.) Syntax: Parameters.Append Object The Object parameter is the Parameter object to be appended. Code (Visual Basic): Set objTrainingComplete = objDevGuruStaff.CreateParameter("ASP-plus", adBoolean, adParamInput) objDevGuruStaff.Parameters.Append objTrainingComplete objTrainingComplete.Value = True Delete Method The Delete method deletes a Parameter object from the Parameters Collection. Syntax: Parameters.Delete Index The Index parameter is either the name property or the ordinal position (index) in the collection of the Parameter object. Code (VBScript): objCommand.Parameters.Delete 3 Refresh Method The Refresh method updates the Parameter objects in the Parameters Collection with the latest information from the provider. Before calling Refresh for a Command object, you need to set the ActiveConnection property to an active Connection object, set the CommandText property to a command that will be recognized by the provider, and set the CommandType property to the adCmdStoredProc constant. Code (VBScript): objCommand.Parameters.Refresh