ASP connection access, sql database code and database operation code, friends in need can refer to the following 1. ASP and Access database connection:
Copy the code code 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 the code code 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 asp connection to sql:
Copy the code code as follows:
DataServer = jb51 'Database server IP
DataUser = jb51 'Access database user name
DataBaseName = jb51 'Database name
DataBasePsw = www.vevb.com 'Access database 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 Database connection error, please check the database parameter settings in the Conn.asp file. :Response.End
Create a recordset object:
set rs=server.createobject(adodb.recordset)
rs.open SQL statement,conn,1,3
How to use 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 conditional expression
sql=update data table set field 1=value 1, field 2=value 2... field n=value n where conditional expression
Delete data records:
sql=delete from data table where conditional expression
sql=delete from data table (delete all records in the data table)
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 from the source data table to the target data table)
Data recording statistical functions:
AVG(field name) derives a table column average
COUNT(*|field name) counts the number of data rows or counts the number of data rows with a value in a certain column
MAX (field name) gets the maximum value of a table column
MIN (field name) gets the minimum value of a table column
SUM(field name) adds the values of the data columns
How to reference the above function:
sql=select sum(field name) as alias from data table where conditional expression
set rs=conn.excute(sql)
Use rs (alias) to obtain statistical values, and use the same functions as above for other functions.
Creation and deletion of 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)
Methods of recordset objects:
rs.movenext moves the record pointer down one line from the current position
rs.moveprevious moves the record pointer up one row from the current position
rs.movefirst moves 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 moves the record pointer to row N of the data table
rs.absolutepage=N moves the record pointer to the first row of page N
rs.pagesize=N sets each page to N records
rs.pagecount returns the total number of pages according to the settings of pagesize
rs.recordcount returns the total number of records
rs.bof returns whether the record pointer exceeds the beginning of the data table, true means yes, false means no
rs.eof returns whether the record pointer exceeds the end of the data table, true means yes, false means no
rs.delete deletes the current record, but the record pointer does not move downwards
rs.addnew adds records to the end of the data table
rs.update updates data table records