Create a stored procedure/view using query in access and execute it using ASP

Abstract: This article demonstrates the function of using access query to construct a stored procedure similar to MSSQL, and insert data in ASP to display data.

Directory:
1. Create a table in the access query, named TT

2. Create a query in the access query with the following content :...

3. Use tt_insertparm in ASP to add 10 records to table TT
3.1 construct the asp ado connection function, fcreatecnn and fclosecnn
3.2 use fcreatecnn, fclosecnn, and tt_insertparm in combination to add records

4. Create a stored procedure/view for displaying data in the access query. Content :...

5. Use tt_selectparm in ASP to select data

6. Complete.

1. Create a table in the access query, named TT
Enter the following content to query and execute the statement. After execution, the table TT is created.

Linenum
1. Create Table TT
2. (
3. Id autoincrement
4. Primary Key,
5. Title varchar (255 ),
6. Content memo,
7. Dateandtime date
8. )

Note:
The ID field is automatically numbered and is the primary key.
Title is of the text type, with a length of 255
Content is the remarks type
Dateandtime is of the date type.

2. Create a query in access query with the following content:

Linenum
1. Parameters sptitle varchar (255), spcontent varchar (255 );
2. Insert into TT (title, content, dateandtime)
3. Values ([sptitle], [spcontent], now ())

Save the name tt_insertparm
Function: Add a record to the access stored procedure
Note: In the spcontent field, the memo data type cannot be used.

3. Use tt_insertparm in ASP to add 10 records to table TT

3.1 construct the asp ado connection function, fcreatecnn and fclosecnn

Linenum
1. <%
2. Function fcreatecnn (CNN)
3. Set CNN = Createobject ("ADODB. Connection ")
4. End Function
6. Function fclosecnn (CNN)
7. CNN. Close
8. Set CNN = nothing
9. End Function
10. %>

3.2 use fcreatecnn, fclosecnn, and tt_insertparm in combination to add records

Linenum
1. <%
2. Dim title, content
3. Title = "insert title"
4. Content = "insert content"
5. Dim CNN, RS
6. Dim I
7. Call fcreatecnn (CNN)
8. CNN. Open Conn
9. For I = 1 to 10
10. Cnn.exe cute ("Exec tt_insertparm" & Title & I & "," & content & I)
11. Next
12. Call fclosecnn (CNN)
13. If err. Number = 0 then response. Write "data added" else response. Write "error, data not added"
14. %>

4. Create a stored procedure/view for displaying data in the access query. content:

Linenum
1. Parameters qid long;
2. Select *
3. From TT
4. Where id = IIF (isnull ([qid]), ID, [qid])

Save the name tt_selectparm
Description: If the qid parameter value is null, all data is selected. Otherwise, the corresponding row of qid is selected.

5. Use tt_selectparm in ASP to select data

Linenum
1. <%
2. Dim qid, fldnum, I
3. Qid = 10
4. Dim CNN, RS
5. Call fcreatecnn (CNN)
6. CNN. Open Conn
7. Set rs = cnn.exe cute ("Exec tt_selectparm" & qid)
8. 'Set rs = cnn.exe cute ("Exec tt_selectparm null ")
9. Fldnum = Rs. Fields. Count-1
10. Do until Rs. EOF
11. For I = 0 to fldnum
12. Response. Write RS (I)
13. Response. Write ""
14. Next
15. Response. Write "<br/>"
16. Rs. movenext
17. Loop
18. Rs. Close
19. Set rs = nothing
20. Call fclosecnn (CNN)
21. %>

6. Complete.

Note: There are also updates. The Stored Procedure/view for data deletion is not demonstrated. However, if you read the above operations, this should not be a problem.
From: http://blog.csdn.net/btbtd/archive/2006/08/30/1145678.aspx