ADO operation and its parameters
1. ASP and Access database connection: <% @ language = VBScript%> <% DIM conn, mdbfilemdbfile = server.mappath ("Database Name. MDB") set conn = server.createObject ("AdoDb.Connection" Conn.open "Driver = {Microsoft Access Driver (* .mdb)}; uid = admin; pwd = database password; DBQ =" & mdbfile%> 2. ASP and SQL database connection: <% @ language = vbscript%> <% DIM Conn Set CONN = Server.createObject ("AdoDb.Connection") Con -.open "provider = SQLOLEDB; DATA SOURCE = SQL server name or IP address; UID = sa; PWD = database password; Database = Database Name%> Establish record set object: SET RS = Server.createObject ("AdoDb.Recordset") RS.OPEN SQL statement, conn, 3, 23. SQL common command usage: (1) Data Record Filter: SQL = "SELECT * FROM Datasheet WHERE Field Name = Field Value ORDER BY Field Name [DESC] "SQL =" SELECT * FROM Datasheet WHERE Field Name Like '% Field Value%' Order By Field Name [DESC] "SQL =" SELECT TOP 10 * From Datasheet WHERE Field name Order by Field Name [DESC] "SQL =" SELECT * FROM Datasheet WHERE Field Name in ('value 1', 'value 2', 'value 3') "SQL =" SELECT * FROM DATA WHERE Field Name BetWeen Value 1 and Value 2 "(2) Update Data Record: SQL =" Update Datasheet Set Field Name = Field Value WHERE Condition Expression "SQL =" Update Data Table Set Field 1 = Value 1, Field 2 = Value 2 ... Field n = Value N where condition expression "(3) Delete data record: SQL =" Delete from Data Table Where Condition Expression "SQL =" Delete from Data Sheet "(4) Add Data Record: s QL = "INSERT INTO Data Sheet (Field 1, Field 2, Field 3 ...) VALUES (Value 1, Value 2, Value 3 ...)" SQL = "Insert INTO Target Data Table Select * FROM Source Data Sheet" (Source Data) Table Record Add to Target Data Sheet) (5) Data Record Statistics: AVG (Field Name) Since the table bar average value count (* | field name) statistics on the number of data lines or values for a column Data Row Statistics Max (Field Name) gets the maximum value of a table bar min MIN (field name) gets the smallest value SUM (field name) of a table bar, add the value of the value of the data bar to the above function: SQL = "SELECT SUM (field name) AS alias from Datasheet Where conditions express "SET RS = conn.excute (SQL) RS (" alias "
) The value of the obtained, and other functions are used. (5) Establishment and deletion of data sheet: CREATE TABLE DATA Name (Field 1 Type 1 (Length), Field 2 Type 2 (Length) ...) Example: Create Table Tab01 (Name Varchar (50), DateTime Default NOW )) Drop Table Data Table Name (Permanent Delete A Data Table) 4. Method for Record Set Objects: rs.Movenext Moves the Record Pointer from the current location RS.MovePrevious to move the record pointer from the current location Rs.Movefirst Move the record pointer to the data table RE. RS.MOVELAST Move the record pointer to the last line RS.AbsolutePosition = N Move the record pointer to the data table Nth line rs.absolutepage = N Move the record pointer to move the record pointer The first line of the nth page RS.PageSize = N Sets each page for n-record RS.PageCount Returns the total number of pages based on PageSize Rs.Recordcount Returns the total number RS.BOF Returns the record pointer over the data sheet header, TRUE Indicates whether false is rs.eo returns the record pointer over the end of the data table, the true representation is whether false deletes the current record, but the record pointer does not move the RS.AddNew to the data table end RS .UPDATE Update Data Table Record ------------------------------------- RECORDSET Object Open Method Recordset.open Source, ActiveConnection, Cursortype, LockType, OptionsSourceRecordset object can connect the Command object via the Source property. The Source parameter can be a COMMAND object name, a SQL command, a specified data table name or a Stored ProCedure. If this parameter is omitted, the system uses the Source property of the Recordset object. ActiveConnectionRecordset objects can connect the Connection object through the ActiveConnection property. The ActiveConnection here can be a connection object or a string parameter containing database configuration (Connectionstring).
CURSORTYPERECORDSET Object Open method The CURSORTYPE parameter represents what kind of cursor type starts data, including AdopenForwardOnly, AdoPENDYNAMIC, and AdoPenStatic, is described below: ------------------- ----------------------------------------- constant constant value description --- -------------------------------------------------- -------- AdopenForwardonly 0 default, start a cacope that can only move forward. AdopenKeyset 1 Start a KEYSET type cursor. AdoPendynamic 2 launches a dynamic type cursor. AdoPenStatic 3 launches a cursor for a Static type. -------------------------------------------------- ----------- The above cursor type will directly affect all the properties and methods all of the Recordset object, the following list describes the differences between them.
-------------------------------------------------- ----------- RECORDSET Property AdopenForwardonly AdopenKeyset AdoPENDYNAMIC ADOPENSTATIC --------------------------------- ---------------------------- AbsolutePage does not support you do not support readable write readable absolutePosition does not support not support readable write readable ActiveConnection readable to read-readable write read-read read-only BOF read-only read-only read-only BOOKMARK does not support readable write readable Cachesize readable WrTable Write-readable Writing CACHESIZE CURSORLOCATION can be read from or write read read write CursorType readable, writable read-only and writable read-only read-only and writable read only EditMode EOF read-only read-only and writable read-only and writable read-only Filter writable Readable to write LockType readable write readable Readable-readable Writable Write-readable Writing Writing Write-readable Writing Writing Write-readable Writing Write-readable Writing Writing Writing Writing Writing Write-readable Writing Read Read-only PageSize Write-readable recordcount does not support read-only Source read-read read-read read-read read-only STATUS read-only read-only read-only STATUS read-only read-only read-only ADDNEW support support support support Support support support support CanceluPdate support
Support Support CloSe support support support support support support Delete support support support support GETROWS support support support support MOVE support support support support support support support support support support MOVELAST does not support support support MOVELEXT support support support MOVEPREVIOS does not support support support Support NextRecordset support support support Open Support Support Support Requuery Support Support Support RESYNC does not support support support Supports support support support support Update Support support support support UPDATEBATCH support support support support ----------------------------------------- --------------------- where the nextRecordset method does not apply to the Microsoft Access database. LockTypeRecordset Object Open method The LockType parameter represents the Lock type to be used, if this parameter is ignored, then the system is preset with the LockType property of the Recordset object. The LockType parameter contains AdlockReadonly, AdlockPrsSIMistic, AdlockOptimistic, and AdlockBatchOptimistic, etc., is described below: ------------------------------------------------------------------------------------------------------------------------------------ -------------------------- constant constant value description ------------------- ------------------------------------------ AdlockReadOnly 1 default value, Recordset Objects are launched in read-only mode, unable to run AddNew, Update and Delete, etc. AdlockPrsSIMistic 2 When the data source is being updated
