Using 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)
Use ASP to do one's own address Book practice practicing bar ...
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
Party * 1:
Set conn = Server.CreateObject ("Adodb.connection"
Conn.Open "Driver={microsoft Access Driver (*.mdb)};d bq=" &server.mappath ("Data.mdb"
Party * 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 = "Mutation"
Usermail = "Fytb@163.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 () obtains the current time date, adds the new record by using the INSERT INTO statement, Conn.execute to execute, and finally closes.
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 Name "mutation": sql = "SELECT * from Data where username= '" mutation ""
4. Choose to use all records of 163 mailbox (sorted by number of views): sql = "SELECT * from data where usermail like '%" @163.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 as 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
Set rs = Nothing
Conn.close
Set conn = Nothing
Description: Establish the database connection, create RS to get the Recordset, loop the record, rs.eof indicate the end of the record, rs.movenext the next record, and then close.
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 = "Fytb@163.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, modifies the record by using the UPDATE statement, Conn.execute to execute, and finally closes.
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
Description: Establishes a database connection, obtains the record ID, deletes the record using the DELETE statement, Conn.execute to execute;
Delete multiple records as: sql = "Delete from data where ID in (ID1,ID2,ID3)"
Delete all records as: sql = "Delete from data"
Current 1/4 page
1234 Next read the full text
