Microsoft KB Archive/310351

= How To Roll Back Updates After an Error When You Use DataAdapter and DataSet in ADO.NET and Visual Basic .NET =

Article ID: 310351

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 1.1
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition

-



This article was previously published under Q310351



For a Microsoft Visual C# .NET version of this article, see 316024.

This article refers to the following Microsoft .NET Framework Class Library namespaces:
 * System.Data
 * System.Data.SqlClient

IN THIS TASK
SUMMARY
 * Steps to Build the Sample

REFERENCES



SUMMARY
This article describes how to roll back updates after an error when you use a DataAdapter and a DataSet object.

If your client application uses an ADO.NET DataSet object to store changes that are made to data or to add new records, you can use a Transaction object, a DataSet object, and a DataAdapter object to roll back any changes if an error occurs. If you use a CommandBuilder object to generate INSERT, UPDATE, and DELETE commands, you must set the Transaction property of the SELECT command of the DataAdapter.

If you use a wizard to generate a typed DataSet and its Command objects to populate the InsertCommand, the UpdateCommand, and the DeleteCommand properties of the DataAdapter, you must set the Transaction property for each InsertCommand, UpdateCommand, and DeleteCommand but not the SelectCommand of the DataSet.

NOTE: This article uses the CommandBuilder.

back to the top

Steps to Build the Sample
 Follow these steps to create a new Visual Basic .NET Windows Application project:  Start Microsoft Visual Studio .NET. On the File menu, point to New, and then click Project. In the New Project dialog box, click Visual Basic Projects under Project Types, and then click Windows Application under Templates.   Add the following Imports statement to the &quot;Form1 Declarations&quot; section: Imports System.Data.SqlClient   Add a Button control, and then add the following code to the Click event of Button1: Dim stConnect As String = &quot;user id=myUserID;password=myPassword;initial catalog=northwind;data source=myServer;Connect Timeout=30&quot; Dim cnNorthwind As New SqlConnection(stConnect) Dim stSQL As String = &quot;select * from employees&quot; Dim cmNorthwind As New SqlCommand(stSQL, cnNorthwind) Dim daNorthwind As New SqlDataAdapter(cmNorthwind) 'sqlCommandBuilder will generate the insert, update and delete command of the DataAdapter Dim cmbNorthwind As New SqlCommandBuilder(daNorthwind) Dim dsNorthwind As New DataSet Dim trNorthwind As SqlTransaction daNorthwind.Fill(dsNorthwind, &quot;Employees&quot;)

cnNorthwind.Open

' Add you code to add or change a row(s) here 'Dim dr As DataRow 'dr = dsNorthwind.Tables(0).NewRow 'dr(1) = &quot;John&quot; 'dr(2) = &quot;Doe&quot; 'dsNorthwind.Tables(0).Rows.Add(dr)

Dim dsNorthwind2 As New DataSet dsNorthwind2 = dsNorthwind.GetChanges trNorthwind = cnNorthwind.BeginTransaction(IsolationLevel.ReadCommitted)

daNorthwind.SelectCommand.Transaction = trNorthwind

Try daNorthwind.Update(dsNorthwind2, &quot;Employees&quot;) trNorthwind.Commit ' if you expect ds2 to be modified by the Update process dsNorthwind.Merge(dsNorthwind2, False)

MsgBox(&quot;Record(s) add or changed&quot;) Catch ex As Exception Try trNorthwind.Rollback MsgBox(&quot;An error accured and all changes have been Rollback&quot;) Catch TransacationEx As Exception 'Handle Exception

End Try

End Try </li> Modify the parameter of the connection string property of the SqlConnection object as necessary for your environment.</li> Run the code, and note that the changes have been rolled back. You can uncomment the code that adds a DataRow to see the transaction committed.</li> You can add a DataGrid control and code to bind the grid to your DataSet to view records, to make changes, or to add records to the DataSet.</li></ol>

back to the top

<div class="references_section">