1. ASP and Access database connection:
2. ASP and SQL database connection:
Establish a recordset: SET RS = Server.createObject ("AdoDb.Recordset")
RS.Open SQL statement, conn, 3, 2
3. SQL common command usage method:
(1) Data Record Filter: SQL = "SELECT * FROM Datasheet WHERE Field Name = Field Value ORDER BY Field Name" SQL = "SELECT * FROM DATA WHERE Field Name Like '% field value%' Order BY field Name [DESC] "SQL =" SELECT TOP 10 * FROM DATA 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 DATA WHERE Field Name BetWeen Value 1 And Value 2 "
(2) Update Data Record: SQL = "Update Data Table SET Field Name = Field Value WHERE Condition Expression" SQL = "Update Datasheet Set Field 1 = Value 1, Field 2 = Value 2 ... Field N = Value N Where Conditional expression
(3) Delete data record: SQL = "delete from data table where condition express" SQL = "delete from data table" (delete all records of data tables)
(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 * from source Data table "(add a record of the source data table to the target data sheet)
(5) Data Record Statistics: AVG (Field Name) gives a mean of table column
Count (* | field name) Statistics on the number of data lines or the number of data line counts on a certain column
Max (field name) get the largest value of a table bar
MIN (field name) gets the smallest value of a table column
SUM (field name) adds the value of the value of the data bar to the above function:
SQL = "SELECT SUM (Field Name) AS alias from Datasheet Where Condition Expression
SET RS = conn.excute (SQL) uses RS ("alias" to get the value, 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 Dataset Name (Permanent Delete A Data Table) 4. Method for Record Set Objects: rs.Movenext Moves the Record Pointer from the current location
Rs.MovePrevious moves the record pointer from the current location
Rs.MoveFirst moves the record pointer to the first line of the data table
Rs.Movelast moves the record pointer to the last line of the data table
Rs.absolutePosition = n Moves the record pointer to the data table N
Rs.absolutePage = N Move the record pointer to the first line of the nth page
rs.pagesize = n Sets each page as N records
Rs.PageCount Returns the number of pages based on PageSize
Rs.RecordCount Returns the total number of records RS.BOF Returns whether the pointer exceeds the data sheet header, True is indicated by the FALSE
rs.eof Returns whether the pointer exceeds the end of the data table, True is indicated by the FALSE
Rs.delete deletes the current record, but the record pointer will not move downward
Rs.addnew Add record to the end of the data
Rs.Update Update Data Table Record ---------------------------------------
Recordset Object Method Open Method Recordset.open Source, ActiveConnection, Cursortype, LockType, OptionsSource
The Recordset 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. ActiveConnection
The Recordset object 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). Cursortype
The CURSORTYPE parameter of the RECORDSET object Open method represents what kind of cursor type starts data, including AdopenForward, AdoPenkey, AdoPENDYNAMIC, and AdoPenStatic, and is described below:
-------------------------------------------------- ----------------
Constant constant value description
-------------------------------------------------- -----------
AdopenForwardonly 0 defaults to start a prominend 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 number of cursor types will directly affect all attributes and methods of the Recordset object, and the following list describes the differences between them. -------------------------------------------------- -----------
Recordset property adopenforwardonly adopenkeyset adoPENDYNAMIC AdoPenStatic
-------------------------------------------------- -----------
AbsolutePage does not support not support readable write readable
AbsolutePosition does not support not support readable write readable
ActiveConnection readable to read and read-readable write-readable, readable, read-only read only read only read only
Bookmark does not support not support readable write readable
Cachesize readable write readable write readable write readable
CursorLocation Readable Writables Readable Writing Writing Writing
CURSORTYPE readable write readable write readable write readable
EditMode reads read only read only read only
EOF read-only Read read only read only
Filter readable write readable write readable write readable
LockType readable to read-readable write-readable
Marshaloptions readable and writable readable write readable
MaxRecords readable write readable write readable write readable
PageCount does not support only read-only read only
PageSize readable write readable write readable write readable
Recordcount does not support not support read only read only
Source Readable Writable Writable Writable Writing
State Read Read Read Read Read Read
Status is read-only Read read only read only
AddNew support support support support
Cancelbatch support support support support
CancelupDate support support support support
Clone does not support
Close Support Support Support
Delete support support support support
GETROWS support support support support
MOVE does not support support support support
MoveFirst support support support support
MovelaST does not support support support support
MoveNext support support support support
MovePrevious does not support support support support
NextRecordset support support support support
Open support support support support
Requory support support support support
RESYNC does not support support support support
Supports support support support support
UPDATE support support support support
UpdateBatch support support support support
-------------------------------------------------- ----------------
Where the next NEXTRECORDSET method does not apply to the Microsoft Access database. LockType
The LockType parameter of the RECORDSET object Open method indicates the Lock type to be used. If this parameter is ignored, the system will be 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, Recordset objects are started in read-only mode, unable to run 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.
AdlockBatchOptimistic 4 When the data source is being updated, other users must change the CURSORLOCATION property to ADUDECLIENTBATCH to increase, delete, and change the data.
