ASP Advanced Tutorial Ⅸ: Message query function (i)

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 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.
　　

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

ActiveConnection Property--establishing a link 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--Creating 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, we can execute SQL commands as well without command objects. 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

' Create the Recordset object first, then execute the SQL command to select the data.
How do you 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

' You can use the command object to execute the SQL command by setting the Connection object and SQL command to the ActiveConnection and CommandText properties of the Object command before executing cmd. The Execute function.

As we can see from the above, using the Recordset object and using 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.

This is a lot to talk about, we first learn to use the Recordset object to implement 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 * from Guestbook ORDER by ID Desc"

This 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)

Using the Recordset object to execute the SQL command, the "message name" query principle 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: Design the Web page for query results (search.asp).

<%
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>"
Next
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>"
Next
Response.Write "</tr>"
Rs. MoveNext
Wend
Response.Write "</table></center>"
' & Rs. Fields (i). Value & "data field contents" that show the datasheet
End Sub
%>

ASP Advanced Tutorial Ⅸ: Message query function (ii)

We will continue to explain how to use the command object to execute SQL commands, "message record Date query" and "Guestbook last five days message record query." However, before applying the command object to execute SQL commands, we must first establish the query in the message database, if we want to query the "guest book in the past five days message record", then in the message database to establish a query in the specific method as shown below. (For a database, you can create five different types of queries in Access because we're looking for a message record between two dates, so I'm just going to use the "Design view" to create a parameter query), and of course you can "create a query using a wizard." Using the Design view to build a query is much more flexible than using a wizard. For example, in Design view, you can create a simple query such as a select query, and you can create complex queries like parameter queries and action queries, and you can add, move, insert, and delete fields in the query design grid , you can set criteria and sort order, calculate sum and average, etc.)

In access, use the ' design ' view to establish a parameter query:
Open the message database, click the Query tab in the Database window, and then click the New button.
Second, in the New Query dialog box, click Design View, and then click OK.
In the Show Table dialog box, select the Tables tab, click the Add button or double-click the name of the guestbook table, add it to the Query window, and then close the Show Table dialog box.
In the field list of the "Guestbook" table, click the field name of the ID field, and hold down the mouse, and drag it to the first column of the query design grid. Similarly, you can drag the field names of the name, phone, EMAIL, subject, message, and Time fields to the other columns in the query design grid, in turn.
In the Criteria cell under the Time field used as a parameter, type the following expression:
between[Please type the start date]and[Please type an end date].
Six, click the "Save" button on the toolbar to save the query, there will be a "Save as" dialog box, we in the Save As dialog box, enter the name of the query table "message Record Date Query", and then click the OK button.

After the query has been established in the message database, we will then write the following SQL command to execute the date query using the Command object (date.asp):
<!--#include file= "Adovbs.inc"-->
<!--#include file= "Search.asp"-->
<%
Head= "Guest Book query"
Start=request ("Start")
Last=request ("Last")
' reads the data entered by the user and assigns it to the start and last variables
If start= "" Then start = #99 -10-1#
If last= "" Then last = #99 -12-30#
' If the user does not enter the start and end time of the query, display the default values defined here 99-10-1 and 99-12-30 in the start time and End time query boxes
% >
< h2 align= "Center" > <%=head%>< hr noshade color= "Red" >
<%
Set conn = Server.CreateObject ("ADODB. Connection ")
DBPath = Server.MapPath ("Book2.mdb")
Conn. Open "Driver={microsoft Access driver (*.mdb)};d bq=" & DBPath
Set cmd = Server.CreateObject ("Adodb.command")
Set cmd. ActiveConnection = conn
Cmd.commandtext = "Message Record Date query"
' Sets the connection object and SQL command to the ActiveConnection property of the Command object and the CommandText property
ReDim param (1)
' declares an array of two elements, because the message date query must have the start and end times of the query, so here you must declare an array of two elements
Param (0) = CDate (start)
PARAM (1) = CDate (last)
Set rs = cmd. Execute (, param)
' Assign the start and last variables to the Param array, and then pass in CMD. In the Execute function, here is the function cmd. Execute contains two parameters, the first of which is the number of data records you want to return, where the default indicates that you want to return all of the data records; parameter two "param" is the parameter of the SQL command.
Search RS
%>
< hr noshade color= "Red" > < div align= "center" >
< a href= "datesearch.asp" class= "Text2" > Return message Query </a>
Similarly, the use of command objects to execute SQL commands, "Guestbook past five days message record query" principle and the implementation of the "Message record Date Query" principle is the same, we can write as follows:
Set conn = Server.CreateObject ("ADODB. Connection ")
DBPath = Server.MapPath ("Book2.mdb")
Conn. Open "Driver={microsoft Access driver (*.mdb)};d bq=" & DBPath
Set cmd = Server.CreateObject ("Adodb.command")
Set cmd. ActiveConnection = conn
Cmd.commandtext = "Guest book last five days message record"
Set rs = cmd. Execute
Search RS

At this point, two ways to implement the query we have learned, we can say what is the difference between them?
For example, if we directly write a SQL command in the ASP program, to know whether the command is correct, we must start browser browsing this program to test out, if the SQL command is wrong, we have to modify the ASP program, and then browse again until the correct If we use the command object to execute the SQL command, the SQL command must be stored as a "query object" until the query established in the message database is completely correct. So in the ASP program we simply assign the name of the query object to the Command object's CommandText parameter to execute the SQL command correctly.