ADO Command and Stored Procedures

You can design stored procedures to hide such complexities, leaving a more concise interface available for application development.

The command object has the power to change the query each time it is used. The code below demonstrates how to use the ADO command object with these stored procedure. First, create the command object and connect the connection object to the command object.

'create a command object
set cm = Server.CreateObject("ADODB.Command")
'connect the command
cm.ActiveConnection = cn

Next, specify the query.

Method 1

set cm.CommandText = "Select * from schools"

Method 2 (Table)

set cm.CommandText = "schools"
cm.CommandType = adCmdTable

Method 3 (Stored Procedure)

set cm.CommandText = "add_school"
cmdCommandType = adCmdStoredProc

Method 4 (Stored Procedure with Parameters)

set cm.CommandText = "add_school"
cm.cmdCommandType = adCmdStoredProc

set p = cm.Parameters

p.Append cm.CreateParameter("@style",adChar,adParamInput,50)
p.Append cm.CreateParameter("@school", adChar, adParamInput,50)
p.Append cm.CreateParameter("@id",adInteger,adParamInput)

cm("@style") = "Kempo"
cm("@school") = "WSU"
cm(Id) = 1

cm.execute

Method 5 ( Return the results to a recordset)

rs.Open cm, cn

Method 6 ( Recordset, type, and locking method)

rs.Open cm, cn, adOpenKeyset, adLockOptimistic