A common SQL statement is used to record a set object and other information

A common SQL statement is used to record a set object and other information A common SQL statement is used to record a set object and other information ASP and Access database connection: ASP and SQL database connection: Establish a record set object: Set rs = server.createobject (" adodb.recordset ") Open SQL statement, conn, 3, 2 SQL common command usage: (1) data recording screening: SQL = "select * from data table where field name = field value order by field name [desc]" SQL = "select * from data table where field name like % order by field name [desc]" SQL = "select top 10 * from data table where field name order by field name [desc]" SQL = "select * from data table where field name in (value 1, value 2, value 3)" SQL = "select * from data table where field name between value 1 and 2" (2) updating data records: SQL = "update data table set field name = field value where conditional expression" SQL = "update data table set field 1 = value 1, field 2 = value 2... field n = value n where condition expression" (3) delete data records: SQL = "delete from data table where condition expression" SQL = "delete from data table" (delete any records from the data table) (4) adding data records: 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 the record of the source data table to the target table) (5) data recording statistics function: The AVG (field name) yields a table bar average COUNT (* | field name) statistics on the number of data rows or data rows with values in a column MAX (field name) takes the maximum value of a table bar MIN (field name) takes the minimum value of a table column SUM (the field name) adds the value of the data column How to refer to the above functions: SQL = "select sum (field name) as alias from the data table where condition expression" Set the rs = conn. Excute (SQL) Use the rs (" alias ") to obtain the system's value, and the other functions apply the same. (5) the establishment and deletion of data tables: The 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 data TABLE name (permanently delete a data TABLE) The method of recording a set object: RRS movenext will record the pointer from the current location to the lower row RRS. Moveprevious moves the record pointer from the current position to the next line Rr.movefirst moves the record pointer to the first row of the data table RRS movelast moves the record pointer to the last row of the data table R.absoluteposition = N moves the record pointer to the data table, line N R.absolutepage = N moves the record pointer to the first line on page N The rs pagesize = N is configured with N records per page Rs pagecount returns the total number of pages based on the pagesize configuration RRS. Recordcount returns the total number of records The rr.bof returns the record pointer over the data table, which is true for false The rr.eof returns the record pointer over the end of the data table, and true indicates false to no Rs. Delete deletes the current record, but the record pointer does not move down The RRS. Addnew adds the record to the end of the data table Rr.update updates the data table record -- -- -- -- -- -- -- -- You object method The Open method Recordset. Open Source, ActiveConnection, CursorType, LockType, Options The Source The Recordset object can connect the Command object with the Source property. The Source parameter can be a Command object name, an SQL Command, a specified data table name, or a Stored Procedure. If this parameter is omitted, the system USES the Source attribute of the Recordset object. ActiveConnection The Recordset object can connect the Connection object with the ActiveConnection property. The ActiveConnection here can be a Connection object or a string of string parameters that contain the database Connection information (ConnectionString). CursorType You Open the object of the CursorType parameter will begin with what kind of cursor type data, including adOpenForwardOnly, adOpenKeyset, adOpenDynamic and adOpenStatic, points mentioned below: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Constant numerical description -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - AdOpenForwardOnly 0, the default, start a cursor can Only move Forward, Forward Only). AdOpenKeyset 1 initiates a Keyset type cursor. AdOpenDynamic 2 starts a cursor of Dynamic type. AdOpenStatic 3 starts a cursor of Static type. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - These cursor types will directly affect any properties and methods of the Recordset object, and the following list illustrates the difference between them. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - You attribute adOpenForwardOnly adOpenKeyset adOpenDynamic adOpenStatic -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - AbsolutePage does not support unsupported reads and writes AbsolutePosition does not support the lack of read-write and read-write ActiveConnection can read and write read and write BOF read-only read only Bookmark does not support non-read and write read-write CacheSize can read and write read and write CursorLocation can read and write read and write CursorType can read and write and read and write EditMode read-only read-only EOF read-only read-only The Filter can read and write to read and write LockType can read and write read and write MarshalOptions can read and write read and write The MaxRecords can read and write read and write PageCount does not support not supporting read-only read-only PageSize can read and write read and write RecordCount does not support not supporting read-only read-only The Source can read and write and read and write State reads only read-only Status reads only read-only AddNew supports support support The CancelBatch support supports support CancelUpdate supports support support Clone does not support it Close support supports support The Delete support supports support GetRows support supports support Move does not support support support MoveFirst supports support support MoveLast does not support support support MoveNext supports support support MovePrevious does not support support support NextRecordset supports support support Open support supports support Requery supports support support Resync does not support unsupported support Supports support supported by Supports support Update support supports support UpdateBatch support supports support -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- The 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, and if this parameter is ignored, the system will default as the LockType attribute of the Recordset object. The LockType parameter contains adLockReadOnly, adlockprssion, adLockOptimistic, and adLockBatchOptimistic. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - Constant numerical description -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- The default value of adLockReadOnly 1, the Recordset object is started in read-only mode, unable to run AddNew, Update, and Delete methods When the data source is being updated, the system temporarily locks the actions of other users to keep the data consistent. AdLockOptimistic 3, when the data source is being updated, the system will not lock the actions of other users, and other users will be able to add, delete, and modify the data. AdLockBatchOptimistic 4 when the data source is being updated, other users must change the CursorLocation attribute to adUdeClientBatch to increase the data, Delete and modify.