ASP Technology Access Web database

web| Access | data | database

I. Multiple scenarios for accessing a Web database
Currently, there are several technologies for accessing Web databases in the Windows environment, including:
1. Public Gateway Interface CGI (Common gateway Interface)
CGI is a technology that is implemented earlier. Applicable to a variety of server platforms, such as UNIX, Windows, but CGI development costs, maintenance difficulties, limited functionality, do not have transaction processing functions, occupy more server resources.
2. Internet DB connector IDC (Internet database Connector)
IDC is integrated in the ISAPI (Internet Server API), leverages DLL technology and is easy to expand, but programming is more complex than CGI and applies only to small database systems.
3. Advanced Database Connector ADC (Advance DB Connector)
ADC provides ActiveX control to access the database, its main feature is that the data query is executed by the client browser, so it is necessary to download some of the records in the server-side database to the client, the system is expensive, slow response, only for special frequent database query operations.
4. JAVA/JDBC Language Programming
The Java language is an object-oriented, portable, multithreaded language that can be used to connect to a database through JDBC. The software, which is written by JAVA/JDBC, is suitable for many operating systems, but its execution efficiency and execution speed are not satisfactory, so it is unable to establish high efficiency and high speed application at present.
5. Dynamic Server Pages ASP (Active Server Page)
ASP is Microsoft's latest Web application development technology, focusing on the processing of dynamic Web pages and Web database development, programming flexible, concise, with high performance, is currently the best choice to access the Web database.
Two. asp Introduction
1.ASP access to the database principle
ASP is a server-side script execution environment that can be used to generate and execute dynamic, high-performance Web server programs.
When a user requests an ASP home page using a browser, the Web server responds, invokes the ASP engine to execute the ASP file, interprets the scripting language (JScript or VBScript), connects the database through ODBC, and the database accesses the component ADO (ActiveX Data Objects) completes the database operation, and finally the ASP generates the HTML home page that contains the results of the data query to return the client display.
Because the ASP is running on the server side, the results are returned to the user's browser in the HTML homepage, thus the ASP source program will not leak and increase the security confidentiality of the system. In addition, ASP is an object-oriented scripting environment, users can add their own ActiveX components to expand their capabilities, expand the scope of application.
2.ASP the structure of the page:
ASP's program code is simple, generic, file name from. asp end, ASP file is usually composed of four parts:
1 Standard HTML Tags: all HTML tags can be used.
2 ASP Syntax command: The ASP code located in the <%%> tag.
3 server-side include statement: The #include statement can be transferred into other ASP code, enhance the flexibility of programming.
4 scripting language: ASP with JScript and VBScript two scripting languages, adding ASP programming capabilities, users can also install other scripting languages, such as Perl, Rexx and so on.
3.ASP Operating Environment
At present, the ASP can run in three kinds of environment.
1 WINDOWS NT Server 4.0 runs IIS 3.0 (Internet Information Server) above.
2 WINDOWS NT Workstation 4.0 runs peer Web Server 3.0.
3) WINDOWS 95/98 runs PWS (Personal Web Server).
With the strongest IIS capabilities on NT Server, full support for ASPs is the best choice for creating a high-speed, stable ASP home page.
4.ASP of built-in objects
ASP provides six Builtin objects for direct user invocation:
1 Application object: Manages all session information and is used to share information among all users of the specified application.
2 Session object: Store a specific user's conversation information, only to be accessed by the user, when the user in different Web page jumps, the session of the variable in the user throughout the conversation process has been saved. The Session object requires cookie support.
3 Request object: Obtain the information from the client to the server, is the ASP reads the user input the main method.
4 Response object: The server sends output to the client.
5 Server object: Provides access to server-related methods and properties.
6 Object Context object: IIS 4.0 new objects that are used for transaction processing. This feature needs to be supported by MTS (Microsoft transcation Server) management.
5. ASP's main built-in components:
1) Ad Rotator component: Used to automatically rotate and display advertisements on the same page according to the specified schedule for increasingly important advertising services on www.
2) Browser Capabilities component: Determines the functional data of the user's browser that accesses the Web site, including type, performance, version, and so on.
3 Database Access component: Provides ADO (ActiveX Data Objects) to access ODBC-enabled databases.
4 File Access Component: Provides read/write functionality to server-side files.
5 Content Linking component: Generates a list of Web page contents and connects each page sequentially to make a navigation bar.
In addition, can also install MyInfo, counters, Content rotator, Page count and other components, users can also prepare their own actiive components to improve the practicality of the system.
6. Database Access Component ADO
A very important application on WWW is to access the Web database, when using ASP to access the Web database, you must use the ADO component, ADO is the ASP built-in ActiveX server component (ActiveX server Component), By setting up ODBC and OLE DB on a Web server, you can connect to multiple databases, such as Sybase, ORACLE, INFORMIX, SQL SERVER, ACCESS, VFP, and so on, as the most efficient and straightforward way to operate a database currently supported by Microsoft.
The ADO component mainly provides the following seven objects and four collections to access the database.
1 Connection object: Establish a connection with the background database.
2 Command object: Execute SQL instruction, Access database.
3 parameters object and Parameters collection: Provides data and parameters for the Command object.
4 Recordset object: Storing data information after accessing the database is the most frequently used object.
5 Field object and field collection: Provides the ability to access individual fields of the current record in the recordset.
6 Property objects and Properties collection: Provides information for use by connection, Command, RecordSet, and Field objects.
7 the Error object and the Errors collection: Provides information about errors when accessing the database.
Three. ASP Access database steps
In ASP, you can use the ADO component to access the background database by following these steps:
1. Defining a data source
On the Web server, open Control Panel, check ODBC, select Add under System DSN, select the type of database you want, name, location, and so on. This article defines "SQL SERVER", the data source is "HT", the database name is "Htdata", and the scripting language uses JScript.
2, query the Web database using the ADO component
1) Call Server.CreateObject method to obtain "ADODB." Connection, and then open the database using the Open method:
conn = Server.CreateObject ("ADODB"). Connection ")
Conn. Open ("HT")
2 specifies the SQL command to execute
When you connect to a database, you can manipulate database operations, such as queries, modifications, deletes, and so on, which are done through SQL directives, such as querying the code in the datasheet Signaltab for records containing "X"
SQLSTR = "SELECT * from Signaltab where code like '%X% '"
RS = conn. Execute (SQLSTR)
3 use Recordset properties and methods, and display the results
To more accurately track data, you create a cursor that contains data using the Recordset component, which is the data stored in memory.
rs = Server.CreateObject ("ADODB.") RecordSet ")
Rs. Open (Sqlstr,conn,1,a)
Note: a=1 read
A=3 Add, modify, delete
In the recordset component, the common properties and methods are:
Rs. The number of fields for the Fields.Count:RecordSet object.
RS (i). Name: The names of the I fields, I are 0 to Rs. Fields.count-1
RS (i): Data in the I field, I is 0 to Rs. Fields.count-1
RS ("field name"): Specifies the data for the field.
Rs. Record.count: The total number of data records in the cursor.
Rs. EOF: Is the last record.
Rs. MoveFirst: Point to the first record.
Rs. MoveLast: Point to last record.
Rs. MovePrev: Point to previous record.
Rs. MoveNext: Point to next record.
Rs. GetRows: Put the data in an array.
Rs. The number of Properties.Count:ADO resultset or connection properties.
Rs. Properties (item). The name of the ResultSet or connection of the Name:ado.
Rs. The value of the ResultSet or connection of the Properties:ado.
Rs.close (): Closes the connection.
4) Close the database
Conn.close ()
Four. Query Web database examples
The following sample program is a Signaltab table that accesses a SQL Server database with three fields: Code (field, character, 3-bit), Class (category field, character, 10-bit), Memo (Memo field, character type, 20-bit). Program in the data source Dsn:ht, user name: client, Password: passwd.
Screen Input page input.asp
<% @ language=javascript%>
<title> Please enter the query terms, input condition</title>
<form action=shotquery.asp method= "POST" target= "_self" >
<input type= "text" size=6 maxlength=3 name=signalcode value= "code" > </font></p>
</center></div><p></p>
<input language= "JavaScript" type= "Submit" value= "confirm" name= "B1"
　　
<input type= "Reset" value= "clear" name= "B2" >
　　
<input language= "JavaScript" type= "button" Name= "B3" value= "return"
>
</font></p>
</center></div>
</form>
</body>
Database Handler shotquery.asp
<!--#include virtual= "/master/lib.inc"-->
<script Language=javascript runat=server>
var signalcode=request.form ("Signalcode");
Dbconn = Server.CreateObject ("ADODB"). Connection ");
Dbconn.open ("DSN=HT; Uid=client; PWD=PASSWD ");
rs = Server.CreateObject ("ADODB.") RecordSet ");
var sqlstr = "SELECT * from Signaltab Order by code";
Rs.Open (sqlstr,dbconn,1);
if (Rs. RecordCount < 1) {
Response.Write ("<p> no Qualifying Records </p>");
Response.Write ("<p><form> <input type= ' button ' value= ' return ' ></form></p>");
}
else {
Response.Write ("<P><center><B> data query Results </B></center></P>");
Response.Write ("<tr><td><b> Code </b></td><td><b> Classification </b></td> <td><b> remarks </b></td> ");
for (var i = 1;i <= Rs. pagesize;i++) {
if (!rs. EOF) {
Response.Write ("<td><span style= ' font-size:9t ' >" +rs ("code") + "</span></td>");
Response.Write ("<td><span style= ' font-size:9t ' >" +rs ("class") + "</span></td>");
Response.Write ("<td><span style= ' font-size:9t ' >" +rs ("Memo") + "</span></td>");
Response.Write ("</tr>");
Rs. MoveNext ();
}
else break;
}
Response.Write ("</table></form>");
Rs.close ();
Dbconn.close ();
}
</script>