How to use ASP to invoke views and stored procedures for SQL databases

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.

The following are the referenced contents: <% Set DataConn = Server.CreateObject ("ADODB. Connection ")" to establish 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") ' Creates a Command object Set rst= Server.CreateObject ("ADODB.") Recordset ")" To set up Recordset objects Cmdtemp.commandtext = "Customers" Cmdtemp.commandtype = 2 Set cmdtemp.activeconnection = dataconn. National Webmaster Station Rst. Open Cmdtemp,, 1, 3 ' Generate query Results %>

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 the database stored procedure

The following are the referenced contents: <% Set DataConn = Server.CreateObject ("ADODB. Connection ")" to establish 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") ' Creates a Command object Set rst = Server.CreateObject ("ADODB.") Recordset ")" To set up Recordset objects 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) www^chinaz^com CmdTemp.Parameters.Append Tmpparam Set Tmpparam = Cmdtemp.createparameter ("@BeginDate", 135, 1, Riqi) ' Create 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:

The following are the referenced contents: <% Set DataConn = Server.CreateObject ("ADODB. Connection ")" to establish 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 ASP and ADO usage, see the detailed references for Microsoft ActiveX Data Objects (ADO) and active Server Pages (ASP).

Five, concluding remarks in the development of B/s structure, We can write some business rules or complex queries with stored procedures in the DBMS, and then use the ADO object in ASP to call, complete the original C/s structure of the traditional functions.