Ado|web in today's various Dynamic Web page (Dhtml) solutions have emphasized the connection with the database, in fact, web-linked background database is currently a popular application, E-commerce and other fields have a wide range of applications. Microsoft has introduced a new ADO (Active Data Object) model to accommodate its development, with an ODBC connection to provide a convenient, fast-clean connection to the current popular desktop database system. Here, I want to summarize the ADO objects and their respective properties and methods through the application of ADO in ASP technology.
One, ADO object model:
The following is a brief introduction to the object Model of ADO
ADO has three main objects, the Connection object, the Command object, and the Recordset object.
Connection (object)
─errors (set) ─error (object)
Command (object)
─parameters (set) ─parameter (object)
RecordSet (object)
─fields (set) ─field (object)
Where the Connection object, the Command object, The Recordset object and the Field object also produce the Properity object, each with a properities collection. We have a good understanding of these objects in ASP programming. The following are the relationships between the three main objects.
command.activeconnection-$#@62; Connection
recordset.activeconnection-$#@62; Connection
connection.execute-$#@62; RecordSet
command.execute-$#@62; RecordSet
recordset.source-$#@62; Command
Well, with the above relational table, you should be able to understand some of the equivalent forms of expression.
Form 1
Set rs = Server.CreateObject ("ADODB.") RecordSet ")
strconn = "Driver={sql server};server=srv;" & _
"Uid=sa;pwd=;d atabase=pubs"
strSQL = "SELECT * from Employee;"
Rs.Open Strsql,strconn,,, adCmdText
Form 2
Set conn = Server.CreateObject ("ADODB. Connection ")
strconn = "Driver={sql server};server=srv;" & _
"Uid=sa;pwd=;d atabase=pubs"
Conn.Open strconn
Set rs = Server.CreateObject ("ADODB.") RecordSet ")
Rs. Activeconnection=conn (note this sentence)
strSQL = "SELECT * from Employee;"
Rs.Open strSQL,,,, adCmdText
"The above sentence can also be written as Rs.Open strsql,conn,,, adCmdText
Form 3
Set conn = Server.crreateobject ("ADODB. Connection ")
Set rs = Server.CreateObject ("ADODB.") RecordSet ")
strconn = "Driver={sql server};server=srv;" & _
"Uid=sa;pwd=;d atabase=pubs"
Conn.Open strconn
strSQL = "SELECT * from Employee;"
Rs=conn. Execu strSQL (Note the second sentence)
Form 4
Set rs = Server.CreateObject ("ADODB.") RecordSet ")
strconn = "Driver={sql server};server=srv;" & _
"Uid=sa;pwd=;d atabase=pubs"
strSQL = "SELECT * from Employee;"
Rs.Open Strsql,strconn,,, adCmdText
Note: The example above assumes the name=srv of SQL Server, using SQL Server Authentication to use the default account SA, which does not have a password set.
Each of these forms can produce an instance RS of the same Recordset object, but the methods are different, and in the later discussion we will see their differing advantages.
Second, the Connection object:
In the ADO model, the connection object is the most basic object, and he mainly provides a connection to the database. The other two objects are connected to the database to complete the operation. Its properties, methods are shown below.
Main properties of the Connection object
1, CursorLocation, its value has two, one is adUseClient, one is adUseServer (default), from its meaning of English itself can be seen, the former is using the client's cursor, and the latter is the use of server-side cursors. The difference is that adUseClient cursors can provide additional attributes that are not provided by the vendor and therefore have greater flexibility. Note that both the connection object and the Recordset object have this property, and the Recordset object produced by the connection object automatically inherits this property. In addition, to make this property work with instances of connection and Recordset objects, you must define them before you open them.
Let's look at an example
Set conn=server.createobject ("ADODB. Connection ")
Conn. Cursorlocation=aduseclient
strconn = "Driver={sql server};server=srv;" & _
"Uid=sa;pwd=;d atabase=pubs"
Conn.Open strconn
Set rs= Server.CreateObject ("ADODB.") RecordSet ")
Rs.Open "Emloyee", Conn,,, adCmdTable
The Conn and RS cursors are aduseclient with the secondary method.
2, ConnectionString, set the database connection information before opening a connection instance. In the example above we used a statement Conn.Open strconn, where the strconn is connectionstring, so we can rewrite the above statement as follows:
Conn.connectionstring=strconn
Conn.Open
3, ConnectionTimeout, set the connection timeout.
4, CommandTimeout, set the command execution timeout.
The main methods of connection
1, open, opened a Connection object instance, commonly used as Conn.Open ConnectionString, if you have defined the ConnectionString attribute before opening, you can directly open.
2, Execute, produce a Recordset instance, commonly used to be written as
Rs=conn. Excute commandtext,recordsaffected,option
The CommandText can be in the following forms, mainly by the value of option to determine the 1 SQL statement, at this time the value of option is adCmdText, which means that a section of SQL statement will be executed. 2 A table name for the database, the value of option is adcmdtable, which means that the table will be manipulated.
3, a StoredProcedure name, at which point the option value is adCmdStoredProc, which means that a stored procedure defined on the SQL will be executed. This is a very flexible and powerful method, it can hide the specific information of the database, but only the user to provide the appropriate parameters, but also to return the required parameter values. The command object is described later in detail. Notably, it is sometimes not necessary to return a Recordset object after Excute, such as deleting records in a table. Look at the following example:
Set conn= Server.CreateObject ("ADODB.") Connection ")
Conn. connectionstring= "Driver={sql server};server=srv;" & _
"Uid=sa;pwd=;d atabase=pubs"
Conn.Open
Conn. Execute "Delect from Employee Where job_id=1;", adCmdText
This example deletes the record job_id 1 in the employee table and does not need to return an instance of the recordset, but if the last sentence is changed to rs= Conn. Execute "Delect from Employee Where job_id=1;", adCmdText then we can use this RS to point to the record bar in the table. The RS returned with the Source property in the back Recordset object are the same.
Collection of Connection objects
1, Errors collection, corresponding to the production of Error objects. We'll do a separate discussion later.
2, properties set, corresponding to the production Property object, the following gives a piece of code, which contains the main methods and attributes of the properties object.
$#@60;%
"This program are testing the ADO" s Property object
Const adCmdTable = &h0002
Set Conn=server.createobject ("ADODB. Connection ")
Set Rs=server.createobject ("ADODB. RecordSet ")
