Little knowledge about SQL and RECORDSET
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 objects:
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 Datasheet WHERE Field Name = Field Value ORDER BY Field Name" SQL = "SELECT * FROM Datasheet 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
(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 Recording Statistics: AVG (Field Name) draws a table column average count (* | field name) Statistics on the number of data lines or the number of data line counts on a certain column (field name) Get the maximum value of a table bar min (field name) get the smallest value SUM (field name) with 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 data Table 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: 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 Sheet Name (Permanent Delete A Data Table) 4. Method for Recording Set Objects: Rs.MoveNext Moves the Record Pointer from the current location RS.MovePrevious to move the record pointer from the current position up row RS. Movefirst Move the record pointer to the first line of the first line RS.MovelAst Move the record pointer to the deadline RS.AbsolutePosition = n Move the record pointer to the data table Nth line rs.absolutepage = N Move the record pointer to Nth The first line of the page RS.PageSize = N Sets the N-note per page to return rs.pageCount Returns the total number of the total number RS.Recordcount Return to the total number of rs.trib returns the total number of data table heads, TRUE is said to be , False is not RS.EOF to return to the record pointer over the end of the data table, True Representation is whether false deletes the current record, but the record pointer does not move the RS.AddNew to add a record to the data table end Rs.Update Update Data Sheet Record ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------- Recordset Objects Open Method Recordset.Open Source, ActiveConnection, Cursortype, LockType, OptionsSourceRecordset object can be connected to 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 containing data.
String parameters of the library connection information (Connectionstring).
The CURSORTYPERECORDSET Object Open method The CURSORTYPE parameter represents what kind of cursor type starts data, including AdopenForwardonly, AdoPenKeyset, AdoPENDYNAMIC, and AD
OpenSTATIC, is described below: -------------------------------------------- ----------------- constant constant value description -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------- AdopenForwardonly 0 default, start a cursor that can move forward forward ( Forward ONLY). 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 of the Recordset object, the following list describes the differences between them. -------------------------------------------------- ------------ Recordset Property AdopenForwardonly AdopenKeyset AdoPENDYNAMIC ADOPENSTATIC ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Read and write ActiveConnection readable and write-readable write readable write-readable and write BOF read-only Read read-only bookmark does not support you can read and write Cachesize readable and write-readable and write-to-write CacssorLocation can be read write write write CursorType be readable, writable and readable, writable and read write read only read only read only read only EditMode EOF read only read only read-only and writable read only readable, writable Filter Read and write to read and write lockType readable and write readable write readable write readable and write MarshalOptions readable write readable write readable write readable write maxRecords readable and write readable write readable write readable and write pageCount does not support only Read only PageSize readable and write-readable and write readable write-readable recording, read-only Source read-read-only Source read-read-only STATUS Read-only Read-only AddNew support support support can 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 REQUERY Support Support Support RESYNC does not support support support Supports support Support support UPDATE support support support support UPDATEBATCH support support support support ------------------------------------- ------------------------ in which 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. L
