server| Data | Database ASP and its ADO component introduction
----ASP (Active server Pages) is a server-side scripting language that can be viewed as a combination of HTML, scripting, and CGI (Common Gateway interfaces). But it is more secure than HTML, more flexible than script, and more efficient than CGI.
----an ASP program with the extension ". ASP, which can contain HTML statements, ASP commands, text, and all descriptive languages that can be nested in HTNK documents (such as vbscripts,javascripts, etc.). The ASP commands must be located between the ASP-defined symbol "<%", "%>", and when the user accesses the ASP Web page, the Web server resolves the ASP commands between "<%", "%>", and then responds to the user with the results. This allows users to not see the ASP source files. This is the reason for the high confidentiality of ASP.
----ASP has built-in ADO components, so you can easily access various databases. ADO is the latest technology offered by Microsoft to developers to access databases in Web pages. ADO components mainly include Connection objects, command objects, parameter objects, Recordset objects, Field objects, property objects, error objects, and other seven objects and fields data sets, Properties data collection, parameters data collection, errors data collection, etc. four data sets.
----The Connection object is responsible for connecting to the data source (database or text file);
----The command object to pass the specified SQL command;
----The Parameter object to provide the SQL command parameters required by the command object;
The----Recordset object is responsible for browsing and manipulating data retrieved from the data source;
----A Field object to specify the specific fields of the Recordset object.
----This is a couple of objects that are closely related to database access.
fuzzy queries for SQL Server databases
----A SELECT query command for a SQL Server database to implement a query for a database. Fuzzy queries can be done using the "like" keyword and the "%" wildcard in the WHERE clause of the SELECT command. The following statement looks up the authors table in the pubs database for the value of the Name field that contains all the records for the "Smith" string.
Use pubs
Select * FROM Authors
Where Name like "%smith%"
Go
----SQL Server database also provides a database object called stored Procedure (stored procedure) that allows users to store some commonly used command procedures in the form of stored procedures, as long as you enter stored procedure names when you need to run these stored procedures. This not only simplifies operations, but also improves efficiency (stored procedures run much more efficiently than commands). The selection of the stored procedure name should conform to the SQL Server naming convention, which, when it begins with "#", represents a temporary stored procedure that exists only when the application is running, and is automatically deleted when the program is finished.
----The following statement to create a temporary stored procedure named #query:
Create Procedure #query
As
Select * FROM Authors
Where Name like "%smith%"
ASP implementation of database fuzzy query
----in the ASP command, there is no similar to the "like" keyword and "%" wildcard characters in SQL commands, so it is difficult to use ASP commands only to implement fuzzy queries on the database. In the programming practice, using the Connection object of ADO component, the Recordset object dynamically creates and runs the temporary stored procedure object of the database, not only realizes the fuzzy query, but also has the very good interactivity.
----1. Create a user interaction page input.html
< HTML >
< head >
< title > User Input Interface </title >< BODY >
< Center >< Font face= "Official script" Size=+3 >
Database Query </font ></center >
< form method= "POST" action= "query.asp" >
By field < select Name= "Leixing" >
< option value= "name" > name </option >
< option value= "sex" > Sex </option >
</select > Query < br >
Keywords < input type= "text" name= "keyword" size=40 >< br >
< input type= "submit" value= "Query" >
< input type= "reset" value= "clear" >
</form >
</body >
----When the user enters data on this client page, the data is passed by the "POST" method to the Query.asp page on the server side through the form data collection, and the query function is completed by query.asp.
----2. Create query.asp file, implement fuzzy query
< HTML >
< head >
< title ></title >
< BODY >
<%
Response.Write "< Center >< font face=" "Official script"
"Size=+2 > Query Results </font ></center >"
DIM QUCOL,STR,STR5
'--Get form data--------
Qucol=request.form ("leixing")
Str=request.form ("keyword")
'--------generate WHERE clause--
str5=qucol& "like" & "" "%" &str& "%" ""
' Establish a database connection. Carlos: Data source name; SA:
User name; pubs: Database name
Setconn= Server.CreateObject ("ADODB"). Connection ")
Conn.Open "Dsn=carlos; Uid=sa; pwd=;D Atabase=pubs "
For I=0 to Conn.errors.count-1
Response.Write Conn.errors (I) description& "< br >"
Next
'---create temporary stored procedures----------
commandtext= "CREATE PROCEDURE
#query "&session (" num ") &_
"AS SELECT * from authors Where" &STR5
Conn.execute commandtext,0,-1
'--Create a Recordset object--
Set rs=server.createobject ("ADODB. RecordSet ")
'--run temporary stored procedures--------
Const adopendynamic=2
Const adlockoptimistic=3
Const ADCOMDSTOREDPROC=8
commandtext= "#query" &session ("num")
Session ("Num") =session ("num") +1
Rs. Open Commandtext,conn,adopen
Dynamic,adlockoptimistic,_
Adcomdstoredproc
'---------Display query results
Response.Write "< table border=" "1" ">"
Response.Write "< tr >"
For I=0 to Rs.fields.count-1
Response.Write "< td >" &rs (I). name& "</td >"
Next
Response.Write "</tr >"
While not Rs. Eof
Response.Write "< tr >"
For I=0 to Rs.fields.count-1
Css=rs (I). Value
If css< > "" Then
Response.Write "< td >" &css& "</td >"
Else
Response.Write "< td >.</td >"
End If
Next
Rs.movenext
Response.Write "</tr >"
Wend
Response.Write "</table >"
Rs.close
Set rs=nothing
Set conn=nothing
% >
<!--query end, return to input interface, start next query-->
< a href= "input.html" > Back to Query </a >
</body >
----The temporary stored procedure here is named #query1 (#query2,# query3,......), each time with a different name to avoid creating a stored procedure with the same name each time the query.asp is executed. Since we create a temporary stored procedure, the stored procedures are automatically deleted when the user query finishes exiting, without any impact to the database.
