ASP Advanced Tutorial: Message query function (i)

Source: Internet
Author: User
Tags count query
Tutorial when one day, your mood suddenly become very bad, but coincidentally, in your guest book, your net friend left a very exciting words, so you quickly regained the happy mood. But after a while you're feeling bad again, but this time you're not as lucky as you were, because no one gives you encouragement, so you naturally want to take another look at the last one that inspired you. Then you will think: if my guest book has the message query function is good!



in order to be able to achieve your wish, so I decided in this chapter tutorial for you to have message query function of the guest book program. Before we explain, let's learn the command object that will be used in the source program.



first let's take a look at the properties and methods provided by the command object and their corresponding functions



ActiveConnection Attribute--Establish a link relationship with the connection channel

CommandText Property--Specify data query information

CommandTimeout Property-The maximum time allowed to continue execution after a data query is started

CommandType Property--Specifies the type of data query information

Prepared Property--Specifies whether the data query information is to be compiled in advance

CreateParameter Method--to establish a new parameter object

Execute Method--Data query for database



The command object is also a member of the ADO object collection, which is mainly used to control the request information sent to the database, telling the database: "Which data table?" What field do you want the data in? What restrictions must the data meet? Please keep all the data that meet my requirements in the Recordset object and return back! "In the final analysis, the Command object's function is to execute SQL (Structured Query Language Structured Query Language, a tool for organizing, managing, and retrieving data stored in a computer database. ; is a specific type of database-relational database command. If you are a more careful person, you must remember that I have already used the SQL command in the third chapter, so you will ask me: "You did not use the command object?" ”



Indeed, without a command object we can execute SQL commands as well. We can also execute SQL commands using the Connection object or the Recordset object. The method looks like this:




Set RS = conn. Execute (SQL command)

' Executes the Select SQL command using the Connection object and assigns the result to the Recordset object.

Conn. Execute SQL command

' SQL command to perform data operations using the Connection object.

Set rs = Server.CreateObject ("ADODB.") Recordset ")

Rs. Open SQL command, conn


' creates the Recordset object first, then executes the SQL command to select the data.

How does
use the command object to execute SQL commands? Please see:

Set cmd = Server.CreateObject ("Adodb.command")

Set cmd. ActiveConnection = conn

cmd.commandtext = SQL

Set rs = cmd. Execute



' Visible using command objects to execute SQL commands, you have to set the Connection object and SQL command to the ActiveConnection and CommandText properties of the Object command before executing cmd. The Execute function.



from the above we can see that the original use of the Recordset object and use the command object to execute the SQL command is essentially the same. That being the case, then why do we have to use the command object? To illustrate this issue, I used two different methods to add a message query for the guest book, which query "guestbook last five days message record" and "Message Record Date query" is the use of command objects to execute SQL commands, and "message name query" and "the latest 10 Message Records" Query uses a Recordset object to execute the SQL command.


said a lot of this is also the point, we first learn to use the Recordset object to achieve the "Guestbook Name Query" and "the latest 10 message records" query. The following is a step-by-step approach to the instructions.

Step One: First we have to design a message query interface (datesearch.asp), as shown in the following figure:






Step Two: Use the Recordset object to execute the SQL command to implement the "latest 10 Message Records" query.

Set conn = Server.CreateObject ("ADODB. Connection ")

DBPath = Server.MapPath ("Book2.mdb")

Conn. Open driver={microsoft Access Driver (*.mdb)};d bq= "& DBPath

sql = "SELECT top * out Guestbook ORDER by ID Desc"



here uses the ORDER by ID Desc to sort by the ID field from large to small. Because in the database I will ID field data type to "AutoNumber", the message record ID will be sorted from small to large, so to query the latest 10 comments is the last 10 records of the query ID.

Set RS = conn. Execute (SQL)


use the Recordset object to execute SQL commands, the principle of "message name" query is consistent with the above, the program looks like this:



NameSearch = Request.Form ("name")

NameSearch = Replace (NameSearch, "'", "" ")

' Remember when I introduced the SQLSTR function in chapter three? Forget about it and go back to see it.

Set conn = Server.CreateObject ("ADODB. Connection ") DBPath =server.mappath (" Book2.mdb ")

Conn. Open driver={microsoft Access Driver (*.mdb)};d bq= "& DBPath

sql = "SELECT * from guestbook where name = ' &namesearch& ' ORDER by ID Desc"

Set RS = conn. Execute (SQL)



Step Three: A Web page (search.asp) that designs query results.




Sub Search (RS)

Response.Write "< center>< table border=2 width=90% cellpadding=2 cellspacing=2 bordercolorlight= #000000 bordercolordark= #FFFFFF bordercolor= #FFFFFF bgcolor= #FFFFFF align=center> "

Response.Write "< TR bgcolor= #33CCCC >"

for i=0 to Rs. Fields.count-1

Response.Write "< td>" & Rs. Fields (i). Name & "</td>"


Response.Write "</tr>"

' "& Rs. Fields (i). Name & "indicates that the field header of the datasheet is displayed"

while not Rs. EOF

Response.Write "< tr>"

for i=0 to Rs. Fields.count-1

Response.Write "< td>" & Rs. Fields (i). Value & "</td>"


Response.Write "</tr>"

Rs. MoveNext


Response.Write "</table></center>"

' "& Rs. Fields (i). Value & "data field contents" that show the datasheet

End Sub


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.