ASP technology access web database

ASP technology access web database

I. Various Solutions for accessing Web databases

Currently, there are a variety of Web database access technologies in windows, mainly including:

1. Common Gateway Interface (CGI)

CGI is a technology implemented earlier. It is applicable to multiple server platforms, such as UNIX and windows. However, CGI features high development costs, difficult maintenance, limited functions, no transaction processing functions, and a large amount of server resources.

2. Internet Database Connector (IDC)

IDC is integrated into ISAPI (Internet Server API), which makes full use of DLL technology and is easy to expand. However, programming is more complex than CGI and is only applicable to small database systems.

3. Advance Database Connector)

The ADC provides ActiveX Control to access the database. Its main feature is that data query is executed by the client browser. Therefore, you need to download some records from the server database to the client, large System overhead and slow response, applicable only to extremely frequent database query operations.

4. Java/jdbc Programming

Java is an object-oriented, portable, and multi-threaded language that can be connected to a database through JDBC. The software written in Java/JDBC is highly portable and suitable for multiple operating systems. However, the execution efficiency and speed are not ideal. At present, it is impossible to establish efficient and high-speed applications.

5. Dynamic Server Page ASP (Active Server Page)

ASP is the latest Web Application Development Technology released by Microsoft. It focuses on the development of dynamic web pages and web databases. It features flexible and concise programming and high performance, it is the best choice for accessing Web databases.

II. Introduction to ASP

1. Principles of ASP database access

ASP is the script execution environment on the server. It can be used to generate and execute dynamic and high-performance Web servers. Program .

When a user requests an ASP homepage through a browser, the web server responds, calls the ASP engine to execute ASP files, explains the script language (jscript or VBScript), and connects to the database through ODBC, the database access component ADO (ActiveX Data Objects) completes database operations. Finally, ASP generates an HTML homepage containing data query results and returns it to the user end.

Because ASP runs on the server and the running result is returned to the user's browser in the form of an HTML homepage, the ASP source code will not be leaked, increasing the security and confidentiality of the system. In addition, ASP is an object-oriented scripting environment. Users can add ActiveX components to expand their functions and expand their application scope.

2. ASP page structure:

ASP program Code Simple and common. The file name ends with. asp. The ASP file is usually composed of four parts:

1) standard HTML Tag: All HTML tags can be used.

2) Asp syntax command: ASP code in the <%> label.

3) Include statement on the server: The # include statement can be used to call other ASP code, enhancing programming flexibility.

4) scripting language: ASP comes with two scripting languages: JScript and VBScript. The ASP programming function is added. You can also install other scripting languages, such as Perl and Rexx.

3. asp Runtime Environment

Currently, ASP can run in three environments.

1) run IIS 4.0 (Internet Information Server) or above on Windows NT Server 3.0.

2) run peer Web Server 4.0 or above in Windows NT Workstation 3.0.

3) Run PWS (Personal Web Server) in Windows 95/98 ).

Among them, the IIS on the NT Server is the most powerful and provides comprehensive support for ASP, which is the best choice for creating a high-speed and stable ASP homepage.

4. asp built-in objects

ASP provides six built-in objects for users to directly call:

1) application object: manages all session information and can be used to share information among all users of a specified application.

2) Session Object: stores the session information of a specific user and is only accessed by this user. When a user jumps to a different web page, the variables in the session are stored throughout the user's entire session. Cookie support is required for session objects.

3) request object: retrieving information from the user end and passing it to the server is the main way ASP reads user input.

4) response object: the server sends the output content to the client.

5) Server Object: provides access to server-related methods and attributes.

6) object context object: an object added to IIS 4.0 for transaction processing. This feature is supported by the MTS (Microsoft transcation server) management.

5. Main built-in components of ASP:

1) AD rotator component: used to automatically rotate and display advertisements on the same page according to the specified plan, and used for the increasingly important advertising services on www.

2) browser capabilities component: determines the functional data of the user's browser accessing the web site, including the type, performance, and version.

3) database access component: Provides ADO (ActiveX Data Objects) to access databases that support ODBC.

4) file access component: provides the ability to read and write files on the server.

5) content linking component: generate the content list of the web page and connect the pages in sequence to create a navigation bar.

In addition, you can install components such as myinfo, counters, content rotator, and page count. You can also compile your own actiive components to improve the practicability of the system.

6. database access component ADO

An important application on WWW accesses a web database. when accessing a Web Database Using ASP, the ADO component must be used. ADO is an ActiveX Server component built in ASP ), you can set ODBC and oledb on the Web server to connect to multiple databases, such as Sybase, Oracle, Informix, SQL Server, access, and VFP, it is the most effective, simple, and direct method to operate the databases currently supported by Microsoft.

The ADO component provides the following seven objects and four sets to access the database.

1) connection object: establish a connection with the background database.

2) command object: Execute SQL commands and access the database.

3) parameters object and parameters set: provides data and parameters for the command object.

4) recordset object: stores the data information after accessing the database, which is the most frequently used object.

5) Field object and field set: provides the ability to access the fields of the current record in recordset.

6) property object and properties set: provides relevant information for use by connection, command, recordset, and field objects.

7) Error object and errors collection: Provides error information when accessing the database.

3. ASP database access steps

To access the background database using the ADO component in ASP, follow these steps:

1. Define the data source

Open "Control Panel" on the Web server, select "ODBC", select "add" under "system DSN", and select the desired database type, name, and location. This document defines "SQL Server", the data source is "ht", the database name is "htdata", and the script language uses JScript.

2. Use the ADO component to query web databases

1) call server. Createobject to obtain the "ADODB. Connection" instance, and then open the database using the open method:

Conn = server. Createobject ("ADODB. Connection ")

Conn. Open ("HT ")

2) Specify the SQL command to be executed

After connecting to the database, you can perform database operations, such as query, modification, and deletion, through SQL commands, to query records whose code contains "X" in the signaltab of the data table

Sqlstr = "select * From signaltab where code like '% x % '"

Rs = conn. Execute (sqlstr)

3) use the recordset attribute and method and display the result.

To track data more accurately, use the recordset component to create a cursor containing data, which is the data stored in the memory.

Rs = server. Createobject ("ADODB. recordset ")

Rs. Open (sqlstr, Conn, 1,)

Note: A = 1 read

A = 3 add, modify, and delete

Common attributes and methods in the recordset component include:

Rs. Fields. Count: number of fields in the recordset object.

RS (I). Name: name of the I-th field, where I is 0 to Rs. Fields. Count-1

RS (I): The data of field I, where I is 0 to Rs. Fields. Count-1

RS ("field name"): Specifies the data of a field.

Rs. Record. Count: Total number of data records in the cursor.

Rs. EOF: indicates whether the last record is used.

Rs. movefirst: point to the first record.

Rs. movelast: point to the last record.

Rs. moveprev: point to the previous record.

Rs. movenext: point to the next record.

Rs. getrows: puts data into the array.

Rs. properties. Count: the number of attributes of the resultset or connection of ADO.

Rs. properties (item). Name: name of the resultset or connection of ADO.

Rs. properties: The resultset or connection value of ADO.

Rs. Close (): Close the connection.

4) shut down the database

Conn. Close ()

Iv. Web database query example

The following sample program accesses the signaltab table of the SQL Server database. The table has three fields: Code (Code field, character type, 3-bit), Class (classification field, character type, 10 bits), memo (remarks field, character type, 20 bits ). Program Data Source DSN: Ht, User name: client, password: passwd.

Input. asp

<% @ Language = JavaScript %>

<HTML>

<Head>

<Title> enter the query condition please input condition </title>

</Head>

<Form action = shotquery. asp method = "Post" target = "_ Self">

<Input type = "text" size = 6 maxlength = 3 name = signalcode value = "code"> </fo

NT> </P>

</Center> </div> <p> </P>

<Input language = "JavaScript" type = "Submit" value = "OK" name = "B1"



<Input type = "reset" value = "clear" name = "B2">



<Input language = "JavaScript" type = "button" name = "B3" value = "return"

Onclick = "window. History. Back ()">

</Font> </P>

</Center> </div>

</Form>

</Body>

</Html>

Database processing program 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 matching record </P> ");

Response. Write ("<p> <form> <input type = 'button 'value =' Return 'oncli

Ck = 'window. History. Back () 'name = 'b1 '> </form> </P> ");

}

Else {

Response. Write ("<p> <center> <B> Data Query Result </B> </center> </P> ");

Response. write ("<tr> <TD> <B> Code </B> </TD> <B> Category </B> </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> </T

D> ");

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>