VB.NET & System.Data.OleDb - Transaction() - How to learn System.Data.OleDb namespace is the .NET Framework Data Provider for OLE DB data source, Using the OleDbTransaction class for makes sure that changes that were made to the store are treated as a group that can be committed or rolled back (Microsoft Access (*.mdb) Database)
ShotDev Focus:
- VB.NET & System.Data.OleDb - Transaction()
Example
Transaction.aspx
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<%@ Page Language="VB" %>
<script runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
SampleTransaction()
End Sub
Sub SampleTransaction()
Dim objConn As System.Data.OleDb.OleDbConnection
Dim objCmd As System.Data.OleDb.OleDbCommand
Dim strConnString,strSQL As String
Dim Trans As OleDbTransaction
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& _
Server.MapPath("database/mydatabase.mdb")&";Jet OLEDB:Database Password=;"
objConn = New System.Data.OleDb.OleDbConnection(strConnString)
objConn.Open()
'*** Start Transaction ***'
Trans = objConn.BeginTransaction(IsolationLevel.ReadCommitted)
Try
'*** Query 1 ***'
strSQL = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
"VALUES ('C005','Weerachai Nukitram','[email protected]','TH','2000000','1000000')"
objCmd = New System.Data.OleDb.OleDbCommand()
With objCmd
.Connection = objConn
.Transaction = Trans
.CommandType = CommandType.Text
.CommandText = strSQL
End With
objCmd.ExecuteNonQuery()
'*** Query 2 ***'
strSQL = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
"VALUES ('C005','Weerachai Nukitram','[email protected]','TH','2000000','1000000')"
objCmd = New System.Data.OleDb.OleDbCommand()
With objCmd
.Connection = objConn
.Transaction = Trans
.CommandType = CommandType.Text
.CommandText = strSQL
End With
objCmd.ExecuteNonQuery()
Trans.Commit() '*** Commit Transaction ***'
Me.lblText.Text = "Record is commit"
Catch ex As Exception
Trans.Rollback() '*** RollBack Transaction ***'
Me.lblText.Text = "Record is rollback ("& ex.Message &")"
End Try
objCmd = Nothing
objConn.Close()
objConn = Nothing
End Sub
</script>
<html>
<head>
<title>ShotDev.Com Tutorial</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label id="lblText" runat="Server"></asp:Label>
</form>
</body>
</html>
Error Case
Record is rollback (The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.)
Screenshot
.
.
.
Download this script.
Loading ...