14.1.1- Using the Command Object

<< 14.1.0- The Command Object

Chapter14

14.1.2- The Structured Query Language (SQL) >>

Using the Command Object

We can create a Command object by using the CreateObject method of the ASP Server object:

 

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

 

This statement creates a new Command object called objCommand. This should look familiar; we've already used the same technique to create Connection and Recordset objects.

Executing a Command

In order to run a command, we use the Command object's Execute method. The Execute method has three parameters:

 

objCommand.Execute RecordsAffected, Parameters, Options

 

All three arguments are optional – if you omit any of them then the Execute method will adopt the default value for that parameter. Let's have a look at them:

 

• The RecordsAffected parameter is for action queries (those that insert, update or delete data and, thus, don't return a recordset). If you want to know the number of records that were affected by the command, then you can pass a variable name into this parameter – when the command is complete, the number of affected records will be placed into this parameter.

It's worth noting that this is the first ADO method we've met that is capable of returning information to you within one of its parameters.

• The Parameters parameter holds an array of parameters that are to be passed to the command. This means that we can write a generic command and use it lots of times. Each time we execute the command, we can make it specific by using the command's parameters to pass in the information specific to this one Execute call. Don't confuse this with the Parameters collection, which we'll be looking at in more detail later in the chapter when we look at stored procedures and queries. Also bear in mind that a parameter value that you provide in the Parameters parameter will override a value specified in the Parameters collection.

We write our generic commands using a query language called SQL. We'll meet SQL properly after the following example.

• The Options parameter is like the Options parameter in the Recordset.Open method. It specifies the type of command being run.

Specifying the Command

It's all very well that we can execute a command, but you might have noticed that there's nothing in the Execute method that specifies what command we want to run. Well spotted! For that, we need to add something to the CommandText property:

 

objCommand.CommandText = "Movies"

objCommand.Execute , , adCmdTable

 

In this case, we've specified the name of the database table, Movies, as the command text. (We'll see later that we can use SQL to create more complex queries than this.) Then we use the Execute method to execute the command. Notice that, in this code fragment, the first two arguments have been omitted; however, we've left the two commas in because we want to specify a value for the third parameter. The third parameter itself is used to specify the type of command being run – and so, since the command text is in the form of a table name, we've used the ADO constant adCmdTable to reflect that (this gives ADO the opportunity to optimize the way the command is executed).

 

Instead of the two lines above, we could achieve the same result by taking an extra line to set the command type, using the CommandType property, like this:

 

objCommand.CommandText = "Movies"

objCommand.CommandType = adCmdTable

objCommand.Execute

 

This time, we don't need to specify the command type as a parameter of the Execute method, because we'd already specified it by setting the CommandType property explicitly.

Specifying the Connection

There's also been no mention of the connection: so how does the command know which data store to use? Well, from what we've shown you so far it doesn't! So before we use the Command object's Execute method, we need to assign its ActiveConnection property too.

 

The ActiveConnection property can be a Connection object or a connection string. This example uses a connection string, which has been previously defined and stored in a variant called strConnect:

 

objCommand.ActiveConnection = strConnect

objCommand.CommandText = "Movies"

objCommand.CommandType = adCmdTable

objCommand.Execute

Capturing any Selected Data

In this case, the command is a 'select' type command – we're asking the data store to select all of the data in the Movies table and to return it to us in the form of a recordset. So in this case, the Execute method actually returns a physical ADO Recordset object!

 

If we want to use that recordset elsewhere in our program, we should give it a name so that we can reference it. We can do that by using the Set statement to assign the result of the Execute statement to a variant of our choice:

 

objCommand.ActiveConnection = strConnect

objCommand.CommandText = "Movies"

objCommand.CommandType = adCmdTable

Set objRS = objCommand.Execute

 

So, now we've arrived at a set of statements that make a lot of sense, given that the code is just returning the Movies table. Let's give it a go in an example page.

1.    We're going to use the Movies database and the strConnect connection string, which we first set up in Chapter 12 and which we used all through Chapters 12 and 13 . For this, you'll need to ensure that your Movie2000.mdb or Movie2000.mdf file is set up as described in Chapter 12; you'll also need the DataStore.asp SSI file placed in the \inetpub\wwwroot\BegASPFiles folder, as we explained in Chapter 12.

2.    In your editor, create a new file, which we'll call Command.asp. Into this file, add the following code:

<%

Option Explicit

Dim strConnect

%>

<!-- #include file="DataStore.asp" -->

<!-- METADATA TYPE="typelib"

FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->

<HTML>

<HEAD>

<TITLE>ADO Command Object</TITLE>

</HEAD>

<BODY>

 

<%

Dim objCommand, objRS

Set objCommand = Server.CreateObject("ADODB.Command") ' create the Command object

 

' fill in the command properties

objCommand.ActiveConnection = strConnect

objCommand.CommandText = "AllMovies"

objCommand.CommandType = adCmdTable

 

' now execute the command, and capture the selected records in a recordset

Set objRS = objCommand.Execute

 

' The Command object has done its job, so clean it up

Set objCommand = Nothing

 

' now loop through the records

While Not objRS.EOF

Response.Write objRS("TitleID") & ": " & objRS("Title") & "<BR>"

objRS.MoveNext

Wend

 

' now close and clean up

objRS.Close

Set objRS = Nothing

%>

</BODY>

</HTML>

 

3.    Save the Command.asp file into your BegASPFiles directory.

4.    View the Command.asp page in your browser:

As you can see, we've just used the Command object to perform the same task of opening and populating the Recordset object – effectively, it has done the same job that we've done previously using the Recordset object's Open method. In fact, you can mix-and-match the Recordset's Open method with the functionality of the Command object, because the first parameter of the Open method (the Source parameter) is able to take a value in the form of an ADO Command object, like this:

 

objCommand.ActiveConnection = strConnect

objCommand.CommandText = "Movies"

objCommand.CommandType = adCmdTable

objRS.Open objCommand

 

This sort of flexibility among the ADO objects is another demonstration of the 'flat hierarchy' of the ADO object model. Using a Command object doesn't depend on having first created a Recordset, or a Connection object. By contrast, if you've got an existing Recordset object then you can use it to run a command (as we've done here) – so you're not forced into creating a new Recordset object if you don't want one. Using ADO objects is a mix'n'match scenario – and it's useful, because it means that you're not forced to create any more objects in your code than you actually intend to use.

 

While we're talking of the flexibility of the ADO objects, let's return briefly to the Connection object and see how that handles commands.

The Connection Object's Execute Method

When we looked at the Connection object, back in Chapter 12 , we touched very briefly on its Execute method, but we deliberately left an explanation of that method until now – because, with a better understanding of recordsets and commands, we can now get a better appreciation of what it does.

 

The Connection object's Execute method is a little different in its syntax from the Command object's Execute method, even though the two methods have the same name. Don't get mixed up in this section! The full list of parameters for the Connection object's Execute method is:

 

objConn.Execute CommandText, RecordsAffected, Options

 

In fact, if the command is one that selects and returns records, we can use a variant to refer to the returned recordset (and thus use the recordset elsewhere in the code), like this:

 

Set objRS = objConn.Execute CommandText, RecordsAffected, Options

 

The parameters here are slightly different to the parameters of the objCommand.Execute method – and they're in a different order. Here:

 

• The CommandText parameter can contain the command text (so it corresponds to the Command object's CommandText property)
• The RecordsAffected and Options parameters work just like their namesake parameters in the Command object's Execute method

So in fact, we don't really need to revisit this, because the functionality provided is very similar to that we've already seen for the Command object. We've included it here to reinforce and demonstrate the flexibility of the 'flat hierarchy' of the ADO object model once again.

 

So far in this book, the only commands that we've used have been 'select' commands – in which we select particular records and/or fields from a table (or indeed, the whole table), and have the data store return those data in the form of a recordset. But the process of selecting fields and records is much more subtle and clever than we've shown so far. Moreover, as we hinted in the first paragraphs of this chapter, data access isn't only about selecting data from the data store – we want our commands to be able to do other things too.

 

In order to learn more about these different types of commands, we need to know a little of the basic language in which they are submitted – and that language is SQL.

<< 14.1.0- The Command Object

Chapter14

14.1.2- The Structured Query Language (SQL) >>