ASP and database application (simple introduction)

Data | database

This tutorial is the fastest tutorial for beginners to learn ASP and database operations, so that some beginners get the fastest start:

Generally speaking, a real, complete site is inseparable from the database, because the actual application, need to save a lot of data, and these data are often associated with, using the database to manage the data, can be very convenient to query and update. There are many types of databases, such as the Fox database (. dbf), Access database (. mdb), Informix, Oracle, SQL Server, and so on, where I'll take a Microsoft Access database as an example of how the ASP accesses the database.

　　Common Database statements

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 the value of a field in a specified table based on a particular guideline.
4.DELETE statement: Create a delete query to purge records from one or more tables that are listed from the FROM clause and that conform to the WHERE clause.
5.EXECUTE statement: For activating PROCEDURE (procedure)

　　I. Establishment of a database:

Create a new table using the designer by creating an empty database named Data.mdb with Microsoft Access. Enter the following fields:

Field name data type description Other
ID AutoNumber data ID field Size: Long integer new value: Incremental index: (No Duplicates)
Username Text name default value
Usermail text e-Mail default value
View number viewing sub-number segment size: Long integer default: 0 index: None
Indate Time Date Join time default value

Save as a data.mdb file, just to make it easier to explain, but to do a relatively simple library.

　　Second, the connection database

Method 1:
Set conn = Server.CreateObject ("ADODB. Connection ")
Conn. Open "Driver={microsoft Access driver (*.mdb)};d bq=" &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, as long as the connection can be once, the database after use to close the connection in a timely manner.
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)};d bq=" &server.mappath ("Data.mdb")

Username = "Web Teaching Network"
Usermail = "web@webjx.com"
Indate = Now ()

sql = "INSERT into data (Username,usermail,indata) VALUES (' &username&" ', ' "&usermail&" ', ' "& indate& "')"
Conn. Execute (SQL)

Conn. Close
Set conn = Nothing

Description: Establishes a database connection, obtains the name, the e-mail string from the form, now () gets the current time date, adds the new record by using the INSERT INTO statement; Conn. Execute, and finally close.

　　Iv. Select the records in the database

1. Select the fields for all records (sorted by record): sql = "SELECT * from Data order BY ID DESC"
2. Select all record names and e-mail fields (not sorted): sql = "Select Username,usermail from Data"
3. Select All records with the name "Web Teaching network": sql = "SELECT * from Data where username= '" "Web Teaching Network" "
4. Select all records to use WEBJX mailbox (sorted by number of views): sql = "SELECT * from data where usermail like '%" @webjx. com "% ' orders by view Desc"
5. Select the latest 10 records: sql = "SELECT top * from Data order BY ID DESC"

The SQL statement already knows, but in the Web application, you also have to create a Recordset object to get the recordset to apply the values taken from the database to the Web page, if you now display all the records on the Web page:

Set conn = Server.CreateObject ("ADODB. Connection ")
Conn. Open "Driver={microsoft Access driver (*.mdb)};d bq=" &server.mappath ("Data.mdb")

sql = "SELECT * FROM Data"
Set rs = Server.CreateObject ("ADODB.") RecordSet ")
Rs. Open sql,conn,1,1

Do but not Rs. Eof
Response.Write "<p> Name:" & RS ("username") & "e-mail:" & RS ("Usermail") & "View:" & RS ("View") & "Times" & RS ("Indate") & "Add </p>"
Rs. MoveNext
Loop

Rs. Close
Set rs = Nothing
Conn. Close
Set conn = Nothing

Description: Establish database connection, create RS to get Recordset, loop display record, Rs. Eof represents the end of the record, Rs. MoveNext is moved to the next record, and finally closes.

　　V. Modify (update) database records

e-mail to modify records:

Set conn = Server.CreateObject ("ADODB. Connection ")
Conn. Open "Driver={microsoft Access driver (*.mdb)};d bq=" &server.mappath ("Data.mdb")

ID = 1
Usermail = "web@webjx.com"

sql = "Update data set usermail= '" &usermail& "where id=" &cint (ID)
Conn. Execute (SQL)

Conn. Close
Set conn = Nothing

Description: Establishes the database connection, obtains the record ID, the new e-mail string, uses the UPDATE statement to modify the record; Conn. Execute, and finally close.
If the view value of the record is added to 1, then: sql = "Update data set view=view+1 where id=" &cint (ID)

　　Vi. deleting database records

Delete a record:

Set conn = Server.CreateObject ("ADODB. Connection ")
Conn. Open "Driver={microsoft Access driver (*.mdb)};d bq=" &server.mappath ("Data.mdb")

ID = 1

sql = "Delete from data where id=" &cint (ID)
Conn. Execute (SQL)

Conn. Close
Set conn = Nothing

Note: Establish a database connection; get the record ID; Delete the record using the DELETE statement; Conn. Execute, and finally close.
Delete multiple records as: sql = "Delete from data where ID in (ID1,ID2,ID3)"
Delete all records as: sql = "Delete from data"

Summarize:

The above tutorial is for the ASP's beginner to write, just introduced some basic usage, after understanding can oneself go to try, the important lies in can extrapolate, the synthesis use. For more syntax and parameters see Microsoft Access Help for a reference to the Microsoft Jet SQL, if the expression is not clear there are places to point out that if there is any problem in the application, but also hope to be able to propose.