Is your website still as static as ever? Among the Internet websites that are growing exponentially, a considerable number are still static, and have invisibly fallen far behind the times. The so-called static means that the web page content of the website is fixed. When the user's browser requests web page content from the WEB server through the Internet's HTTP protocol, the server only transmits the originally designed static HTML document to the user's browser. The content of its page only uses standard HTML code, plus at most dynamic pictures in the popular GIF89A format, such as the animation effect of several puppies and kittens running around. If a website maintainer wants to update the content of a web page, he must manually update all of his HTML documents.
The fatal weakness of static websites is that they are difficult to maintain. In order to continuously update web page content, you must repeatedly create HTML documents. As the content and amount of information on the website increase, you will feel that the workload is unimaginable.
Generally speaking, a real and complete website cannot be separated from a database, because in actual applications, a lot of data needs to be saved, and these data are often related. It is very convenient to use a database to manage these data. Query and update. There are many kinds of databases, such as: Fox database (.dbf), Access database (.mdb), Informix, Oracle and SQL Server, etc. Here, I will take out the information on how ASP accesses the Access database that I have compiled recently and Share it with everyone.
Commonly used database statements
1. SELECT statement: Instructs the database engine to return 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 a specified table based on specific criteria.
4.DELETE statement: Create a delete query to clear records from one or more tables listed in the FROM clause and matching the WHERE clause.
5.EXECUTE statement: used to activate PROCEDURE (process)
Use ASP to make your own address book to practice...
1. Create a database:
Use Microsoft Access to create an empty database named data.mdb, and use the designer to create a new table. Enter the following fields:
Field name data type description other
ID AutoNumber Data Identification Field Size: Long New Value: Increasing Index: Yes (no duplication)
username text name default value
usermail text E-mail default value
view Number of views Field size: Long Default value: 0 Index: None
indate time date join time default value
Save it as a data.mdb file. For ease of explanation, I just made a relatively simple library.
2. Connect to 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: You only need to connect once in a page, and the connection must be closed promptly after the database is used.
conn.Close
Set conn = Nothing
3. 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 = Feng Yun Sudden Change
usermail = [email protected]
indate = Now()
sql = insert into data (username,usermail,indata) values('&username&','&usermail&','&indate&')
conn.Execute(sql)
conn.Close
Set conn = Nothing
Description: Establish a database connection; obtain the name and E-mail string through the form, and obtain the current time and date with Now(); add new records using the insert into statement; execute with conn.Execute; and finally close.
4. Select records in the database
1. Select the fields of all records (sort by records in reverse order): sql = select * from data order by ID desc
2. Select the name and email fields of all records (without sorting): sql = select username,usermail from data
3. Select all records with the name Fengyun Mutation: sql = select * from data where username='Feng Yun Mutation'
4. Select all records using the 163 mailbox (sorted by number of views): sql = select * from data where usermail like '%@163.com%' order by view desc
5. Select the latest 10 records: sql = select top 10 * from data order by ID desc
The SQL statement is already known, but in web applications, you have to create a RecordSet object to obtain the record set, so that the values taken out from the database can be applied to the web page. If all the records are now displayed on the web page, it will look like this:
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 <p>Name: & rs(username) & E-mail: & rs(usermail) & View: & rs(view) & times& rs(indate) &Join</p>
rs.MoveNext
Loop
rs.Close
Setrs=Nothing
conn.Close
Set conn = Nothing
Description: Establish a database connection; create rs to obtain the record set; display records in a loop, rs.Eof represents the end of the record, rs.MoveNext represents moving to the next record; and finally close.
5. Modify (update) database records
E-mail to modify the record:
Set conn = Server.CreateObject(ADODB.Connection)
conn.Open driver={Microsoft Access Driver (*.mdb)};dbq=&Server.MapPath(data.mdb)
ID=1
usermail = [email protected]
sql = update data set usermail='&usermail&' where ID=&CInt(ID)
conn.Execute(sql)
conn.Close
Set conn = Nothing
Description: Establish a database connection; obtain the record ID and new E-mail string; use the update statement to modify the record; use conn.Execute to execute; and finally close.
If the view value of the record is increased by 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; obtain the record ID; use the delete statement to delete the record; conn.Execute to execute; and finally close.
To delete multiple records: sql = delete from data where ID in (ID1,ID2,ID3)
Delete all records as: sql = delete from data
Summarize:
The above tutorials are compiled by myself and are written for beginners of ASP. They only introduce some basic usage. If there are any shortcomings, I hope you can forgive me.