ASP 101 - Active Server Pages 101 - Web05
The Place ASP Developers Go!

Please visit our partners

Windows Technology Windows Technology
15 Seconds
ASP 101
ASP Wire
VB Forums
VB Wire
internet.commerce internet.commerce
Partners & Affiliates

ASP 101 is an site
ASP 101 is an site
Internet News
Small Business
Personal Technology

Corporate Info
Tech Jobs
E-mail Offers

ASP 101 News Flash ASP 101 News Flash

 Top ASP 101 Stories Top ASP 101 Stories
The Top 10 ASP Links @
What is and Why Do I Need It?
An Overview of ASP.NET

Upgrade to the 5.5 Scripting Engine
Show All Tips >>
ASP 101 RSS Feed ASP 101 Updates


Connections, Commands And Procedures

In the previous chapter, we looked at the basics of ADO, concerning ourselves mainly with the Recordset object and the manipulation of data. In most of the examples, we obtained data by simply specifying the name of a database table, but as you've seen from the object model, ADO has other objects that allow data to be accessed.

In this chapter we are going to look at some of these in more detail. In particular, we shall examine:

  • The Connection object, and how it can be used to retrieve data and run commands
  • The Command object, why it is necessary, and what facilities it has
  • How to run stored procedures, especially those that require parameters
  • Some simple optimization techniques for improving ADO performance
  • What Data Shaping is, and how it can be used

Like the Recordset object, we're not going to cover all of the methods and properties of the objects involved. Instead, we're going to cover the most important topics, and those methods and properties that are most applicable for an ASP developer.

The Connection Object

We mentioned in the previous chapter that the Connection object is what gives us a connection to the data store, but that's not all the Connection object does. As well as storing details of the connection (such as the type of data store and the features it supports), we can use the connection to run commands.

These commands can be action queries, such as updates, inserts or deletes, as well as commands that return a recordset. You might wonder what use this is, since we have the Recordset object, but it's all part of the flexibility of ADO, that allows you to use whichever object is the most convenient, and most suited to the task in hand.

The commands run from the Connection object are generally action queries, but it's useful to know that you can get recordsets returned too.

Returning a Recordset

To return a recordset from the Connection object you use the Execute method. The syntax of this method is:

Connection.Execute CommandText, [RecordsAffected], [Options]

The arguments are:

The Execute method optionally returns a recordset, in which case you simply assign the Recordset variable as the return value. For example:

You might wonder what the difference is between using the Execute method of the Connection object and the Open method of the Recordset object. It may not seem that there's much difference, but remember that with the Open method of the Recordset you have the ability to change the cursor type and lock type of the resulting recordset. These options are not available for the Execute method of the connection, so you will always get a forward-only, read-only recordset.

Action Commands

If you are running action commands, such as a SQL UPDATE statement, then you can use the RecordsAffected argument to find out how many records were affected by the command. For example:

This updates the price for all business books by 10%. Once the Execute command has completed, the number of titles affected by the update will be available in lngRecs - this is the RecordsAffected argument.

Notice that we've specified adCmdText for the options of this command - this tells ADO that the command text is a text command. Whilst this isn't strictly necessary, it does allow ADO to know ahead of time what sort of command is being executed, and therefore improves performance. Remember that this value can be one or more of the values from the CommandTypeEnum values.

No Recordset Returned

If no recordset is being returned, as in the example above, then it's also best to add another option to the Execute statement:

Using adExecuteNoRecords tells ADO that the command being executed does not return any records. ADO therefore doesn't bother building a recordset. If you omit this option then ADO builds an empty recordset, which is clearly a waste of time, so adding the option will speed up the statement.

©1999 Wrox Press Limited, US and UK.
Home |  News |  Samples |  Articles |  Lessons |  Resources |  Forum |  Links |  Search |  Feedback
The Network for Technology Professionals



Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers