We have been familiar with in the ASP to perform database operations by calling the SQL Server stored procedure, but you know that in the desktop database Access, we can also create and use the "stored procedure"? Access ASP is an excellent combination of lightweight web applications: simple, fast, compatible, but performance is usually not high. Also, use adoDb.connection and recordset objects to perform SQL statements, there are also some inconvenience, because the parameter value of the SQL statement with parameters is often spliced into the string, so there is such as "single quotes" Trouble. One advantage to use the stored procedure is to support additional provision of SQL statement parameters. In fact, the "stored procedure" in Access (2000 and above), and the Stored Procedure in SQL Server cannot be more than. It can only be "Stored Procedure Lite", do not support multiple SQL statements, do not support logical statements (huh, not t-sql after all), etc., I still don't know whether it is precompiled. However, as the so-called "class" implemented by VBScript is only encapsulated, the "beautification" and program reuse of the code structure have great promotion, and the "lightweight stored procedure" of Access, for the specification, small error chances of databases Operation should also help, and performance may increase. Here are the way you translate STEP BY Step, describe how to create a stored procedure in Access, then use it in the ASP program.
(1) Creating a "stored procedure" in Access, I don't know how everyone's Access application is level. Anyway, it is only a MDB database file for me, I will only create a new MDB file, and then in the interface of Access Create a table, index, constraint, etc. Over ~ Access "query" played the role of the stored procedure. All the Access "stored procedures" or "query" I mentioned below refers to this thing for "Query", and Access provides a fool tool, and the wizard when establishing DataAdapter in vs.net. However, I like to write SQL code directly, let's take a look at the table structure of the database used in our simple example.
Then click on the "Query" button on the ACCESS main interface, and then double-click "Create Query in the Design View" on the right to open the query design view.
At this time, it is a visualized query generator that we first add a table that needs to be involved.
After adding a table, click the right mouse button on the design view and select SQL View to switch to the SQL code editing window.
Ok, let me talk about the characteristics of the ACCESS stored procedure. Access's query, my current feel is a packaging of the SQL statement, perhaps some optimization such as the precompilation. We can't use multiple operations, transactions, logic judgments, loops, etc. in writing SQL Server storage procedures ... But we use the main purpose of the Access stored procedure to use the query provided by the parameters, use the stored procedure, we don't have to face it again Splicing the parameter value to the various troubles encountered in the SQL statement string, such as:
Code:
DIM SQL
SQL = "Select * from users where username = '" & username "The above code is included in the string variable UserName, the error is reported. We must transform manually:
Code:
DIM SQL
SQL = "Select * from users where username = '" & report (username, "'", "'") & "'" is converted to two consecutive single quotes
And use the parameter query, our SQL statement can be written as:
Code:
DIM SQL
SQL = "SELECT * from users where username = @username"
Then incoming the value of the parameters @USERNAME in the parameter property of the Command object, it is very convenient to intuitive.
Code:
WITH CMD
'Creating a parameter object
.Parameters.Append .createParameter ("@ username")
'Specify value to each parameter
.Parameters ("@ username") = username
End with
Here is also the use of parameters during the Access store. In the stored procedure of SQL Server, use the @ variable to specify the parameters, and then the same name is different, the parameters in Access are identified in "order" rather than "name". The incoming parameter does not need to specify the name, the parameter name in SQL can also start, as long as the parameter value is passed, the parameters in the SQL statement will be specified. Typically, we use the Execute method of the Command object, directly incoming parameter value array to execute ~
Code:
cmd.execute, array (username)
For example, your Access stored procedure is written:
Code:
Select * from username = p_username and booktitle = p_booktitle
You can do this, through the incoming parameter value array, but the order should be corresponding:
Code:
Cmd.execute, Array (username, booktitle)
OK, look at the two queries used in our example, one is write data. Save and name it after writing the SQL statement. Another stored procedure code for reading data.
