In general, a true, complete site is inseparable from the database, because there is a lot of data that needs to be saved, and the data is often associated, and the database is used to manage this data, it can be very convenient. Query and update. There are many species, such as: Fox Database (.dbf), Access Database (.MDB), Informix, Oracle, and SQL Server, etc., here I will explain how the ASP accesses the database as an example with the Microsoft Access database.
Common database statement
1.Select statement: The command database engine returns information from the database as a set of records.
2. INSERT INTO statement: Add one or more records to a table.
3.Update statement: Create an update query to change field values in the specified table based on a specific criterion.
4. DELETE statement: Create a delete query to list the record from the From clause and comply with one or more of the WHERE clause.
5.EXECUTE statement: Used to activate Procedure (Procedure)
Use ASP to make a self-cultivation of your own communication ...
First, build a database:
Establish a null database called Data.mdb with Microsoft Access, create a new table using the designer. Enter the following fields:
Field Name Data Type Description Other
ID Auto Number Data Identification Field Size: Long Integer Novel Value: Increment Index: Yes (None Repeat)
Username text name default
Usermail text E-mail default
View Digital View Field Size: Long Integer Default: 0 Index: None
Indiate time date to join the time default
Save as a Data.mdb file, in order to make it easy, just make a relatively simple library.
Second, connect the database
method 1:
Set conn = server.createObject ("adoDb.connection")
Conn.open "Driver = {Microsoft Access Driver (* .mdb)}; dbq =" & Server.mappath ("DATA.MDB")
Method 2:
Set conn = server.createObject ("adoDb.connection")
Conn.open "provider = microsoft.jet.Oledb.4.0; data source =" & server.mappath ("data.mdb")
Note: A page, just connect once, the database is turned off again after the database is used.
Conn.close
Set conn = Nothing
Third, add new records to the database
Set conn = server.createObject ("adoDb.connection")
Conn.open "Driver = {Microsoft Access Driver (* .mdb)}; dbq =" & Server.mappath ("DATA.MDB")
Username = "Wind Clutch"
Usermail = "fytb@163.com"
Indate = now ()
SQL = "INSERT INTO DATA (Username, Usermail, Indata) Values ('" & username & ",'" & usermail & ", '" & indecute (SQL)
Conn.close
Set conn = Nothing
Description: Establish a database connection; obtain your name by form, e-mail string, now () Get the current time date; add a new record using the INSERT INTO statement; Conn.execute is executed; finally closed.
Fourth, select the record in the database
1. Select all the fields of the record (sorted by recording): SQL = "SELECT * from Data Order By ID DESC"
2. Select all the names and E-mail fields (not sorted): SQL = "SELECT UserName, Usermail from Data"
3. Choose all records named "Feng Yun": SQL = "SELECT * from data where username = '" Feng Yun mutation "'"
4. Choose all records using the 163 mailbox (order to view the number of views): SQL = "Select * from data where usermail like '%" @ 163.com "%' Order By View Desc"
5. Choose the latest 10 records: SQL = "SELECT TOP 10 * from data order by id desc"
The SQL statement already knows, but when the web is applied, you have to create a RecordSet object to get a record set, in order to apply the value taken from the database to the web page, if all records are displayed on the web page:
Set conn = server.createObject ("adoDb.connection")
Conn.open "Driver = {Microsoft Access Driver (* .mdb)}; dbq =" & Server.mappath ("DATA.MDB")
SQL = "SELECT * FROM DATA"
SET RS = Server.createObject ("AdoDb.Recordset")
RS.Open SQL, CONN, 1, 1
Do While Not Rs.eof
Response.write "
Name: "" & RS ("UserName") & "" Usermail ") &" View: "& rs (" View ") &" Supreme "& RS (" INDATE ") &" Add "
rs.movenext
Loop
Rs.close
SET RS = Nothing
Conn.close
Set conn = Nothing
Description: Establish a database connection; create RS to get a recordset; loop display record, RS.eof represents the end of the record, RS.MOVENEXT indicates moved to the next record; finally closed. V. Modify (Update) Database Record
Modify the recorded E-mail:
Set conn = server.createObject ("adoDb.connection")
Conn.open "Driver = {Microsoft Access Driver (* .mdb)}; dbq =" & Server.mappath ("DATA.MDB")
ID = 1
Usermail = "fytb@163.com"
SQL = "Update Data Set Usermail = '" & Usermail & "' Where ID =" & CINT (ID)
CONN.EXECUTE (SQL)
Conn.close
Set conn = Nothing
Description: Establish a database connection; get the record ID, a new E-mail string; use the UPDATE statement to modify the record; conn.execute is executed; finally closed.
If the recorded view value is added 1, then: sql = "Update data set view = view 1 where id =" & cint (id)
6. Delete database records
Delete a record:
Set conn = server.createObject ("adoDb.connection")
Conn.open "Driver = {Microsoft Access Driver (* .mdb)}; dbq =" & Server.mappath ("DATA.MDB")
ID = 1
SQL = "delete from data where id =" & cint (id)
CONN.EXECUTE (SQL)
Conn.close
Set conn = Nothing
Description: Establish a database connection; get the record ID; use the delete statement to delete the record; conn.execute is executed; finally closed.
Delete multiple records: SQL = "delete from data where id in (id1, id2, id3)"
Delete all records: SQL = "delete from data"
to sum up:
The above tutorial is written for beginners of ASP, just introduces some basic usage, you can try it yourself after understanding, and it is important to mention a non-three, comprehensive use. For more syntax and parameters, please refer to Microsoft Jet SQL reference in Microsoft Access Help. Since I am too shallow, the ability is limited, if I have an expression, it is still pointed out that if any problems are encountered in the application, I hope to be able to propose. Thank you.
