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 Identifier Field Size: Long Integer Novel Value: Increment index: Have (no duplicate) UserName text Name Default Usermail Text E-mail Default VIEW Number View Digital Segment Size: Long Integer default: 0 Index: No Indate Time Date Add 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.jet "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 Cloud" Usermail = "fytb@163.com" Indate = now ()
SQL = "INSERT INTO DATA" 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 recorded fields (Sort by recording step): SQL = "Select * from data order by id" 2. Select all recorded names and E-mail fields (no sorting): SQL = "Select Username, usermail From Data "3. Select all records named" Windy Clutch ": SQL =" Select * from data where username = '"" Wind cloud mutation "'" 4. Choose all records using 163 mailbox (Sort by view): SQL = "Select * from data where usermail limited" @ 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 = server.createObject ("adoDb.recordset") rs.open SQL, CONN, 1, 1
Do WHILE NOT RS.EOF response.write "
Name:" & rs ("UserMail" & "" Usermail ") &" View: "& RS (" View ") & "Supreme" & RS ("INDATE") & "Join " rs.movenext loop
rs.close set = 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. Deleting multiple records: SQL = "Delete from data where id in (id1, id2, id3)" Delete all records: SQL = "Delete from data"
