You can also use the Parameters collection of the Command object to supply the stored procedure's parameters. The syntax for the methods and properties of the Parameters collection is straightforward. However, it can be awkward to create many para- meter statements every time you want to use the Parameters collection. Callout B in the online listing shows how to use the Parameters collection in the cmdGet1TitleParam_Click procedure:
cmd.Parameters.Append
cmd.CreateParameter("TitleID", adChar,
adParamInput, 6, _
Trim(txtTitleID))
This line of code first calls the CreateParameter method of the Command object to create a new parameter named TitleID. This new input parameter has a data type of adChar and is six characters long. You set the value of the parameter from the txtTitleID textbox. Next, this code calls the Command object's Append method to append the new parameter to the command. This line of code might seem long, but it keeps the listing simple and accomplishes the task efficiently.
Alternatively, you can create named parameters and use them when you need to. You use the same syntax with the Create-Parameter method as with the above cmd.Parameters.Append command. Instead of passing the parameter into the Command object's Append method, you create the parameter and set a reference to it, as the cmdGet1TitleParam_Click event procedure in Callout B in the online listing illustrates. The command procedure creates the parameter in the error-handling section of the code:
Set mParam = mcmd.CreateParameter("TitleID",
adChar, adParamInput, 6, Trim(txtTitleID))
The mParam variable points to the Parameter object. You can use the pointer to append the Parameter object to the Command object:
mcmd.Parameters.Append mParam
You might wonder why you would create a pointer to the Parameter object instead of simply using the original method, which directly appends the Parameter object and doesn't bother to create an object reference. The answer is that the parameter is reusable. The cmdSetParam_Click procedure shows how you can reuse the parameter in the If statement early in the procedure:
If mParam.Value > "" Then
mParam.Value = txtTitleID
End If
This code simply sets the parameter's Value property to the new title ID. After the Value property changes, you can execute a new command with the command object or reopen the recordset to retrieve the new record. When you create and reuse the parameter object, you can easily set and reset parameters in your code. You can use the Refresh method of the Parameters collection to pull the attributes for the parameters. The Refresh method frees you of the need to specify them, but it generates an extra call to the server, which slows the application.
When you use a stored procedure that doesn't return a recordset, you can use the Execute method of either the Command or the Connection object to execute that stored procedure. If you don't want ADO to return a recordset, you can execute the command with the adExecuteNoRecords option:
cmd.Execute , , ADODB.adExecuteNoRecords
The adExecuteNoRecords option can speed up your application because ADO doesn't generate a recordset. Use this option only when the stored procedure or SQL statement performs an action that doesn't return a recordset.
ADO's Future
Microsoft touts ADO as a universal data-access technology. My tests of the code in this article demonstrate the portability of ADO across programming languages and confirm the universal data-access claims. I did most of the tests in VB 6.0, ported most of the code to ASP, then reran the tests. As I expected, all test results from VB and ASP were the same.
Windows 2000 (Win2K) includes ADO 2.5. This latest version of ADO continues to provide new features for developers, such as the Stream object and the new Record object, that you can use with recordsets. You can open a Record object directly with ADO's Open method, or you can use the Open method to open a record from a recordset or URL. You can use the new Stream object to dynamically store data in memory in a way similar to how you save a recordset to disk in ADO 2.1.
ADO 2.5 also lets you work more easily with non-structured data, such as file systems or message folders, which helps when you're working with non-relational data in your applications. You can work with various types of data, query, add, and change data, and search through collections of non-relational data.
For more information about ADO, see VB Toolkit, "Saving and Restoring Recordsets" (November 1999) and "Using ADO Parameters," Web Exclusive Article ID 7849 on the SQL Server Magazine Web site at http://www.sqlmag.com.
Mastering ADO with VB and ASP can be easy if you try these examples and learn how they work. You'll benefit from increased performance when you use ADO with implicit and explicit connections, command types, stored procedures, the Parameters collection, and the new ADO 2.5 features.
End of Article
Prev. page
1
2
[3]
next page -->
