Transferred from: http://goaler.xicp.net/showlog.asp?id=499 We are already familiar with executing database operations in ASP to perform database operations by calling the SQL Server stored procedure, but do you know that in the desktop-level database Access, we also Can you 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, anyway, it's just a MDB database file, I only create a new MDB file, then create a table, index, constraint, etc. in Access interface, Over ~ "Query" in Access plays 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 SQLSQL = "Select * from username = '" & username "or above, if the string variable UserName contains"' "single quotes, it will be reported. We must transform: code: DIM SQLSQL = "SELECT * from users where username = '" & report (username, "'", "'") & "'" converted to two consecutive single quotes and use parameters Query, our SQL statement can be written as: code: DIM SQLSQL = "Select * from users where username = @username" and then incoming the value of the parameter @USERNAME to the parameter attribute of the Command object, it is convenient to intuitive. Code: with cmd 'Creating a parameter object. Parameters.Append .createParameter ("@ username")' gives each parameter specified value. Parameters ("@ username") = UserNameters) = UserNameters This also illustrates 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 Words: Code: Select * from users where username = p_username and booktitle = p_booktitle You can do this, through incoming parameter value, 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 save and name another stored procedure code for reading the data.
(2) Use the stored procedure and 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 - our CommandType property of our Command object is set 4, that is, Stored Proc! So ...
<% Option Explicit Dim sRandomizes = Rnd * 100 Dim conn, cmdSet 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, resultArraySet resultRS = cmd.Execute (, Null) If Not resultRS.EOF Then resultArray = resultRS .Getrows () end if set resultrs = nothingset cmd = NothingConn.closset conn = Nothing response.write "
