Access| stored procedure The picture is not shown because of the problem of the patch upload:
Complete article please see: http://www.luckybbs.com/showAnnounce.asp?id=36487
We are already familiar with using SQL Server stored procedures in ASP to perform database operations, but do you know that we can also create and use stored procedures in desktop-level database Access?
Access + ASP is the perfect combination for developing lightweight WEB applications: simple, fast, and compatible, but typically not very performance-efficient. And, with ADODB. There are also some inconvenient ways in which the Connection and Recordset objects Execute SQL statements, because the parameter values of the SQL statements with parameters are often spliced into strings, so there are problems such as "single quotes." One advantage of using stored procedures is that it supports additional provision of SQL statement parameter values.
In fact, the so-called "stored procedures" in Access (version 2000 and above) are not comparable to the Stored Procedure in SQL Server. It can only be regarded as "Stored Procedure Lite", does not support more than one SQL statement, does not support logical statements (hehe, after all, not T-SQL) and so on, I also do not know whether it is precompiled. However, just as the so-called "class" implemented with VBScript is only encapsulated, as for the "landscaping" of the code structure and the Great promotion of program reusability, Access's "Lightweight stored procedures", for the specification, the database operation of the small error probability should also be helpful, and performance may be improved.
Here's how to create a stored procedure in Access, and then use it in an ASP program.
(i) Create a stored procedure in Access
I do not know how the level of Access applications, anyway it is for me, just a MDB database file Creation tool, I will only create a new MDB file, and then in the Access interface created tables, indexes, constraints, etc., over~
The query in Access plays the role of the stored procedure. The "Stored procedures" or "queries" in Access that I mentioned below refer to this thing
For query creation, Access provides a dummy tool, similar to the wizards in Vs.net when creating DataAdapter. But I like to write SQL code directly.
OK, let's take a look at the table structure of the database used in our simple example.
Pictures related to this topic
Then click the Query button on the left side of the Access main interface, and then on the right, double-click Create query in Design view to open the query Design view.
Pictures related to this topic
The pop-up is a visual query builder, and we first add the tables that the SQL statement needs to involve.
Pictures related to this topic
After adding the table, click the right mouse button in Design view and select SQL view to switch to the SQL Code editing window.
Pictures related to this topic
OK, here's what the stored procedures for Access are.
Access query, my current feeling is a wrapper on the SQL statement, perhaps some optimizations such as precompilation. We can't use multiple operations, transactions, logical judgments, loops, etc. as we write SQL Server stored procedures ...
But the main purpose of using an Access stored procedure is to use the additional query provided by the parameter, using the stored procedure, and we don't have to face any of the hassles of stitching the parameter values into the SQL statement string, such as:
Code:
Dim SQL
sql = "SELECT * from Users WHERE UserName = '" & UserName & "" "
In the above code, if the string variable userName contains "'" single quotes, an error is found. We must convert by hand:
Code:
Dim SQL
sql = "SELECT * from Users WHERE UserName = '" "& Replace (UserName," "", "" "") & "" "" "into a continuous two single quotes
Using a query with parameters, our SQL statement can be written as:
Code:
Dim SQL
sql = "SELECT * from Users WHERE UserName = @userName"
Then the parameter @userName value is passed into the Command object's Parameter property, which is convenient and intuitive.
Code:
With cmd
' Create a Parameter object
. Parameters.Append. CreateParameter ("@userName")
"Assign values to each parameter
. Parameters ("@userName") = UserName
End With
This also describes the use of parameters in Access stored procedures. and SQL Server stored procedures with the @ variable to specify the parameters, and then the same name passed in the Parameter object, Access to the parameters in the "order" rather than "name" to identify. Incoming parameters do not need to specify a name, casually, the name of the parameter in the SQL can also casually, as long as the parameter values passed in the SQL statement in the order in which the parameters appear. Typically, we use the Execute method of the Command object to pass directly into the parameter value array to perform the ~
Code:
Cmd. Execute, Array (userName)
For example, one of your Access stored procedures writes:
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 in the order that they correspond to:
Code:
Cmd. Execute, Array (UserName, BookTitle)
OK, look at the two queries used in our example, one to write the data. Save and name The SQL statement after you write it.
Pictures related to this topic
(ii) Use of stored procedures
Then we can call these stored procedures in the ASP program.
Here you can see why I said the query in Access is its stored procedure-the CommandType property of our Command object is set to 4, that is, Stored proc!
So ...
The following code is simple:
Code:
<%
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 With
Cmd. Execute, Array (CSTR (now)), CSng (s))
With cmd
. ActiveConnection = conn
. CommandType = &h0004 ' stored procedure
. CommandText = "GetData"
End With
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>"
%>
Run the results.
Pictures related to this topic
Feel, speed seems very fast, hehe ~
I don't know how much it means to use stored procedures in Access, but it's really fun.
A
