Ways to use ASP stored procedures

The main use of the Microsoft SQL Server7.0 database, the first to establish a connection file adosql7.asp standby, in the future when the time is not specifically described.

Use Command object and parameter object to pass parameters

The main use of the Microsoft SQL Server7.0 database, the first to establish a connection file adosql7.asp standby, in the future when the time is not specifically described.

<% ' adosql7.asp

Option Explicit

Response.Expires = 0



"The first part: establish the connection

Dim Cnn, strcnn

Set Cnn = Server.CreateObject ("ADODB.") Connection ")

STRCNN = "PROVIDER=SQLOLEDB; User Id=sa; password=; Initial catalog=pubs; Data SOURCE=ICBCZJP "

Cnn.open strcnn

%>

Note: When you use it, set the data source to the name of the machine where your database server is located.

In addition, when using an Access database, you can easily view fields and data with Microsoft ACCESS97, while using a SQL Server database, especially if you are debugging ASP scripts on another machine instead of the database server, To see the fields and data you need to install additional tools, here is a tool for you: Msqry32.exe (Microsoft Query), which is installed with OFFICE97, typically under the directory "Microsoft Office\Office".

Example wuf70.asp:

<%@ language= "VBSCRIPT"%>

<!--#include file= "adosql7.asp"-->

<!--#include file= "Adovbs.inc"-->

<% ' wuf70.asp

Dim cmdtest, Prmtest, rstest

' Create Command Object

Set cmdtest = Server.CreateObject ("Adodb.command")

' Recordset, Command object can connect connection object by ActiveConnection property

Cmdtest.activeconnection = Cnn

' SQL command-contains two parameters, with? Said

Cmdtest.commandtext = "Update jobs Set Job_desc =?" Where job_id =? "

' Set command type to SQL statement

Cmdtest.commandtype = adCmdText

The ' Prepared property determines whether the SQL command is precompiled, set to true, and can be run faster

cmdtest.prepared = True



' Create Parameter Object

Set prmtest=cmdtest.createparameter ("Job_desc", advarchar,adparaminput,50, "network")

"Append data to the Parameters data collection

CmdTest.Parameters.Append Prmtest



Set prmtest = Cmdtest.createparameter ("job_id", Adsmallint,adparaminput,, "12")

CmdTest.Parameters.Append Prmtest



' Perform the modification-no need to return the result, simply use the Cmdtest.execute.

Cmdtest.execute



' Reset the parameter run-you can modify another piece of data

Cmdtest.parameters ("job_id") = "1"

Cmdtest.parameters ("job_desc") = "Test"

Cmdtest.execute



' Reset parameter to run

Cmdtest ("job_id") = "14"

Cmdtest ("job_desc") = "Finance"

Cmdtest.execute



Set rstest = Cnn.execute ("Select Job_id,job_desc from Jobs")

While not rstest.eof

Response.Write rstest (0) & Rstest (1) & "<br>"

Rstest.movenext

Wend



Cnn.close:Set prmtest = Nothing

Set cmdtest = Nothing:set Cnn = Nothing

%>

Analysis:

1. The CreateParameter method of the Command object is used to establish a parameter object for the SQL command or stored procedure, with five parameters (five arguments are optional):

First parameter: The name of the parameter object;

The second parameter: Parameter object's data type, the kind is too many, still refer to ADO help, here adVarChar (string value), adSmallInt (2 byte signed integer);

Third parameter: parameter type. Can be: adParamInput (indicates input parameter), adParamOutput (indicated as output parameter), adParamReturnValue (indicated as return value), Adparamunknown (indicates argument type cannot be determined), adParamInputOutput (indicated as input/output parameters);

Fourth parameter: The data length of the parameter, preferably specified to be equal to the corresponding field length in the database to avoid error when using, especially when the data type is varchar, if it is an integral type or date type, it is not necessary to supply the value;

Fifth parameter: The setting initial value of the parameter.

2. The CmdTest.Parameters.Append method adds a Parameter object to the Parameters data collection, and you can see how to use multiple parameters in this example.

3. From this example, we can see that only need to reset the input parameters, you can perform changes to other data, very convenient, this idea in programming is also one of the most commonly used methods.

4. Reset parameters can either be cmdtest.parameters or omitted as Cmdtest ("job_id").



Second, the use of stored procedures in ASP

What is a stored procedure (stored procedure in a database server, a collection of SQL statements that can contain one or more SQL statements), and how to create a stored procedure that is not part of this lecture is an example of how to invoke a stored procedure in ASP.

The benefits of using stored procedures are significant, and stored procedures are more efficient than running SQL commands in ASP scripts, improving overall performance and easing network load (reducing interaction between network servers and data servers), optimizing ASP code and enhancing code flexibility, and more.

(i) Use of input parameters in stored procedures

The stored procedure used in this example is the "byroyalty" of SQL Server7.0, where an SQL statement is simple, with one more create PROCEDURE byroyalty, and one input parameter is @percentage:

CREATE PROCEDURE byroyalty @percentage int

As

Select au_id from titleauthor

where Titleauthor.royaltyper = @percentage

Serve by www.cidu.net

Case wuf71.asp