|
|
|
|
|
|
|
|
|
|
|
|
|
|
ASP ADVANCED
All communication with a
database takes place through an open connection.
Before you can insert or retrieve any information in a database, a
connection with the database must be opened.
This process of opening and closing a connection is often compared
with the process of placing a telephone call.
Before you can communicate with SQL Server, you must first call it. This section describes how
to use the ADO connection object. First
you learn how to open and close a connection to a database.
Next, you learn how to execute SQL statements with an open
connection. Finally, you
learn how to use the Connection object to create transactions. Opening and Closing a
Database Connection To open a connection with a
database, you can create an instance of the connection object.
Once an instance of this object is created, you can call the open
method of the connection object to actually open the connection.
Here's an example: <% In this example, an
instance of the Connection object named MyConn is created.
The Open method is called with the name of a file data source.
Next, the Execute method of the connection object is called to
execute a SQL statement. Finally,
the connection is closed. Any same person quickly
grows tired of entering the name of the file data source every time a new
connection needs to be opened. You
should assign this string to a session variable or make it a constant in
an include file. This way,
you only need to type the name of a variable rather than the whole name of
the file data source. To
create a session variable that contains the name of the file data source,
you should create this variable within the Global.asa file.
For example, you can add the following line to the
Session_OnStart script of the Global.asa file: After this session
variable has been created, you can open a connection by using the
following script: Another advantage of
assigning the file data source name to a session variable is that you can
easily change the data source in the future.
If you need to use a different data source, you can simply change
the value of a single session variable in the Global.asa file. When you're finished using
a connection, you should close it. This
is like hanging up a phone so that you're no longer tying up the line.
The close method of the Connection object closes a connection.
After a connection is closed, you can no longer use the connection
to communicate with the database. Therefore,
objects that depend on the connection can no longer communicate with the
database. Executing SOL Statements with an Open Connection To execute a SQL statement
with an open connection, you use the Execute method.
This method has two forms: One form is used when retrieving results
from a database, and the other form is used when no results are returned. The following example
shows how you can use the Execute method to execute a SQL statement that
doesn't retrieve any results: <% In this example, the
Execute method is used to execute a SQL INSERT statement.
Because no results are returned, the Execute method doesn't use
parentheses. You can also use the
Execute method to return results from a SQL query, as in this example: <% In this example, the
Execute( ) method is used to return the results from a SQL SELECT query.
Unlike the preceding example, parentheses are used with this
Execute method. When
returning results, you must remember to include the parentheses, or you'll
get the error Expected end of statement. The results of the SQL
query are retrieved into an instance of a Recordset object named RS.
This Recordset is automatically created by the Execute( ) method.
You learn how to work with Recordsets in the next chapter. The Execute method includes
two optional parameters. You
can provide a RecordsAffected parameter that indicates the number of
records that the SQL statement affected.
You can also include an Options parameter that provides information
about the type of SQL statement being executed.
This example uses both of these optional parameters: <!-- #INCLUDE
VIRTUAL="ADOVBS.inc"--> In this script, a SQL
UPDATE statement is executed to change the values of all the rows in the
MyTable table. The Execute
method also has two additional parameters.
The first parameter is the RecordsAffected parameter.
In this example, the variable named HowMany is passed as the
RecordsAffected parameter. After
the SQL statement executes, this variable will contain the number of
records that the SQL statement affected.
For example,if 32 rows in the table are updated, the value of
HowMany will be 32. The second parameter
included with this Execute method is the options parameter.
In this example, the options parameter is specified as the constant
adCMDText. This constant is
used to warn the ADO that it should interpret the contents of the command
string to be a textual command rather than the name of a table or a stored
procedure. By warning the ADO
about the contents of the string being executed, the constant makes ADO
execute the command more efficiently. (For more on commands, see Chapter
"Working with Commands.") You can use the
following constants for the Options parameter:
Before you can use any of these constants in an Active Server Page, you must include a special file called the ADOVBS.inc file. The ADOVBS.inc file contains all the VBScript constants that are used with the ADO. The first line in the preceding example contains an INCLUDE directive to include the ADOVBS.inc file. When you installed
Active Server Pages, this file should have been installed automatically as
well. Currently, the file is
installed into the c:\Program Files\Common Files\System\ADO directory.
However, you may have to use the Find command from the Windows NT
Start menu to find the exact location of this file.
After you find it, copy the file into your Active Server Pages
directory. You can call the Execute
method as many times as you need after a connection is open.
For example, the following script enters 32 different strings
into the table named MyTable: <!-- #INCLUDE
VIRTUAL="ADOVBS.inc"--> This script uses a
FOR...NEXT loop to insert 32 records into the MyTable table.
The variable MYSQL contains the SQL command string used with the
Execute Method. Notice how
single quotes and double quotes are used when specifying the SQL string.
The single quotes are Used to represent the quotation marks that
appear within the SQL statement. The
double quotes specify the beginning and end of the string within VBScript. You can use the Execute
method to execute almost any SQL command.
For example, the following script creates a table, populates it,
truncates it, and then drops it: <!-- #INCLUDE
VIRTUAL="ADOVBS.inc" --> Creating Transactions When a group of statements
form a transaction, if one statement fails, they all fail.
Transactions are useful when you need to update information in more
than one table and you don't want one update to fail and the other to
succeed. For example, suppose
whenever someone buys something from your Web site, the information about
the purchase is stored in two tables.
One table contains a list of credit cards to be debited.
The second table contains a list of items to be shipped. Now, suppose someone
attempts to buy something from your Web site.
The person's credit card number is entered into the first table.
However, at that very moment, disaster strikes.
A bolt of lightning hits your server and the second table doesn't
get updated. In this
situation, it would be much better if neither table is updated.
You don't want to charge the person for buying an item that will
never be shipped. Using
transactions, you can prevent the credit card number table from being
updated if the shipping table is never updated: <% In this example, the
BeginTrans and CommitTrans methods are used to mark the beginning and end
of a transaction. After the
BeginTrans method call, if anything goes wrong before CommitTrans is
called, the tables are not updated. If
lightning strikes after the first table is updated, this change is
automatically rolled back. You can also roll back a
transaction explicitly. To do
this, you use the RollBackTrans method.
Consider the following script: <% In this example, the RollBackTrans method is used to explicitly roll back the transaction on Sunday. On Sunday, neither the CreditCard table nor the Shipping table will be updated. Summary This chapter introduced the
ActiveX Data Objects. The first section gave you an overview of the ADO.
In the second section, you learned how to configure your server to use the
ADO. Finally, you were introduced to one of the most important of the ADO
objects: the Connection object.
|
 |
||||
|
|
||||||
|
|
||||||
|
|
|
 |
