ASP common database connection and operation method and skill

1.ASP connect to an Access database
<%
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:
<%
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
%>
To set up a Recordset object:
Set rs=server.createobject ("Adodb.recordset")
Rs.Open SQL statement, conn,3,2
3. SQL Common Command use method:
(1) 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 * 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"
(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 records from datasheet)
(4) Adding data records:
sql= INSERT into Data table (field 1, Field 2, Field 3 ...) valuess (value 1, value 2, value 3 ...)
Sql= INSERT INTO Target datasheet SELECT * from source datasheet (add a record from the source datasheet to the destination datasheet)
(5) Data record statistic function:
AVG (field name) to derive a table column average
Count (*| field name) statistics on the number of data rows or the number of data rows that have a value for a column
Max (field name) gets the maximum value of a table column
Min (field name) gets the smallest value of a table column
sum (field name) adds the value of the data bar
How to reference the above function:
Sql= "Select sum (field name) as Alias from data table where condition expression"
Set Rs=conn.excute (SQL)
Using RS ("Alias") to obtain the value of the unified, other functions using the same.
(5) The establishment and deletion of the data table:
CREATE Table datasheet Name (field 1 type 1 (length), Field 2 type 2 (length) ...)