/ * Give teachers to the teacher to write tutorials, now put individual chapters * / 5.5 ADO foundation ADO (ActiveX Data Objects) is a data access technology introduced by Microsoft. Using ADO, you can write compact and concise scripts to connect to OLE DB compatible data sources, such as databases, spreadsheets, sequential data files, or email directories. It can be combined with the ASP to establish web content providing database information, query, insert, update, delete, etc.
5.5.1 Connection Objects The Connection object is used to establish and manage connections between applications and data sources, or use it to perform a command. The properties and methods of the Connection object can be used to open and close the connection of the database, in the ADO, there can be multiple Connection objects, pointing to different data sources, respectively.
5.5.2 Use of Connection objects Before use, you must create this object, its format is: <% set cn = server.createObject ("AdoDb.connection"%> string "adodb.connection" is used to create a Connection object PROGID. Alternatively, you can also create
(1) Use the driver <% cn.open "DRIVER = {SQL Server}; database = guest; server = (local); uid = sa; pwd ="%> where driver specifies the name of the data source driver, SQL Server uses {SQL Server}. Database specifies the requested database. Server specifies the data source server name, when set to LOCAL, indicating a local copy of SQL Server.
<% CN.Open "DBQ =" & Server.mAppath ("Guests.mdb") & "; driver = {Microsoft Access Driver (*. MDB)"}%> The above is the way to connect the Microsoft Access database file, where DBQ is Point the address of the Access database. (2) Using OLE DB's Provider <% CN.Open "provider = SQLOLEDB.1; user ID = sa; password =; initial catalog = guest; data source = mls"%> where the Provider specifies the data provider. Initial Catalog points to the database to be accessed on SQL Server. Data Source Specifies the computer name or IP address of SQL Server.
<% cn.open "provider = microsoft.jet.Oledb.4.0; data source =" & server.mappath ("guests.mdb")%> The above is the use of the Office of the Microsoft Access database file (3) Use ODBC's DSN We take SQL Server as an example, first selected "ODBC data source" in the Control Panel, appears as shown in Figure 5.10. Select the System DSN tab. Figure 5.10 ODBC Data Source Figure 5.11 "Creating Data Source" dialog box, select SQL Server Figure 5.12 Enter the name of the DSN and specify SQL Server Server diagram 5.13 Select SQL Server Verification Login ID Method Figure 5.14 Select Database File Figure 5.15 Other Settings Once you have created a DSN connection, you can create a Connection object and use the OPEN method to create a data source. You can set the connectionString property first, then call the OPEN method. For example: <% set cn = server.createObject ("adodb.connection") cn.connectionstring = "DSN = Guests; UID = sa; pwd =" cn.open%> or directly use the connection string as parameters: <% Set cn = server.createObject ("adodb.connection") cn.open = "DSN = Guests; UID = SA; PWD ="%> Example 5-6 SQL Database All records in the table guests, table guests definition as tables 5.16 shown. 1 Insert a "Name:" Li Si ", Password:" Li "" record.
The procedure is as follows: <% @ language = VBScript%> <% set cn = server.createObject ("adoDb.connection") cn.open "provider = SQLOLDB.1; user ID = sa; password =; initial catalog = guest; data Source = mls "cn.execute" Insert INTO GUESTS (Name.Password) Value ('Lee 4', 'Li') "Insert a record with INSERT method CN.Close 'Close Database Connection%> 2 Query all of the guests table Record <% set cn = server.createObject ("adodb.connection") cn.open "provider = SQLOLEDB.1; user ID = sa; password =; initial catalog = guest; data source = mls" set RST = cn.execute ("Select * from guests") 'Generate a recordset, and assign the value do while NOT RST.EOF' When the record set is not processed, response.write "Name:" & RST ("name") & "Response.write" Password: "& RST (" Password ") &"
"Rst.Movenext 'pointer points to the next record loop' loop Continued Rst.Closecn.close%> 3 Delete" Obtain "record in the USER table <% SET CN = Server .CreateObject ("adodb.connection") cn.open "provider = SQLOLEDB.1; user ID = sa; password =; initial catalog = guest; data source = mls" cn.execute ("delete * from guests where name = '明明 '")' Execute SQL Delete Statement Cn.close% with Execute Method
Example 5-7 Reads all records of the table guest in the SQL database with a recordset. The procedure is as follows: <% @ language = vbscript%> "The file used to include the call you want, if the directory is different, use the Virtual parameter <% set cn = server .CreateObject ("adoDb.connection") cn.open "provider = SQLOLEDB.1; user ID = sa; password =; initial catalog = guest; data source = mls" set RST = Server.createObject ("AdoDb.Recordset") 'Creating a record set Rst.Open "Select * from guests", CN, 1, 3' assignment to record set, 1, 3 is defined lock type and cursor type do while not rst.eof 'When the record set is not processed Response.write "name:" & rst.fields ("name") & "" response.write "password:" & RST.Fields ("password") & "
" Rst.Movenext 'pointer points to the next record Loop RST .close 'close method Close this recordset and releases all the full data cn.close%> Example 5-8 using record sets and tables to add records with record sets and tables. Step 1, establish two text input boxes and a submission button in the form.
