<%@ Page Language="VB" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SQLClient" %>
<script language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim myTransaction As SqlTransaction
' Create our connection
myConnection = New SqlConnection("Data Source=10.2.2.133;" _
& "Initial Catalog=samples;User Id=samples;Password=password;" _
& "Connect Timeout=15;Network Library=dbmssocn;")
' Open the connection.
' Create our command and set its connection property.
myCommand = New SqlCommand()
myCommand.Connection = myConnection
' Begin a transaction and assign it to the Transaction property of our command.
myTransaction = myConnection.BeginTransaction()
myCommand.Transaction = myTransaction
' Build our command text and execute the command
myCommand.CommandText = "INSERT INTO [scratch] (text_field, integer_field, date_time_field) " _
& "VALUES ('" _
& CStr(WeekdayName(WeekDay(Now()))) & "', '" _
& CInt(Day(Now())) & "', '" _
& Now() & "');"
litSqlCommand.Text = myCommand.CommandText
' Either commit or rollback the transaction based on querystring
If Request.QueryString("rollback") = "true" Then
litRollback.Text = "Rolling Back Transaction."
' If we're going to commit the transaction that means that we're adding
' a record to our sample table. In order to keep the table a managable
' size we need to remove a record from the table as well. This isn't
' very important to the functioning of the script, but it makes my life
' easier and might be interesting to some.
myCommand.CommandText = "DELETE FROM scratch WHERE id = " _
& "(SELECT TOP 1 id FROM scratch ORDER BY id ASC)"
litRollback.Text = "Commiting Transaction."
' Close the connection
<title>ASP.NET Database Transactions Sample</title>
<form runat="server" EnableViewState="false">
<strong>Executing SQL Command:</strong><br />
<code><asp:Literal id="litSqlCommand" runat="server" /></code>
<strong><asp:Literal id="litRollback" runat="server" /></strong>
<strong>Run the script again:</strong>
<a href="<%= Request.ServerVariables("URL") %>?rollback=false">Commit Transaction</a>
<a href="<%= Request.ServerVariables("URL") %>?rollback=true">Rollback Transaction</a>
You can view any data that was committed to the database via our
<a href="http://aspnet.asp101.com/samples/db_simple.aspx">Simple Database</a>
sample. It's probably easiest if you use the value of the
<code>date_time_field</code> (shown in the SQL statement above)
to locate the appropriate record.
Click <a href="http://www.asp101.com/samples/db_trans_aspx.asp">here</a>
to read about and download the source code.