The LA Fox Developer Newsletter

July 2000

ADO Jumpstart (Con’t from page 6)

series of objects in order to gain access to other objects. For example, the following is one way to create and open both a Connection and a Recordset object:

oConnection CreateObject(”adodb.connectiofl”)

oRecordset CreateObject(””adodb.recordset””)

With oConnection .Provlder ““SQLOLEDB.I”

.ConnectionString = “Persist Security lnfoFalse;User

ID=sa;lnitial Catalog=Nothwind;Data SourceJVP” .Open

EndWith

With oRecordset .ActiveConnection oConnection .Source “Products’”’ .Open

EndWith

Here is another way to create the two objects:

oRecordset = CreateObJect(”adodb.recordset”) With oRecordset

.ActiveConnection “ProviderSQLOLEDB.l ;Persist Security Info=False;User ID=sa;lnitial CatalogNorthwind;Data

Source=JVP”

.Source = “Products”

.Open

EndWith

Now, you can reference the Connection object because it has been implicitly created from the passed connection string:

?oRecordset.ActiveConnectlon.COflflectiOflStllflg

The same is true for the Command object. While a Command object was not explicitly created, a Command object was in fact created and actually did the work of creating the recordset. Using the recordset just created, the following command will yield “Products” as the CommandText:

?oRecordset.ActiveCommand.CommafldTeXt

Which method should you use?

It is really a matter of preference. The latter method, which uses only the RecordSet object, is somewhat overloaded. It carries the same overhead as the former method because you must still create a Connection object The former method is probably a better way to go as it makes for more readable code.

Parameters Collection

The Parameters collection works with the Command object. The primary use of the Parameters Collection is to both pass arguments to, and accept return values from stored procedures. To illustrate, consider the CustOrderHist stored procedure in the SQL Server Northwind database:

CREATE PROCEDURE CustOrderHist @CustomerlD nchar(5)

AS

SELECT ProductName, TotaISUM(Quantity)

FROM Products P, (Order Details] OD, Orders 0, Customers C

WHERE C.CustomerlD = @CustomerlD

AND C.CustomerlD = O.CustomerlD AND O.OrderlD = OD.OrderlD AND

OD.ProductlD = P.ProductlD GROUP BY ProductName

To illustrate how the Parameters collection is used in conjunction with the Command object, consider the following comprehensive example:

First, you need to establish a valid connection:

oConnection CreateObJect(”adodb.connection”)

Next, the connection needs to be opened.

With oConnection .Provlder “SQLOLEDB.1”

.ConnectionString = “Persist Security Info=False;User

ID=sa;lnitial CatalogNorthwlnd;Data SourceJVP” .Open

EndWith

With a valid, open connection, a Command object can be prepared:

With oCommand .ActiveConnection = oConnection .CommandText = “CustOrderHist”

.Commandlype = adCmdStoredProc && adCmdStoredProc = 4 EndWith

At this point, information can be obtained from the Parameters collection:

For Each Parameter in oCommand.Parameters ?Parameter.Name,Parameter.Size,Parameter.Type

Next Parameter

The first Parameter object is reserved for the value that the stored procedure may return. Regardless of whether the stored procedure explicitly returns a value, this Parameter object will be created. Examining the CustOrderHist stored procedure, note that a single argument, a customer ID, is accepted.

With a Command object and Parameter object in place, the real work can begin. To get things rolling, a value needs to be assigned to the Parameter object that will in turn be passed to the stored procedure. In this case, a SQL statement is executed that totals the quantity, by product, that a specified customer has purchased. The following code provides a customer ID and executes the stored procedure:

oCommand.Parameters(”@CustomerlD”).Value = “ALFKI” oRecordset = oCommand.Execute

Yet another way to produce a Recordset object is through the execution of a stored procedure. The resulting Recordset object contains two fields that correspond to the select statement in the CustOrderHist stored procedure. Need a different history? Just update the Value property of the Parameter object and invoke the Execute method of the Command object.

The Parameters collection also comes into play in the area of parameterized queries. Consider the following SQL Statement:

(Con’t, page 8)

Page 7