Use ADO to access data sources in ASP

ActiveX Data Object (ADO) is an easy-to-use and scalable technology, to add database access to your Web page, you can use ADO to write simple and upgradeable scripts to connect to data sources compatible with ole db, such as databases, workbooks, ordered data files, or email directories. Ole db is a system-level programming interface that provides a set of standard COM interfaces to demonstrate the functions of the database management system. With the ADO object model, you can easily access these interfaces (Using VBScript or JScript or other scripting languages) and add database functions to your Web applications. In addition, you can use ADO to access databases compatible with Open Database Interconnection (ODBC.

If you are a script writer who has some knowledge about database interconnection, you will find that the ADO command syntax is very simple and easy to use. If you are an experienced developer, you will appreciate the upgraded high-performance access to various data sources provided by ADO.

For more information about ADO, visit the Microsoft Universal Data Access (UDA) Web site http://www.microsoft.com/data /.

Create a connection string
The first step in creating a Web application is to provide ADO with a method to locate and identify the data source. This is done through the "connection string". The connection string is a series of parameters separated by semicolons, used to define parameters such as the data source provider and data source location. ADO uses the connection string to identify the ole db "provider" and direct the provider to the data source. A provider is a component used to describe the data source and display the information to the application in the form of a row set.

The following table lists the ole db connection strings of several common data sources:

Data Source ole db connection string
Microsoft Access Provider = Microsoft. Jet. OLEDB.4.0; Source = point to the physical path of the. mdb File
Microsoft SQL Server Provider = SQLOLEDB.1; Source = path to the database on the Server
Oracle Provider = MSDAORA.1; Source = path to the database on the server
Microsoft Indexing Service Provider = MSIDXS.1; Source = point to the file path

To provide backward compatibility, the odbc ole db Provider supports the ODBC connection string syntax. The following table lists the commonly used ODBC connection strings:

Data source driver ODBC connection string
Microsoft Access Driver = {Microsoft Access Driver (*. mdb)}; DBQ = point to the physical path of the. mdb File
SQL Serverr Driver = {SQL Server}; SERVER = path to the Server
Oracle Driver = {Microsoft ODBC for Oracle}; SERVER = path to the SERVER
Microsoft Excel Driver = {Microsoft Excel Driver (*. xls)}; DBQ = point to the physical path of the. xls file; DriverID = 278
Microsoft Excel 97 Driver = {Microsoft Excel Driver (*. xls)}; DBQ = point to the physical path of the. xls file; DriverID = 790
Paradox Driver = {Microsoft Paradox Driver (*. db)}; DBQ = physical path to the. db file; DriverID = 26
Text Driver = {Microsoft Text Driver (*. txt; *. csv)}; DefaultDir = point to the physical path of the. txt file
Microsoft Visual FoxPro (with a database container) Driver = {Microsoft Visual FoxPro Driver}; SourceType = DBC; SourceDb = physical path to the. dbc File
Microsoft Visual FoxPro (without database container) Driver = {Microsoft Visual FoxPro Driver}; SourceType = DBF; SourceDb = physical path to the. dbf File

Note that using a UNC path to reference a data source located on a remote computer may cause potential security issues. To prohibit unauthorized access to your data source, you can create a Windows account for the computer on which you want to access the data, and then apply the appropriate NTFS permissions to the data source. For more information, see use NTFS to protect files.

Advanced considerations when designing Web data applications
Due to performance and reliability, we strongly recommend that you configure data-driven Web applications (which must meet the needs of more than 10 concurrent users for simultaneous access requests, use the database engine of the client server. Although ADO is applicable to any data source compatible with ole db, it has been extensively tested to work with Client Server databases (such as Microsoft SQL Server or Oracle.

ASP supports Microsoft Access or Microsoft FoxPro as a valid data source. Although some examples in the ASP document use a "Shared File" database, we recommend that these database engines be used only for development purposes or limited development solutions. The shared file database may not be as suitable as the client server database for demanding and high-quality Web applications.

If you are developing an ASP database application and the application will be connected to a remote SQL Server database, pay attention to the following points:

Select the SQL Server connection scheme to access the remote SQL Server database. You can select the TCP/IP socket method or the named pipe method. To use a named pipe, Windows must verify the database client before establishing a connection, to prevent remote computers running named MPs queues from rejecting access from users on the computer that have an appropriate SQL Server access certificate but do not have a Windows user account. As another choice, the connection using TCP/IP socket will be directly connected to the database server, instead of connecting through the intermediate computer. If you use an intermediate computer, it is similar to using a named pipe. In addition, TCP/IP socket connections are directly connected to the database Server, so you can obtain access through SQL Server Authentication instead of Windows authentication.
ODBC 80004005 error if the connection scheme to access SQL Server is not correctly set, users who are viewing database applications may receive an ODBC 80004005 error message. To correct this problem, you can use a local named pipe connection to replace the named pipe connection of the network, but SQL Server and IIS must run on the same computer. Windows 2000 security rules are not affected because the MPs queue is a local connection instead of a network connection. It can be simulated by an anonymous user account. Similarly, in the SQL Server connection string (in the Global. asa file or webpage-level script), change the parameter SERVER = server name to SERVER = (local ). The keyword (local) is a special parameter identified by the SQL Server ODBC driver. If this solution does not work, try a non-authentication protocol, such as a TCP/IP socket, between IIS and SQL Server. This protocol works when SQL Server runs on a local or remote computer.
Use a TCP/IP socket to improve performance when connecting to a remote database.

SQL Server security if you use SQL Server's "integration" or "hybrid" security features and the SQL Server database resides on a remote Server, you will not be able to use integrated Windows verification. In particular, you cannot send the integrated Windows authentication certificate to a remote computer. This means that you have to use basic authentication, which requires the user to provide the user name and password information.
For more information about these issues, visit the Microsoft Product Support Services Web site in the http://www.microsoft.com/china/support.

Connect to the data source
ADO provides a "Connection" object for establishing and managing connections between applications and ole db compatible data sources or ODBC compatible databases. The properties and methods of the "Connection" object can be used to open and close the database Connection, and query the updated information.

To establish a database Connection, you must first create a "Connection" object instance. For example, the following script creates a "Connection" object instance and then opens a Connection:

<%
'Create a connection object
Set cnn = Server. CreateObject ("ADODB. Connection ")
'Use the ole db connection string to open the connection
Cnn. Open "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = C: \ MarketData \ ProjectedSales. mdb"
%>
Note that the connection string does not contain spaces around the equal sign (=.

In this case, the "Open" method of the "Connection" Object references the Connection string.

Use the Connection object to execute SQL queries
You can use the "Execute" method of the "Connection" object to publish commands to data sources, such as Structured Query Language (SQL) queries. (SQL is an industrial standard language for communicating with databases and defines commands for retrieving and updating information .) The "Execute" method can receive parameters to specify commands (or queries), the number of affected data records, and the type of commands used.

The following script uses the "Execute" method to publish a query using the SQL "INSERT" command, which is used to INSERT data into the specified database table. In this case, the script block inserts the name "Jose Lugo" into the database table named "MERs.

<%
'Define the ole db connection string.
StrConnectionString = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = C: \ Data \ Employees. mdb"

'Indicates the Connection object and opens the database Connection.
Set cnn = Server. CreateObject ("ADODB. Connection ")
Cnn. Open strConnectionString

'Define the SQL SELECT statement.
StrSQL = "insert into MERs (FirstName, LastName) VALUES ('job', 'lugo ')"

'Use the Execute method to publish SQL queries to the database.
Cnn. Execute strSQL, adw.text + adExecuteNoRecords
%>
Note: Two parameters are specified in the statement used to execute the query: adCmdText and adExecuteNoRecords. The optional parameter adCmdText specifies the command type, indicating that the provider must evaluate the query statement (here the SQL query statement) as the command text definition. The adExecuteNoRecords parameter instructs ADO not to create a data record set when no results are returned to the application. This parameter is only used to define command types for text definitions (such as SQL queries) or stored database procedures. Although adCmdText and adExecuteNoRecords are optional parameters, you must specify these parameters when using the "Execute" method to enhance the performance of data applications.

Important ADO parameters, such as ad1_text, must be defined in the script before use. A convenient way to define parameters is to use the "component type library", which is a file containing all ADO parameter definitions. To implement the component type library, declare it first. Add the following <METADATA> tag to the. asp file or the Global. asa file to declare the ADO Library:

<! -- Metadata name = "Microsoft ActiveX Data Objects 2.5 Library" TYPE = "TypeLib" UUID = "{00000205-0000-0010-8000-00AA006D2EA4}" -->
For more information about implementing the component type library, see use constants in use variables and constant topics.

In addition to the SQL insert command, you can also use the SQL UPDATE and DELETE commands to modify and DELETE database information.

You can use the SQL UPDATE command to change the values of each item in the database table. The following script uses the UPDATE command to change all the FirstName fields in the MERs table that contain the last name Smith to Jeff.

<%
Set cnn = Server. CreateObject ("ADODB. Connection ")
Cnn. Open "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = C: \ Data \ Employees. mdb"
Cnn. Execute "UPDATE Customers SET FirstName = 'jeff 'WHERE LastName = 'Smith'", ad1_text + adExecuteNoRecords
%>
To DELETE the specified records in the database table, run the DELETE command of SQL. The following script deletes all rows with the last name Smith in the MERs table:

<%
Set cnn = Server. CreateObject ("ADODB. Connection ")
Cnn. Open "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = C: \ Data \ Employees. mdb"
Cnn. Execute "delete from MERs WHERE LastName = 'Smith '", ad1_text + adExecuteNoRecords
%>
Be careful when using the SQL DELETE command. The DELETE command without the WHERE clause deletes all rows in the table. Make sure that the SQL WHERE clause is included to explicitly specify the row to be deleted.

Use Recordset object to process results
To retrieve data, check results, and change the database, ADO provides A Recordset object. As shown in the name, the "Recordset" object has the function to retrieve and display database rows (or "records"), depending on your query constraints. The "Recordset" Object retains the position of each record returned by the query, so that you can view all the results, one at a time.

Retrieve record set
A successful Web data application uses both the "Connection" object to establish a link and the "Recordset" object to process the returned data. By combining some of the special features of these two objects, the developed database application can execute almost all data processing tasks. For example, the following server-side script uses the "Recordset" object to execute the SELECT command of SQL. This SELECT command is used to retrieve information sets based on query constraints. This query also contains the SQL WHERE clause, which limits the query to a specified standard. In this example, the WHERE Clause limits the query to all records WHERE the last name field in the MERs database table contains Smith.

<%
'Establish a data source connection
StrConnectionString = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = C: \ Data \ Employees. mdb"
Set cnn = Server. CreateObject ("ADODB. Connection ")
Cnn. Open strConnectionString

'Example Recordset object
Set rstCustomers = Server. CreateObject ("ADODB. Recordset ")

'Use the Open Method to Open a record set
And use the Connection established through the Connection object.
StrSQL = "SELECT FirstName, LastName FROM MERs WHERE LastName = 'Smith '"
RstCustomers. Open strSQL, cnn

'Traverse record set and display result
'And use the MoveNext method to increment the record position.
Set objFirstName = rstCustomers ("FirstName ")
Set objLastName = rstCustomers ("LastName ")
Do Until rstCustomers. EOF
Response. Write objFirstName & "& objLastName &" <BR>"
RstCustomers. MoveNext
Loop

%>
Note: In the preceding example, the "Connection" Object establishes a database Connection, while the "Recordset" object uses the same Connection to retrieve results from the database. This method is useful when you need to precisely configure the method for establishing database links. For example, if you want to specify the time delay before the Connection is terminated, you may need to use the "Connection" object to set this attribute. However, if you only want to use the default connection property of ADO to establish a connection, you can use the "Open" method of the "Recordset" object to create a connection:

<%
StrConnectionString = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = C: \ Data \ Employees. mdb"
StrSQL = "SELECT FirstName, LastName FROM MERs WHERE LastName = 'Smith '"
Set rstCustomers = Server. CreateObject ("ADODB. Recordset ")

'Open the connection using the Open method.
And use the Connection established through the Connection object.
RstCustomers. Open strSQL, strConnectionString

'Traverse the record set and display the result,
'And use the MoveNext method to increment the record position.
Set objFirstName = rstCustomers ("FirstName ")
Set objLastName = rstCustomers ("LastName ")
Do Until rstCustomers. EOF
Response. Write objFirstName & "& objLastName &" <BR>"
RstCustomers. MoveNext
Loop
%>
When the "Open" method of the "Recordset" object is used to establish a Connection, the "Connection" object is implicitly used to protect the link security. For more information, see the Microsoft ActiveX Data Object (ADO) document, which can be found on the Microsoft Universal Data Access Web site http://www.microsoft.com/data.

Note: to significantly improve the performance of ASP database applications, please consider cache records in the "application" status. For more information, see cache data.

The number of records returned in the statistical record set is sometimes useful. The "Open" method of the "Recordset" object enables you to specify optional cursor parameters to determine the methods for potential providers to retrieve and browse record sets. By adding the adOpenKeyset cursor parameter to the statement used to execute the query, the client application can completely browse the record set. Therefore, the application can use the RecordCount attribute to accurately count the number of records in the record set. See the following example:

<%
Set rs = Server. CreateObject ("ADODB. Recordset ")
Rs. Open "SELECT * FROM NewOrders", "Provider = Microsoft. Jet. OLEDB.3.51; Data Source = 'C: \ mermerorders \ Orders. mdb '", adOpenKeyset, adLockOptimistic, adshorttext

'Use the RecordCount attribute of the Recordset object for statistics.
If rs. RecordCount> = 5 then
Response. Write "we have received the following" & rs. RecordCount & "new orders <BR>"

Do Until rs. EOF
Response. Write rs ("CustomerFirstName") & "" & rs ("CustomerLastName") & "<BR>"
Response. Write rs ("AccountNumber") & "<BR>"
Response. Write rs ("Quantity") & "<BR>"
Response. Write rs ("DeliveryDate") & "<BR>"
Rs. MoveNext
Loop

Else
Response. Write "the number of orders is less than" & rs. RecordCount &". "
End If

Rs. Close
%>
Use Command object to improve query
The method for executing a query using the Command object of ADO is the same as that for executing a query using the "Connection" and "Recordset" objects, but the "Command" object can be used to prepare (or compile) query the database source, and send the query repeatedly with different values. The advantage of compiling a query using this method is that when you need to re-release the modified existing query, the release time can be greatly reduced. In addition, you can leave some SQL queries not defined, and use the option to change the query part before execution.

The "Parameters" set of the "Command" object allows you to avoid the trouble of rebuilding the query every time you re-publish the query. For example, if you need to regularly update the supply and cost information of the Web-based inventory system, you can predefine the query as follows:

<%
'Use the Connection object to open the Connection. Note that the Command object
'Does not have an Open method for establishing a connection.
StrConnectionString = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = C: \ Data \ Inventory. mdb"
Set cnn = Server. CreateObject ("ADODB. Connection ")
Cnn. Open strConnectionString

'COMMAND object; Use the ActiveConnection attribute
'Connect to the Command object.
Set CEN = Server. CreateObject ("ADODB. Command ")
Set Cen. ActiveConnection = cnn

'Define SQL queries.
CEN. CommandText = "insert into Inventory (Material, Quantity) VALUES (?, ?) "

'Save the Command object in the CommandText Attribute before it is executed for the first time.
.
CEN. Prepared = True

'Define query parameter configuration information.
CEN. Parameters. Append Cen. CreateParameter ("material_type", adVarChar, 255)
CEN. Parameters. Append Cen. CreateParameter ("quantity", adVarChar, 255)

'Define and execute the First insert operation.
Mn ("material_type") = "Daylight bulb"
CEN ("quantity") = "40"
CEN. Execute, adw.text + adExecuteNoRecords

'Define and execute the second insert operation.
Mn ("material_type") = "fuse"
CEN ("quantity") = "600"
CEN. Execute, adw.text + adExecuteNoRecords
.
.
.
%>
Important ADO parameters (such as ad1_text) are some simple variables, which means you need to define the parameter values before using ADO parameters in the Data Access Script. Since ADO uses a large number of parameters, it is easier to define parameters through the "component type library". The component type library contains all ADO parameters and constant definitions. For more information about implementing the ADO database, see using constants in variable and constant topics.

In the above example, you will notice that the script uses different values to repeatedly build and re-release the SQL query, but it does not redefine and resend the query to the database source. Using the "Command" Command to compile the query also has the following advantages: it can avoid problems when connecting strings and variables into SQL queries. Specifically, you can use the "Parameter" set of the "Command" object to avoid problems related to defining specific types of strings, date, and time variables. For example, an SQL query value containing single quotes (') may cause query failure:

StrSQL = "insert into MERs (FirstName, LastName) VALUES ('Robert ', 'O 'Hara ')"
Note that the surname "O 'Hara" contains a single quotation mark, which conflicts with the single quotation mark used to represent data in the SQL VALUES keyword. You can avoid this problem by limiting the query value to the "Command" object parameter.

Merge HTML forms and Database Access
Web pages that contain HTML forms allow users to remotely query databases and retrieve specified information. With ADO, you can create simple scripts for collecting user form information, create custom database queries, and return information to users. With the "Request" object of ASP, you can retrieve the information entered in the HTML form and embed the information into the SQL statement. For example, the following script block inserts the information provided by the HTML form into the table. The script uses the "Form" set of the "Request" object to collect user information.

<%
'Use the Connection object to open the Connection Command object
'Does not have an Open method for establishing a connection.
StrConnectionString = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = D: \ CompanyCatalog \ Seeds. mdb"
Set cnn = Server. CreateObject ("ADODB. Connection ")
Cnn. Open strConnectionString

'COMMAND object
'And append the object with the ActiveConnection property.
'Connect to the Command object.
Set CEN = Server. CreateObject ("ADODB. Command ")
Set Cen. ActiveConnection = cnn

'Define SQL queries.
CEN. CommandText = "insert into MySeedsTable (Type) VALUES (?) "

'Define query parameter configuration information.
CEN. Parameters. Append Cen. CreateParameter ("type", adVarChar, 255)

'Assign the input value and perform the update.
CEN ("type") = Request. Form ("SeedType ")
CEN. Execute, adw.text + adExecuteNoRecords
%>
For more information about forms and using ASP "Request" objects, see process user input.

Manage database connections
A major challenge for well-designed Web database applications (such as online ordering Input Applications for thousands of customers) is how to correctly manage database connections. Opening and maintaining the database connection, especially when there is no information transmission, will seriously consume the database server resources and lead to connectivity problems. Well-designed Web database applications can reuse database connections and compensate for latency caused by network traffic.

Timeout connection
The database server will generate a backlog when activities surge and greatly increase the time required to establish database connections. Therefore, excessive connection latency can reduce the performance of database applications.

The ConnectionTimeout of the Connection object can be used to limit the number of times the application waits before dropping the Connection attempt and publishing the error message. For example, the following script sets the ConnectionTimeout attribute to wait 20 seconds before canceling the connection attempt.

Set cnn = Server. CreateObject ("ADODB. Connection ")
Cnn. ConnectionTimeout = 20
Cnn. Open "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = C: \ Data \ Inventory. mdb"

The default ConnectionTimeout attribute is 30 seconds.

Note that before you merge the ConnectionTimeout attribute into a database application, make sure that your connection provider and data source support this attribute.

Shared connection
The connection pool allows your Web applications to use connections in the "pool". The "pool" refers to a number of free-connection containers that do not need to be re-established. After a connection is created and put into the connection pool, the application can reuse the connection without executing the connection process. This significantly improves performance, especially when applications are connected over the network or need to be reconnected and disconnected. In addition, shared connections can be reused by multiple applications.

Ole db session pool
Ole db has a shared feature called "session pool", which is used to improve the activity performance of large Web database applications. The session pool protects connection security and other attributes. A shared connection can be used only when the two ends of the connection make a matching request. By default, Microsoft SQL server and Oracle OLE DB providers support session pools. This means that you do not need to configure an application, server, or database to use the session pool. However, if the provider does not support session pools by default, you need to create registry settings to enable session pools. For more information about the session pool, see the ole db 2.0 Software Development Kit (SDK) documentation.

ODBC connection pool
If you want ODBC drivers to share the connection pool, you must configure a specific database driver and set the "CPTimeout" attribute of the driver in the Windows registry. The "CPTimeout" attribute determines the length of time the connection is retained in the connection pool. If the connection is retained in the pool for more than the duration set by CPTimeout, the connection is closed and deleted from the pool. The default value of "CPTimeout" is 60 seconds.

By creating a registry key with the following settings, You can selectively set the "CPTimeout" attribute to enable the connection pool of a specific ODBC database DRIVER:

\ HKEY_LOCAL_MACHINE \ SOFTWARE \ ODBC \ ODBCINST. INI \ driver-name \ CPTimeout = timeout count
(REG_SZ, in seconds)

For example, the following key value sets the connection pool timeout of the SQL Server Driver to 180 seconds (3 minutes ).

\ HKEY_LOCAL_MACHINE \ SOFTWARE \ ODBC \ ODBCINST. INI \ SQL Server \ CPTimeout = 180
Note that by default, the Web Server activates the SQL Server connection pool and sets "CPTimeout" to 60 seconds.

Use multi-page connections
Although connections can be stored in the "Application" object of ASP to reuse connections across multiple pages, this may lead to unnecessary open connections, and may damage the advantages of using the connection pool. If many users need to connect to the same ASP application, a better way is to repeat the database connection string across multiple Web pages, you only need to place the string in the "Application" object of ASP. For example, you can specify the connection string during the Application_OnStart event of the Global. asa file, as shown in the following script:

Application ("ConnectionString") = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = C: \ Data \ Inventory. mdb"

Then, in each ASP file accessing the database, write

<Object runat = server id = cnn PROGID = "ADODB. Connection"> </OBJECT>
To create a connection object instance for this page, and use the script

Cnn. Open Application ("ConnectionString ")
Open the connection. To close the connection, you can use

Cnn. Close
When individual users need to repeatedly connect to multiple Web pages, you will find that using the "Session" object to store the connection string is more advantageous than using the "Application" object.

Close connection
To make full use of the Connection Pool, close the database connection explicitly as much as possible. By default, the connection ends after the script is executed. However, by explicitly disabling connections that are no longer needed by the script, you can reduce the requirements on the database server and allow other users to use the connection.

You can use the "Close" method of the "Connection" object to explicitly terminate the Connection between the "Connection" object and the database. The following script opens and closes the connection:

<%
StrConnectionString = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = C: \ Data \ Inventory. mdb"
Set cnn = Server. CreateObject ("ADODB. Connection ")
Cnn. Open strConnectionString
Cnn. Close
%>