Transactions used by access databases in ASP Programming

Generally, when processing data tasks, an SQL statement cannot perform all data operations, but multiple statements are required. At this time, we need to use database transactions, transactions can effectively ensure data integrity. Most people are familiar with SQL Server and other large databases, but they are not so familiar with access databases. Here I will explain how to use transactions in access databases.

1. Define data connection. Note: The driver above oledb4.0 must be used to connect to the database.
Connstr = "provider = Microsoft. Jet. oledb.4.0; Data Source =" & server. mappath ("database/CY #7812 & ZY. mdb ")
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open connstr

2. Use transactions
Conn. begintrans 'start transaction
SQL = "update a set num = 1000 where id = 24" 'the first SQL statement is update. (Correct syntax)
Conn.exe cute (SQL)
SQL = "insert into a (Num) values ('A')" 'The second SQL statement is an incorrect SQL statement
Conn.exe cute (SQL)
SQL = "insert into a (Num) values (33333)" 'The third SQL statement is the correct SQL statement.
Conn.exe cute (SQL)
If conn. errors. Count = 0 then
Conn. committrans 'if there is no conn error, the transaction is committed.
Else
Conn. rollbacktrans 'or roll back
End if
After debugging, the above Code can be processed normally. However, sometimes we do not want to display compilation errors to users. Then we need to add on error resume next after conn. begintrans. However, on error resume next is used. Conn. errors. Count can only obtain the results returned by the conn of the last database operation. The preceding three SQL statements are invalid because the last SQL statement is correct. Then we need to make corresponding changes to the error handling.
If conn. errors. Count = 0 then should be changed to If err. Number = 0 then
In this way, we can perform other corresponding operations or prompts after database rollback. The modified code is as follows:

Conn. begintrans 'start transaction
On Error resume next
SQL = "update a set num = 1000 where id = 24" 'the first SQL statement is update. (Correct syntax)
Conn.exe cute (SQL)
SQL = "insert into a (Num) values ('A')" 'The second SQL statement is an incorrect SQL statement
Conn.exe cute (SQL)
SQL = "insert into a (Num) values (33333)" 'The third SQL statement is the correct SQL statement.
Conn.exe cute (SQL)
If conn. errors. Count = 0 then
Conn. committrans 'if there is no conn error, the transaction is committed.
Else
Conn. rollbacktrans 'or roll back
End if

If err. Number = 0 then
Conn. committrans 'if there is no conn error, the transaction is committed.
Else
Conn. rollbacktrans 'or roll back
'Other operations after rollback
Strerr = err. Description
Response. Write "Database Error! Error Log: <font color = Red> "& strerr &" </font>"
Response. End
End if