Use the "Storage Procedure" in Access

xiaoxiao2021-03-05  23

Author: sinzy / 9CBS we are already familiar in ASP to perform database operations by calling SQL Server stored procedure, but if you know, in the Access desktop database, 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 Position the parameter value to the various troubles encountered in the SQL statement string, such as: Code: ------------------------------------------------------------------------------------------ -------------------------------------------------- - DIM SQL SQL = "SELECT * from username where username = '" & username "------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------- - In the above code, if the string variable UserName contains "'" single quotes, the error will be reported. We must transform manual: Code: ------------------------------------------- ------------------------------------- DIM SQL SQL = "SELECT * from users where username = ' "& Replace (username," '","' ") &" '"is converted to two consecutive single quotes --------------------- -------------------------------------------------- ------, use the parameter query, our SQL statement can be written as: code: --------------------------- -------------------------------------------------- --- DIM SQL SQL = "Select * from users where username = @username" -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------- then The value of the parameter @USERNAME is incorporated by the parameter property of the Command object, it is very convenient to intuitive. Code: ------------------------------------------------ ------------------------------ WITH CMD 'Create a parameter object.Parameters.Append .createParameter ("@ username") 'Specify value for each parameter.Parameters ("@ username") = username end with ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------ To illustrate 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. Generally, we use the Execute method of the Command object, directly into the parameter value group to execute ~ code: ----------------------------- -------------------------------------------------- - cmd.execute, array (username) ------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------, for example, your Access store process is written : Code: ----------------------------------------------- -------------------------------- SELECT * from users where 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. Write the SQL statement and name it. Another stored procedure code for reading data.

转载请注明原文地址:https://www.9cbs.com/read-34730.html

New Post(0)