Using ADO Parameters

No issue is more central to application development than access to a database. When you use Visual Basic (VB) and ADO to create any type of application, whether a standalone executable or a component, you’ll want your code to effectively access data.

Related: Mastering ADO

A good way to access data in an ADO application is to use a command object and execute stored procedures that use parameters. Most stored procedures use parameters to pass information. For example, the following stored procedure, GetPubs, extracts records that match the Pub_ID parameter from the Publishers table in the Pubs sample database:

CREATE PROC GetPubs @PubID Char(4) AS SELECT * FROM publishersWHERE pub_id = @PubID

To use the GetPubs stored procedure in a VB application that uses ADO, you need to declare the PubID parameter. If you execute GetPubs without declaring the PubID, your code will generate a runtime error specifying the missing parameter.

Getting Started

Stored procedures are objects in the database that run fast. To optimize the use of stored procedures in your application, you need to properly use them with the various ADO features. You can download this article's code from the file 7849.zip in the information box at the top of this page. To create the application Listing 1 shows, you first need to open a new project in VB, then create a reference to the Microsoft ActiveX Data Object 2.1 Library.

The application uses the stored procedures GetPubs and GetTitles. GetPubs takes a pub_id and retrieves the matching publisher. GetTitles takes a Title_ID and retrieves the matching entry from the Titles table:

CREATE PROC Gettitles @titleID char(6) AS SELECT * FROM titles WHERE Title_id = @titleid 

The application executes GetPubs and GetTitles in a For Next loop, which extracts a recordset from each stored procedure each time it iterates through the loop. The results of each iteration are loaded into list boxes, as Screen 1 shows.

Click Execute to fire the cmdExecute_Click event procedure. The PubID and TitleID arrays contain the parameters for the stored procedures, as Callout A shows. The ExecuteStuff procedure fires at the end of the cmdExecute_Click procedure and the code passes the two arrays containing the arguments into the procedure.

Callout B specifies the code used to create the connection to the database and to set the command type. Explicitly setting the command type to adCmdStoredProc tells ADO that it will execute a stored procedure, which saves execution time because it prevents ADO from having to determine what a particular piece of code does before executing it.

You can use the ADO constants to set most of ADO’s options. In this case, you use the adCmdStoredProc constant to let ADO know the command it’s going to use to execute a stored procedure. The ADO constants are located in the ADO type library and are available when you create the reference to the ADO library. Using the constants is an easy way to make your application more readable and maintainable. The constants also ensure that you’re using the correct values for the various options.

Creating Parameters

The code in Callout C creates the parameter for the first stored procedure and opens the recordset. Then the Append method of the parameters collection adds the parameter object to the collection. The CreateParameter method returns a parameter object that is passed to the Append method

cmd.Parameters.Append cmd.CreateParameter("PubID", adChar,adParamInput, 4, PubId(i))

The CreateParameter method takes several arguments. The first, PubID, is a string index to the parameter object that results from the method call. The next argument, adChar, defines the data type. The adParamInput argument specifies whether the parameter is an input to or an output from the stored procedure. The next argument shows the length of the parameter data. And the last argument is the value for the parameter, which is pulled from the PubID array by using the i index to point to the correct value. CreateParameter creates a parameter object and returns a reference to this object as its return value. The code passes this object reference to the Append method to add to the collection.

Explicitly setting options is true with parameters as it is with explicitly setting other ADO options. If you set only part of the options such as the parameter name and value, ADO asks the provider for the information by refreshing the parameters collection. A Refresh queries the provider for information about the parameters for the object you specify, then loads the parameters collection accordingly. The Refresh operation uses resources on the server, so it negatively impacts your application’s performance. Also, not all providers support Refresh by supplying their parameter information. ADO executes Refresh explicitly or whenever ADO doesn’t have the information it needs.

Stored Procedures and the Parameters Collection

The next statement in Callout C sets the CommandText to the name of the stored procedure. The next two lines set the cursor location, then open the recordset. After Callout C, the Do While loop moves through the recordset and loads the text box. Because this application returns only one record at a time, it doesn’t require the Do While loop. But you can feed the Do While loop data from recordsets with multiple rows.

Callout D shows the code that works with the next stored procedure, GetTitles. Because the code uses only one command object, you need to manage the parameters collection. To do so, before you can append the new parameter, you need to delete the current one with the first line of code in Callout D. The argument that is the index value you set when you created the parameter is passed to the Delete method. Setting the index lets you work with the parameter object explicitly using the parameter’s name, which was set with the CreateParameter method. This way the code is simple and easy to read. After the code deletes the parameter, it creates the new parameter, appends it to the parameters collection, and executes the second stored procedure in the remaining code in Callout D.

Other Commands and Methods

If your application uses multiple parameters, you can determine the number of parameters with the Count property:

cmd.Parameters.Count

Then your code can use a For Next loop to go through the parameters collection. If the Count property returns 0, no parameters are in the collection. The Count property is useful when your application uses many parameters, and you need to programmatically inspect or set them.

You can use the Item method to extract a reference to a particular parameter object. After you have a reference to the parameter, you can change any of its properties, such as the value or data type.

The parameters collection can be difficult to work with, especially when you’re using a large number of parameters. The difficulty of using parameters is one reason many people work with dynamic SQL rather than stored procedures; it’s fast and easy to create dynamic SQL. However, working with stored procedures becomes easier than working with dynamic SQL after you understand how to use ADO with stored procedures.

Learn more: ADO and XML