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 conjunc-

tion with the Command object, consider the following compre-

hensive 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 ex-

ecuted that totals the quantity, by product, that a specified

customer has purchased. The following code provides a cus-

tomer 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