Database class 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 Sett CONN = Server.createObject ("AdoDb.Connection") Con -.open "provider = SQLOLOLEDB; 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, 2
3. SQL common command usage: (1) Data Record Filter: SQL = "SELECT * FROM DATA WHERE Field Name = Field Value ORDER BY Field Name" SQL = "SELECT * FROM DATA WHERE Field Name LIKE" % Field Value% "Order By" SQL = "SELECT TOP 10 * FROM Datasheet WHERE Field Name Order By Field Name [DESC]" SQL = "SELECT * FROM DATA WHERE Field Name in (" value 1 "" Value 2 "," Value 3 ")" SQL = "SELECT * FROM Datasheet 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 Sheet WHERE Conditional expression "SQL =" Delete from Datasheet "(4) Add Data Record: SQL =" Insert INTO Data Table (Field 1, Field 2, Field 3 ...) VALUES (Value 1, Value 2, value 3 ... "SQL =" INSERT INTO Target Data Table Select * "Add Data Table to Target Data Table) (5) Data Recording Statistics: AVG (Field Name) Out of a table column average count (* | field name) Statistics on the number of data lines or the number of data line counts for a value of a column gets the maximum value min (field name) for a table bar. The smallest value of the table column SUM (field name) adds the value of the value of the data column to the above function: SQL = "SELECT SUM (field name) AS alias from Datasheet WHERE Condition Expression" SET RS = Conn.Excute (SQL ) Use RS ("alias" to get the value, and other functions are used. (5) Establishment and deletion of data sheet: (only SQL database) Create Table Data table name (field 1 type 1 (length), field 2 type 2 (length) ...) Example: Create Table Tab01 (Name VARCHAR (50 ), DATETIME DEFAULT now ()) DROP TABLE Dataset Name (permanently delete a data sheet)
4. Method of recording a set object: rs.movenext Moves the record pointer down the current location RS.MOVEPREVIOS to move the logger from the current location to the first line RS.MoveFirst to move the record pointer to the first line RS of the data table. MoveLast moves 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 the first line of the first line of the nth page RS.PageSize = n Set per page Returning the Number of the PageSize RS.PageCount Return to the Number RT Returns Returns Returns Returns Returns Returns Returns RT Returns Returns Returns Returns Return Recording Pointer Over the Data Table Premiere, whether false is rs.eof Returns the record pointer. The end of the table, True, is whether false deletes the current record, but the record pointer does not move the RS.AddNew to add the RS.AddNew to the data table Rs.Update Update Data Table Record --------- ----------------------------- Recordset Object Open Method RecordSet.Open Source, ActiveConnection, Cursortype, LockType, OptionsSourceRecordset object Source properties to connect the Command object. 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 write absolutePosition does not support readable write ActiveConnection readable and writable readable write readable write-readable and write-only bookmark does not support you read / write Cachesize readable and write Cachesize Ready-write read-read-read-read-read-read-read-read-read-read-read-read-only read-read-read-read-read-read-read-read-read-read-read-only-read-read-only-read-read-read-like read-read-only-read-read-only-read-read-only-read-read-only-read-read-only-read-read-only-minded write LockType can read and write can read and write can read and write can read and write can read and write MarshalOptions can read and write can read and write can read and write can read and write MaxRecords can read and write can read and write can read and write can read and write PageCount only not supported not supported read-only read PageSize can read and write can read and write can read and write can read and write RecordCount not supported not supported read only read-only Source can read and write can read and write can read and write can read and write State read-only read-only read-only read-only read-only read-only Status Read-only AddNew support support support support CancelBatch support support support CancelupDate support support support CLONE does not support unsupported support support support support support support support support support support GETROWS support support support MOVE does not support support support MOVEFIRST support support support MoveLast does not support support support Movenext support support support MOVEPREVIOUS does not support support support NEXTRECORDSET support support support OPEN support support support support support support support support Support RESYNC does not support support support Supports support support Support support Update support support support support UPDATEBATCH support support support support ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ 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 way, and how to operate AddNew, Update, and Delete. AdlockPrsSIMistic 2 when the data source is being updated, the system is temporarily locked to maintain data consistency. AdlockOptimistic 3 When the data source is being updated, the system does not lock the action of other users, and other users can increase, delete, and change the data.
