Connect to the ACCESS database in ASP and add or delete the query

<1>. Database Connection (used to separate the connection file conn. asp)
<%
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open "driver = {Microsoft Access Driver (*. mdb)}; DBQ =" & server. mappath ("/BBS/db1/user. mdb ")
%>
(Used to connect the user. mdb database under the BBS/db1/directory)
<2> display database records

Principle: displays the records in the database one by one in the client browser, reads each record in the database in sequence
If it is from start to end: Use a loop and determine whether the pointer is used until the end: not Rs. EOF
If it is from the end to the header: Use a loop and determine whether the pointer is used to start using: not Rs. bof

<! -- # Include file = conn. asp --> (conn. asp is used to open user. MDB data in the BBS/db1/directory.
Library)
<%
Set rs = server. Createobject ("ADODB. recordset") (Create A recordset object)
Sqlstr = "select * From message" ----> (message is a data table in the database, that is,
The data table that stores the data)
Rs. Open sqlstr, Conn, 1, 3 ----> (indicates how to open the database)
Rs. movefirst ----> (move the pointer to the first record)
While not Rs. EOF ----> (judge whether the pointer ends)
Response. Write (RS ("name") ----> (display the name field in the message table)
Rs. movenext ----> (move the pointer to the next record)
End ----> (loop end)
------------------------------------------------------
Rs. Close
Conn. Close is used to close the database.
Set rs = nothing
Set conn = nothing
-------------------------------------------------------
%>
The response object is the information sent by the server to the client browser.
<3> Add database records
Rs. addnew and Rs. Update functions are used to add database records.
 
<! -- # Include file = conn. asp --> (conn. asp is used to open user. MDB data in the BBS/db1/directory.
Library)
<%
Set rs = server. Createobject ("ADODB. recordset") (Create A recordset object)
Sqlstr = "select * From message" ----> (message is a data table in the database, that is,
The data table that stores the data)
Rs. Open sqlstr, Conn, 1, 3 ----> (indicates how to open the database)
Rs. addnew adds a new record
RS ("name") = "XX" pass the value of XX to the name field
Rs. Update refresh Database
------------------------------------------------------
Rs. Close
Conn. Close is used to close the database.
Set rs = nothing
Set conn = nothing
-------------------------------------------------------
%>
. <4> delete a record
Rs. Delete and Rs. Update are used to delete database records.
<! -- # Include file = conn. asp --> (conn. asp is used to open user. MDB data in the BBS/db1/directory.
Library)
<%
Dim name
Name = "XX"
Set rs = server. Createobject ("ADODB. recordset") (Create A recordset object)
Sqlstr = "select * From message" ----> (message is a data table in the database, that is, the data table where the data you want to display is stored)
Rs. Open sqlstr, Conn, 1, 3 ----> (indicates how to open the database)
-------------------------------------------------------
While not Rs. EOF
If Rs. ("name") = Name then
Rs. Delete
Rs. Update: query whether the value of the Name field in the data table is equal to the value of the variable name "XX". If yes, delete the data.
Except,
Else otherwise, the query continues until the pointer ends.
Rs. movenext
End if
End
------------------------------------------------------
------------------------------------------------------
Rs. Close
Conn. Close is used to close the database.
Set rs = nothing
Set conn = nothing
-------------------------------------------------------
%>
<5> query Databases
(A) the query field is character-type.
<%
Dim user, pass, QQ, mail, message
User = request. Form ("user ")
Pass = request. Form ("pass ")
Qq = request. Form ("QQ ")
Mail = request. Form ("mail ")
Message = request. Form ("message ")
If trim (User) & "X" = "X" or trim (PASS) & "X" = "X" then (checks whether the user value and pass value are empty, and can detect
To space)
Response. Write ("registration information cannot be blank ")
Else
Set rs = server. Createobject ("ADODB. recordset ")
Sqlstr = "select * from user where user = '" & user & "'" (query the user field in the user data table, where user
Field is character type)
Rs. Open sqlstr, Conn, 1, 3
If Rs. EOF then
Rs. addnew
RS ("user") = user
RS ("pass") = pass
RS ("QQ") = QQ
RS ("mail") = Mail
RS ("message") = message
Rs. Update
Rs. Close
Conn. Close
Set rs = nothing
Set conn = nothing
Response. Write ("registration successful ")
End if
Rs. Close
Conn. Close
Set rs = nothing
Set conn = nothing
Response. Write ("register duplicate names ")
%>
(B) the query field is Numeric.
<%
Dim num
Num = request. Form ("num ")
Set rs = server. Createobject ("ADODB. recordset ")
Sqlstr = "select * From message where id =" & num (query whether the value of the ID field in the message data table matches
Num is equal, where ID is Numeric)
Rs. Open sqlstr, Conn, 1, 3
If not Rs. EOF then
Rs. Delete
Rs. Update
Rs. Close
Conn. Close
Set rs = nothing
Set conn = nothing
Response. Write ("deleted successfully ")
End if
Rs. Close
Conn. Close
Set rs = nothing
Set conn = nothing
Response. Write ("deletion failed ")
%>
%>

This article is reproduced from [Yu Zhiguo Website Design Studio]: http://www.yuzhiguo.com/articleview.asp? Id = 189