ASP invokes SQL Server views and stored procedures

First, the preface

ASP (Active server Pages) is a server-side scripting environment that is supported by Microsoft's IIS3.0 version. It can be used to create dynamic Web pages or build powerful Web applications. ASP pages are files that include HTML markup, text, and script commands. ASP pages can invoke ActiveX components to perform tasks, such as connecting to a database or doing business calculations. With ASP, you can add interactivity to your Web pages or form an entire Web application with HTML pages that use HTML pages as the interface for your customers.

Two, ASP model

When the browser requests an. asp file from the Web server, the ASP script starts running. The Web server then invokes asp,asp to fully read the requested file, execute all script commands, and route 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 a browser request, respond to browsers, and store user information. Includes application, Request, Response, Server, Session, and ObjectContext objects. The most commonly used are requests, Response, and server three objects, 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.

Third, ADO

ASP and backend database connections using Microsoft's ADO (ActiveX Data Objects), ADO is an easy-to-use and extensible technique for adding database access to a Web page. 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, respectively, connection, Command, RecordSet, Fields, Error, parameters, and properties. Through these objects, the ASP can complete all operations on the background database.

Iv. ASP call views and stored procedures

In the general MIS application, there will be a large number of reports, at this time we can write in the background database of the corresponding view or stored procedures, with ASP through ADO call to complete the report work. Here is an example to illustrate the appropriate procedure.

1. Create an ODBC DSN file

Before you create a database script, you must provide a way to locate, identify, and communicate with the database. The database driver uses data Source Name (DSN) to locate and identify specific ODBC-compliant databases and pass information from the WEB application to the database.

2. Call Database View

The first step in accessing database information is to establish a connection with the database source. ADO provides a connection object that you can use to establish and manage connections between applications and ODBC databases.<%
Set Dataconn = Server.CreateObject("ADODB.Connection") '建立连接对象
　　 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") '建立命令对象
　　 Set rst= Server.CreateObject("ADODB.Recordset") '建立记录集对象
　　 cmdTemp.CommandText = "Customers "
　　 cmdTemp.CommandType = 2
　　 Set cmdTemp.ActiveConnection = DataConn
　　 rst.Open cmdTemp, , 1, 3 '生成查询结果
%>

At this point, customers is the view, and querying the data from the view is the same as querying the data from the base table.

3. Call database stored procedure <%
Set dataconn = Server.CreateObject ("ADODB. Connection ") ' establishes the 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 ") ' Establish Command object
Set rst = Server.CreateObject (' ADODB. Recordset ")" Set the Recordset object
Cmdtemp.commandtext = "dbo.pd_test" ' Stored procedure name
Cmdtemp.commandtype = 4 ' command category is 4, represented as 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, Riqi)
' Creates an 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 the standard method provided in ADO, but there is a problem that when there are more than two SELECT statements in the stored procedure, but it is logically impossible to execute concurrently, ADO prompts you that there are too many SELECT statements in the stored procedure. The workaround is to execute the stored procedure directly using the Execute method of the ADO Connection object, as follows:<%
Set Dataconn = Server.CreateObject("ADODB.Connection") '建立连接对象
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 ASP and ADO usage, see the detailed references for Microsoft ActiveX Data Objects (ADO) and active Server Pages (ASP).

V. Concluding remarks

In the development of B/s structure, we can write some commercial rules or complex queries with stored procedures in DBMS, then use the ADO object in ASP to make the traditional function in C/s structure.