Use "Stored Procedure" in access [Post]

From: http://goaler.xicp.net/ShowLog.asp? Id = 499
We are already familiar with using ASP to call SQL Server Stored Procedures to perform database operations. But do you know that in table-level database access, we can also create and use stored procedures "?

Access + ASP is an excellent combination of lightweight Web applications: simple, fast, and compatible, but the performance is usually not high. In addition, ADODB is used. connection and recordset objects are also inconvenient to execute SQL statements, because the parameter values of SQL statements with parameters are often spliced into strings, as a result, there is such a problem as "single quotes. One advantage of using stored procedures is that it supports SQL statement parameter values.

In fact, the so-called "Stored Procedure" in access (2000 and later versions) is incomparable with the stored procedure in SQL Server. It can only be regarded as "Stored Procedure Lite", does not support multiple SQL statements, does not support logical statements (huh, after all, not a T-SQL) and so on, I do not know whether it is pre-compiled. However, just as the so-called "class" implemented by VBScript is only encapsulation, it can greatly promote the "beautification" of the code structure and the reusability of the program. The "lightweight stored procedure" of access ", for specifications, database operations with a low chance of errors should also be helpful, and the performance may be improved.
Next I will translate the step by step method to introduce how to create a stored procedure in access and then use it in ASP programs.

(1) create a "Stored Procedure" in access"

I don't know what the access application level is. For me, it is just a tool for creating mdb database files. I will only create MDB files, create tables, indexes, and constraints on the access interface ~
In access, "query" assumes the role of a stored procedure. The "Stored Procedure" or "query" of access mentioned below refer to this item.
For the creation of "query", Access provides a dumb tool, similar to the wizard used to create a dataadapter in vs. net. However, I like to write SQL code directly.
Well, let's take a look at the table structure of the database used in this simple example.

Click "query" on the left of the access interface, and then double-click "create query in design view" on the right to open the query design view.

In this case, a visual query generator is displayed. We first add the tables to be involved in the SQL statement.

After adding a table, right-click the design view and select "SQL View" to switch to the SQL code editing window.

Okay. Let's talk about the features of the access stored procedure.

Access query, I feel like it is a packaging of SQL statements, maybe some optimization, such as pre-compilation. We cannot use multiple operations, transactions, logical judgment, loops, etc. like writing SQL server stored procedures ......
However, the main purpose of using the access stored procedure is to use the extra query provided by parameters. Using the stored procedure, we do not have to face any troubles when splicing the parameter values into SQL statement strings, for example:

Code:
Dim SQL
SQL = "select * from users where username = '" & username &"'"
In the preceding Code, if the string username contains single quotes ('), an error is returned. We must manually convert:

Code:
Dim SQL
SQL = "select * from users where username = '" & replace (username, "'", "'' ") &" '"' is converted to two consecutive single quotes
When using a query with parameters, our SQL statement can be written as follows:

Code:
Dim SQL
SQL = "select * from users where username = @ username"
Then, pass the value of @ username in the parameter attribute of the command object, which is convenient and intuitive.

Code:
With cmd
'Create a parameter object
. Parameters. append. createparameter ("@ username ")
'Specify values for each parameter
. Parameters ("@ username") = Username
End

The usage of parameters in the access stored procedure is also described here. Unlike the SQL Server Stored Procedure, the @ variable is used to specify parameters, and the input parameter objects with the same name are different. Parameters in access are identified by "order" rather than "name. You do not need to specify the name of the input parameter. You can specify the name of the parameter in the SQL statement as long as the parameter value is entered. Generally, we use the execute method of the command object to directly input an array of parameter values for execution ~

Code:
Cmd. Execute, array (username)
Another example is to write an access stored procedure as follows:

Code:
Select * from users where username = p_username and booktitle = p_booktitle
You can do this by passing in an array of parameter values, but the order must correspond:

Code:
Cmd. Execute, array (username, booktitle)

OK. Let's take a look at the two queries used in our example. One is to write data. Write the SQL statement, save it, and name it.

Another Stored Procedure Code for reading data.

(2) Use stored procedures
Then we can call these stored procedures in the ASP program.
Here we can see why the query in access is its stored procedure -- The commandtype attribute of our command object is set to 4, that is, stored proc! So...

<% Option explicit Dim s Randomize S = RND * 100 Dim Conn, cmd Set conn = server. Createobject ("ADODB. Connection ") Set cmd = server. Createobject ("ADODB. Command ") Conn. Open "provider = Microsoft. Jet. oledb.4.0; Data Source =" & server. mappath ("sp. mdb ") With cmd . Activeconnection = Conn . Commandtype = & h0004' Stored Procedure . Commandtext = "addnewdata" End Cmd. Execute, array (CSTR (now (), csng (s )) With cmd . Activeconnection = Conn . Commandtype = & h0004' Stored Procedure . Commandtext = "getdata" End Dim resultrs, resultarray Set resultrs = cmd. Execute (, null) If not resultrs. EOF then Resultarray = resultrs. getrows () End if Set resultrs = nothing Set cmd = nothing Conn. Close Set conn = nothing Response. Write "<ul>" Dim I For I = 0 to ubound (resultarray, 2) Response. Write "<li>" & resultarray (0, I) Response. Write "" & resultarray (1, I) Response. Write "" & resultarray (2, I) Response. Write "</LI>" Next Response. Write "</ul>" %>

Running result: