Returning Recordsets

For a recordset-returning command you would use the Execute method. However, unlike the Connection object, you do not specify the command text in the Execute method itself - you have to use the CommandText property:

Set cmdAuthors = Server.CreateObject("ADODB.Command")
cmdAuthors.CommandText = "Authors"
Set rsAuthors = cmdAuthors.Execute

This is the simplest way to tell the Command object to run a simple command that returns a read-only recordset.

The Execute method also has some optional arguments:

The RecordsAffected and Options are as previously explained, although you can set the command type by using the CommandType property:

Set cmdAuthors = Server.CreateObject("ADODB.Command")

cmdAuthors.CommandText = "Authors"
cmdAuthors.CommandType = adCmdTable

Set rsAuthors = cmdAuthors.Execute

You can also set this on the Execute line itself - if you're not setting the other arguments though, you must still include the commas for them:

Set rsAuthors = cmdAuthors.Execute(, , adCmdTable)

We'll look at the use of the Parameters argument a little later in the chapter, when we deal with stored procedures and parameters.

Changing the Cursor Type

One important thing to note about recordsets that are returned from the Execute method is that they have the default cursor and lock types. This means they are forward-only, read-only recordsets. There's no way to change this using the Execute method, but there is a way around this problem.

If you need to use a command, and require a different cursor or lock type, then you should use the Open method of the Recordset, but use the Command as the source of the Recordset. For example:

cmdAuthors.ActiveConnection = strConn
cmdAuthors.CommandText = "Authors"
cmdAuthors.CommandType = adCmdTable

rsAuthors.Open cmdAuthors, , adOpenDynamic, adLockOptimistic

Notice that the connection details on the Open line have been omitted, because the connection is set in the Command. The connection details could also have been set in the ActiveConnection property of the Command before it is opened.

Action Commands

For action commands, such as those that update data without returning any records, the procedure is similar - you just leave off the bits that set the recordset:

Set cmdUpdate = Server.CreateObject("ADODB.Command")

strSQL = "UPDATE Titles SET Price = Price * 1.10" _
& " WHERE Type='Business'"

cmdUpdate.ActiveConnection = strConn
cmdUpdate.CommandText = sSQL
cmdUpdate.CommandType = adCmdText

cmdUpdate.Execute , , adExecuteNoRecords

Notice that we set the command type in one place and then add any extra options in the Execute line. This runs the UPDATE command and ensures that no recordset is created.