After you open the Recordset, you can use the MoveFirst, MoveLast, MovePrevious, and MoveNext methods on the object to move through the rows. Of course, if you chose a Forward-only cursor, only the MoveNext method will work. Because the Fields collection is the default property in the Recordset object, Recordset("ColumnName") = value sets the specified column to the value, and variablename = Recordset("ColumnName") sets the variable equal to the value in the named column of the current row.
However, to save changes to the values in the current row's columns, you must execute the Update method. Otherwise, any changes you made will be discarded when you move to the next row. Similarly, if you want to add a row to the Recordset, you must call the AddNew method, which inserts a new row into the Recordset and makes it the current row. When you finish making changes, call the Update method to save them.
Two other Recordset methods also apply: NextRecordset and GetRows. Most people who work with SQL Server recognize that stored procedures and command batches can return multiple rowsets to the client. ADO supports SQL Server returning multiple rowsets to the Recordset object via the NextRecordset method. Calling this method closes the current cursor and tells SQL Server to send the results of the next query. If you have a stored procedure to execute multiple queries, you will find this method particularly useful.
GetRows returns the Recordset's current contents in a dynamic array. You can use the array as an in-memory copy of your data to speed data processing and eliminate locking and storage problems on the server. In general, looping through an array is faster than looping through the rows in a Recordset if you have to visit the rows multiple times.
The Command Object
The Command object differs from the Recordset object in two ways: It doesn't store rows internally, and it can both accept parameterized queries and call stored procedures. You can think of the Command object as a special way of executing SQL statements that are more complex than the Recordset object can handle.
| ADO RESOURCES
To find information about Microsoft's data-access technologies, start at http://www.microsoft.com/data. Get into the habit of checking this Web site every few weeks. This site is where you'll find all the documentation for ActiveX Data Object (ADO) 2.0 and the new Object Linking and Embedding Database (OLE DB) 2.0 data providers. In the next few months, Microsoft will release these products including the native SQL Server provider for both SQL Server 6.5 and SQL Server 7.0. You'll also find the software development kits (SDKs) for all the data-access options, including Open Database Connectivity (ODBC), Remote Data Object (RDO), Data Access Object (DAO), Remote Data Services (RDS), OLE DB, and ADO. In general, if you need information about accessing data, this site is the place to go. If you use ADO primarily in server-side scripting, Microsoft's Site Builder site (http://www.microsoft.com/sitebuilder) is another important Web site, especially for Web developers. During the past 2 years, this site's articles have taught me most of what I know about Web programming, including how to use ADO within Active Server Pages (ASP).
My Web site, http://www.MCSDonLine.com, is where I collect information about developer-related topics. Specifically, that is where I keep my list of recommended books along with useful Web links and the code examples I use when I teach. |
Instead of the Source property, the Command object has a CommandText property. It accepts a standard SQL statement, calls to queries with parameters, and calls to stored procedures. Listing 3 shows how to use a Command object to call the stored procedure FindTitles. The CommandText property contains the name of the stored procedure that already exists on the server, and the adCmdStoredProc value stored in the CommandType property tells ADO the text in CommandText refers to a stored procedure.
After setting the CommandText and CommandType properties, call the Refresh method on the Parameters collection. The Refresh method tells ADO to find the stored procedure on the server, get the list of parameters the stored procedure has defined, create a separate Parameter object for each one, and append the object to the Parameters collection. The subroutine in Listing 3 configures the object but does not call the stored procedure.
Calling the Execute method tells ADO to call the stored procedure on the server. Listing 4 shows the first parameter set to the Pub_id of the currently selected publisher and calls the Execute method. The statements in Listing 3 and Listing 4 are equivalent to the T-SQL statement Execute @RetVal = FindTitles('736'), if we assume 736 is the current publisher ID.
The rsTmp Recordset object holds the rows from the Titles table that have the Pub_id specified in the parameter. In this case, you must use a Command object to retrieve the records because Recordset objects cannot accept queries with parameters or calls to stored procedures. The only real difference between Listing 2 and Listing 4 is that Listing 4 uses the Command object's Execute method on the Command object, and Listing 2 uses the Open method on the Recordset object.
This ability to handle parameters lets you take advantage of the improved performance you gain by calling a stored procedure rather than sending dynamic T-SQL commands to SQL Server. If an application sends a SELECT statement to SQL Server directly, the server has to parse the query, find all the referenced objects, look up the available indexes, work through the optimization algorithm, and create an execution plan for the engine to run. Because a stored procedure is compiled at creation, SQL Server can skip all the early steps and start running the execution plan. For queries running many times and varying only in the WHERE clause values, compiling into stored procedures can greatly improve performance. Prepared SQL offers most of the advantages stored procedures offer.
For example, Listing 5 shows the parameterized SQL Select statement Select pr_info From pub_info where Pub_id = ?. The question mark tells ADO you will change the value of Pub_id before you execute the query, so the Command object will create a Parameter object to hold a place for the value. This approach lets you control which rows SQL Server returns according to a value the application sets.
Exactly what happens under the hood depends on which version of SQL Server you use and on the setting of the Prepare property. If Prepare is False, ADO sends SQL Server the text in the CommandText property after it replaces the question mark with the value in the Parameter object. If Prepare is True, you have different scenarios for SQL Server 4.21a, SQL Server 6.5, and SQL Server 7.0.
For SQL Server 6.5, MSADSQL and SQLOLEDB use the text from the CommandText property to create a temporary stored procedure in the tempDB database. When you call the Execute method, the driver executes the temporary stored procedure with the values from the Parameter objects, if any. Although you can control how long the temporary stored procedure remains in tempDB, the default is to delete it when the connection drops.
Because SQL Server 4.21a doesn't support creating temporary stored procedures, the MSDASQL provider creates permanent stored procedures in the user database instead of tempDB. Otherwise, the process runs the same as SQL Server 6.5's process runs. However, SQL Server 4.21a won't delete the stored procedures when the connection drops because they are permanent. Thus, a buildup of orphaned stored procedures can occur if your application unexpectedly loses connection.
SQL Server 7.0 bypasses creating stored procedures. MSDASQL and SQLOLEDB tell SQL Server 7.0 to compile the text in the CommandText property and store the execution plan in the procedure cache. ADO then jumps directly into the execution plan after it substitutes the parameters. From that point on, SQL Server must recompile the command only if the execution plan is flushed from the procedure cache.
A Simple Beginning
If you have simple data-access needs, this article will bring you up to speed quickly. The sample code contains additional comments to help you understand how to put the pieces together. ADO's capabilities extend far beyond this introduction.