Asp connection access, SQL database code and database operation code

1. ASP and Access database connection: Copy codeThe Code is as follows: dim strConn
Dim conn
StrConn = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" + Server. mappath ("data/isp. mdb") + "; Persist Security Info = False"
Set conn = Server. CreateObject ("ADODB. Connection ")

2. ASP and SQL database connection:
Copy codeThe Code is as follows: 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

Code for connecting asp to SQL:Copy codeThe Code is as follows: DataServer = "jb51" 'database Server IP Address
DataUser = "jb51" 'user name for accessing the database
DataBaseName = "jb51" 'Database Name
DataBasePsw = "www.jb51.net" 'database access password
Set conn = Server. CreateObject ("ADODB. Connection ")
ConnStr = "driver = {SQL Server}; server =" & dataserver & "; UID =" & datauser & "; PWD =" & databasepsw & "; Database =" & databasename
Conn. open ConnStr
If Err Then Err. Clear: Set conn = Nothing: Response. Write "the database connection fails. Check the database parameter settings in the Conn. asp file. ": Response. End

Create a record set object:
Set rs = server. createobject ("adodb. recordset ")
Rs. open SQL statement, conn, 1, 3
Common SQL commands:
Data Record Filtering:
SQL = "select * from data table where field name = Field Value order by field name"
SQL = "select * from data table where field name like '% Field Value %' order by field name"
SQL = "select top 10 * from data table where field name order by field name"
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"
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"
Delete data records:
SQL = "delete from data table where condition expression"
SQL = "delete from data table" (delete all data table Records)
Add data records:
SQL = "insert into data table (Field 1, Field 2, Field 3 ...) Valuess (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)
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.

Create and delete data tables:
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)
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