We are already familiar with performing database operations in ASP by calling SQL Server stored procedures, but do you know that we can also create and use stored procedures in desktop-level database Access?
Access + ASP is an excellent combination for developing lightweight Web applications: simple, fast, and compatible, but the performance is usually not high. Moreover, there are some inconveniences in using ADODB.Connection and Recordset objects to execute SQL statements, because the parameter values of SQL statements with parameters are often spliced into strings, so there are troubles such as single quotation marks. . One benefit of using stored procedures is that they support the additional provision of SQL statement parameter values.
In fact, the so-called stored procedures in Access (2000 and above) are incomparable with the Stored Procedures in SQL Server. It can only be regarded as Stored Procedure Lite, does not support multiple SQL statements, does not support logical statements (haha, it is not T-SQL after all), etc. I don't know yet whether it is precompiled. However, just as the so-called classes implemented by VBScript are only encapsulated and greatly promote the beautification of code structure and program reusability, Access's lightweight stored procedures should also be helpful for standardizing and minimizing error probability database operations, and Performance may be improved.
Below I will explain step by step how to create a stored procedure in Access and then use it in an ASP program.
(1) Create a stored procedure in Access
I don’t know how well everyone uses Access. Anyway, for me, it is just a tool for creating MDB database files. I only create new MDB files, and then create tables, indexes, constraints, etc. in the Access interface, over~
Queries in Access play the role of stored procedures. The Access stored procedures or queries I mention below all refer to this thing.
For query creation, Access provides a fool's tool, similar to the wizard when creating a DataAdapter in VS.NET. But I like to write SQL code directly
Then click the query button on the left on the main Access interface, and then double-click Create Query in Design View on the right to open the query design view.
At this time, the visual query builder pops up. We first add the tables that the SQL statement needs to involve.
After adding the table, right-click the design view and select SQL View to switch to the SQL code editing window.
Okay, let's talk about the characteristics of Access's stored procedures.
My current feeling is that the Access query is a wrapper for the SQL statement, perhaps with some optimization such as precompilation. We cannot use multiple operations, transactions, logical judgments, loops, etc. like writing SQL Server stored procedures...
But the main purpose of using Access stored procedures is to use queries provided by additional parameters. Using stored procedures, we no longer have to face the various troubles encountered when splicing parameter values into SQL statement strings, 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 will be reported. We have to convert manually:
Code:
Dim sql
sql = SELECT * FROM Users WHERE UserName = ' & Replace(userName, ', '') & ' ' is converted into two consecutive single quotes
Using query with parameters, our SQL statement can be written as:
Code:
Dim sql
sql = SELECT * FROM Users WHERE UserName = @userName
Then just pass in the value of parameter @userName using the Parameter property of the Command object, which is very convenient and intuitive.
Code:
With cmd
'Create parameter object
.Parameters.Append .CreateParameter(@userName)
'Specify values for each parameter
.Parameters(@userName) = userName
End With
Here we also explain the use of parameters in Access stored procedures. Unlike SQL Server's stored procedures, which use @ variables to specify parameters and then pass in parameter objects with the same name, parameters in Access are identified by order rather than name. There is no need to specify a name for the parameters passed in. You can name them casually. The parameter names in SQL can also be named casually. As long as the parameter values are passed in, they should be specified in the order in which the parameters appear in the SQL statement. Usually, we use the Execute method of the Command object and directly pass in the parameter value array to execute ~
Code:
cmd.Execute, Array(userName)
For another example, one of your Access stored procedures is written like this:
Code:
select * from Users where UserName = p_UserName and BookTitle = p_bookTitle
You can just do it this way, by passing in an array of parameter values, but in the correct order:
Code:
cmd.Execute, Array(userName, bookTitle)
OK, let's look at the two queries used in our example, one is to write data. After writing the SQL statement, save it and name it.
Another stored procedure code that reads data.
(2) Using stored procedures
Then we can call these stored procedures in the ASP program.
Here you can see why I said that the query in Access is its stored procedure - the CommandType property of our Command object is set to 4, which is Stored Proc!
so...
The following code is very 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>
%>