There are a couple of problems with the code in the book. The first issue is that the Transaction object is not assigned to the DataAdapter when it is constructed. The second issue is that the code tries to rollback the transaction after the ExecuteReader method is called. The ExecuteReader method closes the connection to the database. Once that is done, the database cannot be rolled back. Instead of using ExecuteReader, create a new DataAdapter to fill DataGridUpdated. After you have done this, rollback the transaction and close the database connection manually.
Here is the new code:
<%@ Page Language="C#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
void Page_Load(Object sender, EventArgs e) {
string connectionString;
string queryString;
DataSet data = new DataSet();
OleDbConnection dbConnection;
OleDbDataAdapter dataAdapter;
OleDbCommand command;
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " +
"Ole DB Services=-4; " +
"Data Source=C:\\BegASPNET11\\CH09\\data\\Northwind.mdb" ;
queryString = "SELECT EmployeeID, FirstName, LastName FROM Employees";
// set the connection and command
dbConnection = new OleDbConnection(connectionString);
dbConnection.Open();
OleDbTransaction dbTrans = dbConnection.BeginTransaction();
command = new OleDbCommand();
command.CommandText = queryString;
command.Connection = dbConnection;
command.Transaction = dbTrans;
dataAdapter = new OleDbDataAdapter(command);
// fetch the data
dataAdapter.Fill(data, "Employees");
// display the data
DataGrid1.DataSource = data.Tables["Employees"];
DataGrid1.DataBind();
//-------------------------------------------------------
// add a new row to the table
DataTable table;
DataRow newRow;
table = data.Tables["Employees"];
newRow = table.NewRow();
newRow["FirstName"] = "Freddy1";
newRow["LastName"] = "Blake";
table.Rows.Add(newRow);
// add another new row. We'll be deleting the one above later
// and we can't delete existing rows from the database because
// of referential integrity (every employee also has orders)
newRow = table.NewRow();
newRow["FirstName"] = "Fredd2";
newRow["LastName"] = "Hart";
table.Rows.Add(newRow);
// bind the second grid to the new data
DataGrid2.DataSource = table;
DataGrid2.DataBind();
//-------------------------------------------------------
// edit an existing row in the table
DataRow row;
// the Rows collection is 0 indexed
// so this will change the fourth row
row = table.Rows[3];
row["FirstName"] = "John";
row["LastName"] = "Hartford";
// bind the third grid to the new data
DataGrid3.DataSource = table;
DataGrid3.DataBind();
//-------------------------------------------------------
// delete a row from the table
table.Rows[table.Rows.Count - 2].Delete();
// bind the fourth grid to the new data
DataGrid4.DataSource = table;
DataGrid4.DataBind();
//-------------------------------------------------------
// generate the update commands
OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);
dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
dataAdapter.InsertCommand = commandBuilder.GetInsertCommand();
dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand();
//-------------------------------------------------------
// updqate the data store
dataAdapter.Update(data, "Employees");
//-------------------------------------------------------
// refresh the data in the DataReader and bind it to a new grid
// to prove that the data store has been updated
queryString = "SELECT EmployeeID, FirstName, LastName FROM Employees";
OleDbCommand cmd = new OleDbCommand(queryString, dbConnection);
cmd.Transaction = dbTrans;
dataAdapter = new OleDbDataAdapter(cmd);
data = new DataSet();
dataAdapter.Fill(data,"Employees");
DataGridUpdated.DataSource = data.Tables["Employees"];
DataGridUpdated.DataBind();
dbTrans.Rollback();
dbConnection.Close();
}
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<table width="100%">
<tbody>
<tr>
<td>
Original Data</td>
<td>
Data with new Row</td>
<td>
Data with edited Row</td>
<td>
Data with deleted Row</td>
</tr>
<tr>
<td valign="top">
<asp:DataGrid id="DataGrid1" runat="server"></asp:DataGrid>
</td>
<td valign="top">
<asp:DataGrid id="DataGrid2" runat="server"></asp:DataGrid>
</td>
<td valign="top">
<asp:DataGrid id="DataGrid3" runat="server"></asp:DataGrid>
</td>
<td valign="top">
<asp:DataGrid id="DataGrid4" runat="server"></asp:DataGrid>
</td>
</tr>
</tbody>
</table>
Data fetched from the database after the update:<br />
<asp:DataGrid id="DataGridUpdated" runat="server"></asp:DataGrid>
</form>
</body>
</html>
Chapter 9 Begin Transaction Statement
