Program Database Class 1. ASP and Access database connection: % @ languageVBscript % dimconn, mdbfilemdbfileserver. mappath (Database Name. mdb) setconnserver. createobject (adodb. connection) conn. opendriver {microsoftaccessdriver (*. mdb)}; uidadmin; pwd
Program Database Class 1. ASP and Access database connection: % @ language = VBscript %> % dim conn, mdbfile = server. mappath ("database name. mdb ") set conn = server. createobject ("adodb. connection ") conn. open "driver = {microsoft access driver (*. mdb)}; uid = admin; pwd =
Program database
1. ASP and Access database connection:
<% @ Language = VBscript %>
<%
Dim conn, mdbfile
Mdbfile = 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
%>
Create a record set object:
Set rs = server. createobject ("adodb. recordset ")
Rs. open SQL statement, conn, 3, 2
3. Common SQL commands:
(1) Data Record Filtering:
SQL = "select * from data table where field name = Field Value order by field name [desc]"
SQL = "select * from data table where field name like '% Field Value %' 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 value 2"
(2) update data records:
SQL = "update data table 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 records:
SQL = "delete from data table where condition expression"
SQL = "delete from data table" (delete all data table Records)
(4) add 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 records of source data table to target data table)
(5) statistical functions of data records:
AVG (field name) returns the average value of a table column
COUNT (* | field name) statistics on the number of data rows or the number of data rows with values in a column
MAX (field name) obtains the maximum value of a table column.
MIN (field name) obtains the minimum value of a table column.
SUM (field name) adds the values in the data column
The method for referencing the above functions:
SQL = "select sum (field name) as Alias from data table where condition expression"
Set rs = conn. excute (SQL)
Use rs ("alias") to obtain the calculation value. Use the same method for other functions.
(5) Create and delete data tables:
Create table data TABLE name (Field 1
Type1 (length), Field 2
Type2 (length )...... )
Example: create table tab01 (name varchar (50), datetime default now ())
Drop table data TABLE name (permanently delete a data TABLE)
4. Method of record set object:
Rs. movenext moves the record pointer down a row from the current position
Rs. moveprevious transfers the record pointer from the current position to a row up
Rs. movefirst move the record pointer to the first row of the data table
Rs. movelast moves the record pointer to the last row of the data table
Rs. absoluteposition = N move the record pointer to the nth row of the data table
Rs. absolutepage = N move the record pointer to the first row of page N
Rs. pagesize = N set N records per page
Rs. pagecount the total number of pages returned Based on pagesize settings
Rs. recordcount total number of returned records
Rs. bof indicates whether the record pointer exceeds the first end of the data table. true indicates yes, and false indicates no.
Rs. eof indicates whether the returned record pointer exceeds the end Of the data table. true indicates yes, and false indicates no.
Rs. delete deletes the current record, but the record pointer does not move down
Rs. addnew add record to end of data table
Rs. update data table records
--------------------------------------------------------------
Recordset object Method
Open Method
Recordset. Open Source, ActiveConnection, CursorType, LockType, Options
Source
The Recordset object can be connected to the Command object through the Source attribute. 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 be connected to the Connection object through the ActiveConnection attribute. ActiveConnection can be a Connection object or a string containing the database Connection information (ConnectionString.
CursorType
The CursorType parameter of the Open method of the Recordset object indicates what kind
Cursor
TypeStartup data, including adOpenForwardOnly, adOpenKeyset, adOpenDynamic, and adOpenStatic, are described as follows:
--------------------------------------------------------------
Constant Value description
--------------------------------------------------------------
AdOpenForwardOnly 0 is the default value. Start one that can only move forward.
Cursor(Forward Only ).
AdOpenKeyset 1 starts a Keyset
TypeOf
Cursor.
AdOpenDynamic 2 starts a Dynamic
TypeOf
Cursor.
AdOpenStatic 3 starts a Static
TypeOf
Cursor.
--------------------------------------------------------------
The preceding
Cursor
TypeDirect
ImpactTo all the attributes and methods of the Recordset object, the following lists the differences between them.
--------------------------------------------------------------
Recordset attribute adOpenForwardOnly adOpenKeyset adOpenDynamic adOpenStatic
--------------------------------------------------------------
AbsolutePage does not support read/write.
AbsolutePosition does not support read/write.
ActiveConnection can be read/written or read/write
BOF read-only
Bookmark does not support read/write.
CacheSize: readable/writable
CursorLocation: readable/writable
CursorType can be read/written or read/write
EditMode read-only
EOF read-only
The Filter can read and write, and can read and write.
LockType: readable/writable
MarshalOptions: readable/writable
MaxRecords
PageCount does not support read-only and read-only
PageSize: readable/writable
RecordCount does not support read-only and read-only
Source: readable/writable
State read-only
Status read-only
AddNew support
Support for CancelBatch
Supported CancelUpdate
Clone is not supported.
Support for Close
Support for Delete
GetRows support
Move is not supported.
Supported by MoveFirst
Supported by MoveLast
Supported by MoveNext
Supported by MovePrevious
Supported by NextRecordset
Open Support
Requery support
Not supported by Resync
Supports support
Update support
Supported by UpdateBatch
--------------------------------------------------------------
The NextRecordset method is not applicable to Microsoft Access databases.
LockType
The LockType parameter of the Open method of the Recordset object indicates the Lock to be used.
TypeIf this parameter is ignored, the system uses the LockType attribute of the Recordset object as the default value. The LockType parameters include adLockReadOnly, adLockPrssimistic, adLockOptimistic, and adLockBatchOptimistic, which are described as follows:
-------------------------------------------------------------
Constant Value description
--------------------------------------------------------------
AdLockReadOnly 1 is the default value. The Recordset object is started in read-only mode and cannot run AddNew, Update, Delete, and other methods.
AdLockPrssimistic 2 when the data source is being updated, the system will temporarily lock the actions of other users to maintain data consistency.
AdLockOptimistic 3 when the data source is being updated, the system does not lock the actions of other users. Other users can add, delete, and modify data.
AdLockBatchOptimistic 4 when the data source is being updated, other users must change the CursorLocation attribute to adUdeClientBatch to add, delete, and modify the data.
