1. Preface
ASP (Active Server Pages) is a server-side scripting environment, which is supported by Microsoft's IIS3.0 or above. It can be used to create dynamic Web pages or generate powerful Web applications. ASP pages are files that include HTML tags, text, and script commands. ASP pages can call ActiveX components to perform tasks, such as connecting to a database or performing business calculations. With ASP, you can add interactive content to your Web pages or use HTML pages to compose entire Web applications. These applications use HTML pages as the interface to your customers.
2.
When the ASP model browser requests the .asp file from the Web server, the ASP script starts running. The Web server then calls ASP, which fully reads the requested file, executes all script commands, and transmits the Web page to the browser.
ASP provides a framework for using existing scripting languages such as Microsoft VBScript and Microsoft JScript in HTML pages.
ASP provides built-in objects that make it easier for users to collect information sent through browser requests, respond to browsers, and store user information. Includes Application, Request, Response, Server, Session and ObjectContext objects. The most commonly used objects are Request, Response and Server, which are used to request information from the browser, send information to the browser and access the properties and methods of objects on the server.
3. ADO
ASP and background database connection use Microsoft's ADO (ActiveX Data Objects). ADO is an easy-to-use and scalable technology that adds database access to Web pages. You can use ADO to write compact and concise scripts to connect to Open Database Connectivity (ODBC)-compliant databases and OLE DB-compliant data sources.
ADO contains 7 built-in objects, namely Connection, Command, RecordSet, Fields, Error, Parameters and Properties. Through these objects, ASP can complete all operations on the background database.
4. ASP calls views and stored procedures
. In general MIS applications, there will be a large number of reports. At this time, we can write corresponding views or stored procedures in the background database, and use ASP to call them through ADO to complete the report work. An example is used below to illustrate the corresponding operation process.
1. Create an ODBC DSN file.
Before creating a database script, you must provide a way for ADO to locate, identify and communicate with the database. Database drivers use a Data Source Name (DSN) to locate and identify a specific ODBC-compliant database to pass information from the Web application to the database.
2.
The first step to call the database view to access database information is to establish a connection with the database source. ADO provides a Connection object, which can be used to establish and manage connections between applications and ODBC databases.
<%
Set Dataconn = Server.CreateObject("ADODB.Connection") 'Create a connection object
Dataconn.Open "DSN=SinoTrans;SERVER=APP_SERVER;
UID=sa;PWD=;APP=Microsoft (R) Developer Studio;WSID=APP_SERVER;Regional=Yes"
Set cmdTemp = Server.CreateObject("ADODB.Command") 'Create a command object
Set rst= Server.CreateObject("ADODB.Recordset") 'Create a recordset object
cmdTemp.CommandText = "Customers"
cmdTemp.CommandType = 2
Set cmdTemp.ActiveConnection = DataConn
rst.Open cmdTemp, , 1, 3 'Generate query results
%>
At this time, Customers is a view, and the method of querying data from the view is the same as querying data from the base table.
3. Call the database stored procedure
<%
Set Dataconn = Server.CreateObject("ADODB.Connection") 'Create a connection object
Dataconn.Open "DSN=SinoTrans;SERVER=APP_SERVER;UID=sa;
PWD=;APP=Microsoft (R) Developer Studio;WSID=APP_SERVER;Regional=Yes"
Set cmdTemp = Server.CreateObject("ADODB.Command") 'Create a command object
Set rst = Server.CreateObject("ADODB.Recordset") 'Create a recordset object
cmdTemp.CommandText = "dbo.pd_test" 'Stored procedure name
cmdTemp.CommandType = 4 'The command category is 4, expressed as a stored procedure
Set cmdTemp.ActiveConnection = Dataconn
Set tmpParam = cmdTemp.CreateParameter("Return Value", 3, 4, 4)
cmdTemp.Parameters.Append tmpParam
Set tmpParam = cmdTemp.CreateParameter("@BeginDate", 135, 1, 16, riqi)
'Create input parameter object
cmdTemp.Parameters.Append tmpParam
rst.Open cmdTemp, , 1, 3 'Generate query results
%>
The stored procedure called here is pd_test, which is a standard method provided in ADO. However, there is a problem. When there are more than two SELECT statements in the stored procedure, but it is logically impossible to execute them at the same time, ADO You will be prompted that there are too many SELECT statements in the stored procedure. The solution is to directly use the EXECUTE method of the ADO CONNECTION object to directly execute the stored procedure, as follows:
<%
Set Dataconn = Server.CreateObject("ADODB.Connection") 'Create a connection object
Dataconn.Open "DSN=SinoTrans;SERVER=APP_SERVER;UID=sa;PWD=;
APP=Microsoft (R) Developer Studio;WSID=APP_SERVER;Regional=Yes"
ss = "EXECUTE dbo.pd_test " & "'" & riqi1 & "'"
Set rs = dataconn.Execute(ss)
%>
For more information about using ASP and ADO, see Detailed Reference for Microsoft ActiveX Data Objects (ADO) and Active Server Pages (ASP).
5. Conclusion
In the development of the B/S structure, we can write some business rules or complex queries in the DBMS using stored procedures, and then use the ADO object in ASP to call them to complete the traditional functions in the original C/S structure.