14.6 Changing Database Records

So far, you've retrieved data from a database, but you haven't manipulated its records in any way. Using ADO.NET, it is of course possible to add a record, change an existing record, or delete a record altogether.

In a typical implementation, you might work your way through the following steps:

This process gives you very fine control over the update to your data as well as an opportunity to fix any data that would otherwise cause an error.

In the following example, you'll create a dialog box that displays the contents of the Customer table in Northwinds. The goal is to test updating a record, adding a new record, and deleting a record. As always, I'll keep the code as simple as possible, which means eliminating many of the error-checking and exception-handling routines you might expect in a production program.

Figure 14-7 shows the somewhat crude but useful form I've built to experiment with these features of ADO.NET.

Figure 14-7. The ADO update form

This form consists of a listbox (lbCustomers), a button for Update (btnUpdate), an associated text box (txtCustomerName), and a Delete button (btnDelete). There is also a set of eight text fields that are used in conjunction with the New button (btnNew). These text fields represent eight of the fields in the Customers table in the Northwind database. There is also a label (lblMessage) that you can use for writing messages to the user (it currently says Press New, Update, or Delete).

14.6.1 Accessing the Data

First, create the DataAdapter object and the DataSet as private member variables, along with the DataTable:

private SqlDataAdapter dataAdapter;
private DataSet dataSet;
private DataTable dataTable;

This enables you to refer to these objects from various member methods. Start by creating strings for the connection and the command that will get you the table you need:

string connectionString = "server=(local)\\NetSDK;" + 
    "Trusted_Connection=yes; database=northwind";
string commandString = "Select * from Customers";

These strings are passed as parameters to the SqlDataAdapter constructor:

dataAdapter = 
    new SqlDataAdapter(commandString, connectionString);

A DataAdapter may have four SQL commands associated with it. Right now, we have only one: dataAdapter.SelectCommand. The InitializeCommands( ) method creates the remaining three: InsertCommand, UpdateCommand, and DeleteCommand. InitializeCommands( ) uses the AddParams method to associate a column in each SQL command with the columns in the modified rows:

private void AddParams(SqlCommand cmd, params string[] cols) {
  // Add each parameter
  foreach (String column in cols) {
    cmd.Parameters.Add(
      "@" + column, SqlDbType.Char, 0, column);
  }
}

InitializeCommands( ) creates each SQL command in turn, using placeholders that correspond to the column argument passed to AddParams( ):

private void InitializeCommands( )
{

   // Reuse the SelectCommand's Connection.
   SqlConnection connection =
     (SqlConnection) dataAdapter.SelectCommand.Connection;

   // Create an explicit, reusable insert command
   dataAdapter.InsertCommand = connection.CreateCommand( );
   dataAdapter.InsertCommand.CommandText =
   "Insert into customers " +
      "(CustomerId, CompanyName, ContactName, ContactTitle, " +
      " Address, City, PostalCode, Phone) " +
      "values(@CustomerId, @CompanyName, @ContactName, " +
      "   @ContactTitle, @Address, @City, @PostalCode, @Phone)";
   AddParams(dataAdapter.InsertCommand,
     "CustomerId", "CompanyName", "ContactName", "ContactTitle",
     "Address", "City", "PostalCode", "Phone");

   // Create an explicit update command
   dataAdapter.UpdateCommand = connection.CreateCommand( );
   dataAdapter.UpdateCommand.CommandText = "update Customers " +
     "set CompanyName = @CompanyName where CustomerID = @CustomerId";
   AddParams(dataAdapter.UpdateCommand, "CompanyName", "CustomerID");

   // Create an explicit delete command
   dataAdapter.DeleteCommand = connection.CreateCommand( );
   dataAdapter.DeleteCommand.CommandText =
     "delete from customers where customerID = @CustomerId";
   AddParams(dataAdapter.DeleteCommand, "CustomerID");
}

The DataAdapter uses InsertCommand, UpdateCommand, and DeleteCommand to modify the table when you invoke Update( ).

Back in the constructor, you can now create the DataSet and fill it with the SqlDataAdapter object you've just created:

dataSet = new DataSet( );
dataAdapter.Fill(DataSet,"Customers");

Display the table contents by calling the PopulateLB( ) method, which is a private method that fills the listbox from the contents of the single table in the DataSet:

dataTable = dataSet.Tables[0];
lbCustomers.Items.Clear( );
foreach (DataRow dataRow in dataTable.Rows)
{
    lbCustomers.Items.Add(
        dataRow["CompanyName"] + 
        " (" + dataRow["ContactName"] + ")" );
}

14.6.2 Updating a Record

The form is now displayed, and you're ready to update a record. Highlight a record and fill in a new customer name in the topmost text field. When you press Update, read the resulting name and put it into the chosen record.

The first task is to get the specific row the user wants to change:

DataRow targetRow  = dataTable.Rows[lbCustomers.SelectedIndex];

Declare a new object of type DataRow and initialize it with a reference to the specific row in the DataTable's Rows collection that corresponds to the selected item in the listbox. Remember that DataTable was declared as a member variable and initialized in the PopulateLB( ) method shown in the previous section.

You can now display the name of the company you're going to update:

lblMessage.Text = "Updating " +  targetRow["CompanyName"];
Application.DoEvents( );

The call to the static method DoEvents( ) of the Application class causes the application to process Windows messages and paint the screen with them. If you were to leave this line out, the current thread would dominate the processor and the messages would not be printed until the button handler completes its work.

Call BeginEdit( ) on the DataRow to put the row into editing mode. This suspends events on the row so that you could, if you chose, edit a number of rows at once without triggering validation rules (there are no validation rules in this example). It is good form to bracket changes on DataRows with calls to BeginEdit( ) and EndEdit( ):

targetRow.BeginEdit( ); 
targetRow["CompanyName"] = txtCustomerName.Text;
targetRow.EndEdit( );

The actual edit is to the column CompanyName within the targetRow object, which is set to the text value of the text control txtCustomerName. The net effect is that the CompanyName field in the row is set to whatever the user put into that text box.

Notice that the column you want is indexed within the row by the name of that column. In this case, the name will match the name that is used in the database, but this is not required. When you created the DataSet, you could have used the TableMappings( ) method to change the names of the columns.

Having edited the column, you are ready to check to make sure there are no errors. First, extract all the changes made to the DataSet (in this case, there will be only one change) using the GetChanges( ) method, passing in a DataRowState enumeration to indicate that you want only those rows that have been modified. GetChanges( ) returns a new DataSet object:

DataSet dataSetChanged = 
    dataSet.GetChanges(DataRowState.Modified);

Now you can check for errors. To simplify the code, I've included a flag to indicate that all is OK. If you find any errors, rather than trying to fix them, just set the flag to false and don't make the updates:

bool okayFlag = true;
if (dataSetChanged.HasErrors)
{
   okayFlag = false;
   string msg = "Error in row with customer ID "; 

   foreach (DataTable theTable in dataSetChanged.Tables)
   {
      if (theTable.HasErrors)
      {
         DataRow[] errorRows = theTable.GetErrors( );

         foreach (DataRow theRow in errorRows)
         {
            msg = msg + theRow["CustomerID"];
         }
      }
   }
   lblMessage.Text = msg;
}

First test to see whether the new data record set has any errors by checking the HasErrors property. If HasErrors is true, there are errors; set the Boolean okayFlag to false, and then go on to discover where the error lies. To do so, iterate through all the tables in the new database (in this case, there is only one); if a table has errors, you'll get an array of all the rows in that table with errors (shown here as the errorRows array).

Then iterate through the array of rows with errors, handling each in turn. In this case, you just update the message on the dialog box. However, in a production environment you might interact with the user to fix the problem.

If the okayFlag is still true after testing HasErrors, there were no errors and you are ready to update the database:

if (okayFlag)
{
    dataAdapter.Update(dataSetChanged,"Customers");

This causes the DataAdapter object to create the necessary command text to update the database. Next, update the message:

lblMessage.Text = "Updated " +  targetRow["CompanyName"];
Application.DoEvents( );

You now must tell the DataSet to accept the changes and then repopulate the listbox from the DataSet:

dataSet.AcceptChanges( );
PopulateLB( );

If okayFlag is false, there are errors; in this example, we'd just reject the changes:

else
     dataSet.RejectChanges( );

14.6.3 Deleting a Record

The code for handling the Delete button is even simpler. First, get the target row:

DataRow targetRow  = 
  dataTable.Rows[lbCustomers.SelectedIndex];

and form the delete message:

string msg = targetRow["CompanyName"] + " deleted. ";

You don't want to show the message until the row is deleted, but you need to get it now because after you delete the row it will be too late!

You're now ready to mark the row for deletion:

targetRow.Delete( );

Calling AcceptChanges( ) on the DataSet causes AcceptChanges( ) to be called on each table within the DataSet. This in turn causes AcceptChanges( ) to be called on each row in those tables. Thus the one call to dataSet.AcceptChanges( ) cascades down through all the contained tables and rows.

Next, you need to call Update( ) and AcceptChanges( ), and then refresh the listbox. However, if this operation fails, the row will still be marked for deletion. If you then try to issue a legitimate command, such as an insertion, update, or another deletion, the DataAdapter will try to commit the erroneous deletion again, and the whole batch will fail because of that delete. In order to avert this situation, wrap the remaining operations in a try block and call RejectChanges( ) if they fail:

// update the database
try
{
  dataAdapter.Update(dataSet,"Customers");
  dataSet.AcceptChanges( );
  // repopulate the list box without the deleted record
  PopulateLB( );

  // inform the user
  lblMessage.Text = msg;
  Application.DoEvents( );
}
catch (SqlException ex)
{
  dataSet.RejectChanges( );
  MessageBox.Show(ex.Message);
}

Deleting records from the Customers database might cause an exception if the record deleted is constrained by database integrity rules. For example, if a customer has orders in the Orders table, you cannot delete the customer until you delete the orders. To solve this, the following example will create new Customer records that you can then delete at will.

14.6.4 Creating New Records

To create a new record, the user will fill in the fields and press the New button. This will fire the btnNew_Click event, which is tied to the btnNew_Click event handling method:

btnNew.Click += new System.EventHandler (this.btnNew_Click);

In the event handler, call DataTable.NewRow( ), which asks the table for a new DataRow object:

DataRow newRow = dataTable.NewRow( );

This is very elegant because the new row that the DataTable produces has all the necessary DataColumns for this table. You can just fill in the columns you care about, taking the text from the user interface:

newRow["CustomerID"] = txtCompanyID.Text;
newRow["CompanyName"] = txtCompanyName.Text;
newRow["ContactName"] = txtContactName.Text;
newRow["ContactTitle"] = txtContactTitle.Text;
newRow["Address"] = txtAddress.Text;
newRow["City"] = txtCity.Text;
newRow["PostalCode"] = txtZip.Text;
newRow["Phone"] = txtPhone.Text;

Now that the row is fully populated, just add it back to the table:

dataTable.Rows.Add(newRow);

The table resides within the DataSet, so all you have to do is tell the DataAdapter object to update the database with the DataSet and accept the changes:

dataAdapter.Update(dataSet,"Customers");
dataSet.AcceptChanges( );

Next, update the user interface:

lblMessage.Text = "Updated!";
Application.DoEvents( );

You can now repopulate the listbox with your new added row and clear the text fields so that you're ready for another new record:

PopulateLB( );
ClearFields( );

ClearFields( ) is a private method that simply sets all the text fields to empty strings. That method and the entire program are shown in Example 14-6.

Example 14-6. Updating, deleting, and adding records

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
namespace ProgrammingCSharpWindows.Form
{
   public class ADOForm1 : System.Windows.Forms.Form
   {
      private System.ComponentModel.Container components;
      private System.Windows.Forms.Label label9;
      private System.Windows.Forms.TextBox txtPhone;
      private System.Windows.Forms.Label label8;
      private System.Windows.Forms.TextBox txtContactTitle;
      private System.Windows.Forms.Label label7;
      private System.Windows.Forms.TextBox txtZip;
      private System.Windows.Forms.Label label6;
      private System.Windows.Forms.TextBox txtCity;
      private System.Windows.Forms.Label label5;
      private System.Windows.Forms.TextBox txtAddress;
      private System.Windows.Forms.Label label4;
      private System.Windows.Forms.TextBox txtContactName;
      private System.Windows.Forms.Label label3;
      private System.Windows.Forms.TextBox txtCompanyName;
      private System.Windows.Forms.Label label2;
      private System.Windows.Forms.TextBox txtCompanyID;
      private System.Windows.Forms.Label label1;
      private System.Windows.Forms.Button btnNew;
      private System.Windows.Forms.TextBox txtCustomerName;
      private System.Windows.Forms.Button btnUpdate;
      private System.Windows.Forms.Label lblMessage;
      private System.Windows.Forms.Button btnDelete;
      private System.Windows.Forms.ListBox lbCustomers;

      // the DataSet, DataAdapter, and DataTable are members 
      // so that we can access them from any member method.
      private SqlDataAdapter dataAdapter;
      private DataSet dataSet;
      private DataTable dataTable;

      public ADOForm1( )
      {
         InitializeComponent( );

         string connectionString = "server=(local)\\NetSDK;" +
           "Trusted_Connection=yes; database=northwind";
         string commandString = "Select * from Customers";
         dataAdapter = 
            new SqlDataAdapter(commandString, connectionString);

         InitializeCommands( );

         dataSet = new DataSet( );
         dataAdapter.Fill(dataSet,"Customers");
         PopulateLB( );
      }

      private void AddParms(SqlCommand cmd, params string[] cols) {
        // Add each parameter
        foreach (String column in cols) {
          cmd.Parameters.Add(
            "@" + column, SqlDbType.Char, 0, column);
        }
      }

      private void InitializeCommands( )
      {

         // Reuse the SelectCommand's Connection.
         SqlConnection connection = 
           (SqlConnection) dataAdapter.SelectCommand.Connection;

         // Create an explicit, reusable insert command
         dataAdapter.InsertCommand = connection.CreateCommand( );
         dataAdapter.InsertCommand.CommandText = 
         "Insert into customers " +
            "(CustomerId, CompanyName, ContactName, ContactTitle, " +
            " Address, City, PostalCode, Phone) " +
            "values(@CustomerId, @CompanyName, @ContactName, " +
            "   @ContactTitle, @Address, @City, @PostalCode, @Phone)";
         AddParms(dataAdapter.InsertCommand, 
           "CustomerId", "CompanyName", "ContactName", "ContactTitle", 
           "Address", "City", "PostalCode", "Phone");

         // Create an explicit update command
         dataAdapter.UpdateCommand = connection.CreateCommand( );
         dataAdapter.UpdateCommand.CommandText = "update Customers " +
           "set CompanyName = @CompanyName where CustomerID = @CustomerId";
         AddParms(dataAdapter.UpdateCommand, "CompanyName", "CustomerID");

         // Create an explicit delete command
         dataAdapter.DeleteCommand = connection.CreateCommand( );
         dataAdapter.DeleteCommand.CommandText = 
           "delete from customers where customerID = @CustomerId";
         AddParms(dataAdapter.DeleteCommand, "CustomerID");
      }

      // fill the list box with columns from the Customers table
      private void PopulateLB( )
      {
         dataTable = dataSet.Tables[0];
         lbCustomers.Items.Clear( );
         foreach (DataRow dataRow in dataTable.Rows)
         {
            lbCustomers.Items.Add(
               dataRow["CompanyName"] + " (" + 
               dataRow["ContactName"] + ")" );
         }
            
      }

      protected override void Dispose(bool disposing)
      {
         if (disposing)
         {
             if (components == null)
             {
                 components.Dispose( );
             }
         }
         base.Dispose(disposing);
      }

      private void InitializeComponent( )
      {
         this.components = new System.ComponentModel.Container ( );
         this.txtCustomerName = new System.Windows.Forms.TextBox ( );
         this.txtCity = new System.Windows.Forms.TextBox ( );
         this.txtCompanyID = new System.Windows.Forms.TextBox ( );
         this.lblMessage = new System.Windows.Forms.Label ( );
         this.btnUpdate = new System.Windows.Forms.Button ( );
         this.txtContactName = new System.Windows.Forms.TextBox ( );
         this.txtZip = new System.Windows.Forms.TextBox ( );
         this.btnDelete = new System.Windows.Forms.Button ( );
         this.txtContactTitle = new System.Windows.Forms.TextBox ( );
         this.txtAddress = new System.Windows.Forms.TextBox ( );
         this.txtCompanyName = new System.Windows.Forms.TextBox ( );
         this.label5 = new System.Windows.Forms.Label ( );
         this.label6 = new System.Windows.Forms.Label ( );
         this.label7 = new System.Windows.Forms.Label ( );
         this.label8 = new System.Windows.Forms.Label ( );
         this.label9 = new System.Windows.Forms.Label ( );
         this.label4 = new System.Windows.Forms.Label ( );
         this.lbCustomers = new System.Windows.Forms.ListBox ( );
         this.txtPhone = new System.Windows.Forms.TextBox ( );
         this.btnNew = new System.Windows.Forms.Button ( );
         this.label1 = new System.Windows.Forms.Label ( );
         this.label2 = new System.Windows.Forms.Label ( );
         this.label3 = new System.Windows.Forms.Label ( );
         //@this.TrayHeight = 0;
         //@this.TrayLargeIcon = false;
         //@this.TrayAutoArrange = true;
         txtCustomerName.Location = new System.Drawing.Point (256, 120);
         txtCustomerName.TabIndex = 4;
         txtCustomerName.Size = new System.Drawing.Size (160, 20);
         txtCity.Location = new System.Drawing.Point (384, 245);
         txtCity.TabIndex = 15;
         txtCity.Size = new System.Drawing.Size (160, 20);
         txtCompanyID.Location = new System.Drawing.Point (136, 216);
         txtCompanyID.TabIndex = 7;
         txtCompanyID.Size = new System.Drawing.Size (160, 20);
         lblMessage.Location = new System.Drawing.Point (32, 368);
         lblMessage.Text = "Press New, Update or Delete";
         lblMessage.Size = new System.Drawing.Size (416, 48);
         lblMessage.TabIndex = 1;
         btnUpdate.Location = new System.Drawing.Point (32, 120);
         btnUpdate.Size = new System.Drawing.Size (75, 23);
         btnUpdate.TabIndex = 0;
         btnUpdate.Text = "Update";
         btnUpdate.Click += 
            new System.EventHandler (this.btnUpdate_Click);
         txtContactName.Location = new System.Drawing.Point (136, 274);
         txtContactName.TabIndex = 11;
         txtContactName.Size = new System.Drawing.Size (160, 20);
         txtZip.Location = new System.Drawing.Point (384, 274);
         txtZip.TabIndex = 17;
         txtZip.Size = new System.Drawing.Size (160, 20);
         btnDelete.Location = new System.Drawing.Point (472, 120);
         btnDelete.Size = new System.Drawing.Size (75, 23);
         btnDelete.TabIndex = 2;
         btnDelete.Text = "Delete";
         btnDelete.Click += 
            new System.EventHandler (this.btnDelete_Click);
         txtContactTitle.Location = new System.Drawing.Point (136, 303);
         txtContactTitle.TabIndex = 12;
         txtContactTitle.Size = new System.Drawing.Size (160, 20);
         txtAddress.Location = new System.Drawing.Point (384, 216);
         txtAddress.TabIndex = 13;
         txtAddress.Size = new System.Drawing.Size (160, 20);
         txtCompanyName.Location = new System.Drawing.Point (136, 245);
         txtCompanyName.TabIndex = 9;
         txtCompanyName.Size = new System.Drawing.Size (160, 20);
         label5.Location = new System.Drawing.Point (320, 252);
         label5.Text = "City";
         label5.Size = new System.Drawing.Size (48, 16);
         label5.TabIndex = 14;
         label6.Location = new System.Drawing.Point (320, 284);
         label6.Text = "Zip";
         label6.Size = new System.Drawing.Size (40, 16);
         label6.TabIndex = 16;
         label7.Location = new System.Drawing.Point (40, 312);
         label7.Text = "Contact Title";
         label7.Size = new System.Drawing.Size (88, 16);
         label7.TabIndex = 28;
         label8.Location = new System.Drawing.Point (320, 312);
         label8.Text = "Phone";
         label8.Size = new System.Drawing.Size (56, 16);
         label8.TabIndex = 20;
         label9.Location = new System.Drawing.Point (120, 120);
         label9.Text = "New Customer Name:";
         label9.Size = new System.Drawing.Size (120, 24);
         label9.TabIndex = 22;
         label4.Location = new System.Drawing.Point (320, 224);
         label4.Text = "Address";
         label4.Size = new System.Drawing.Size (56, 16);
         label4.TabIndex = 26;
         lbCustomers.Location = new System.Drawing.Point (32, 16);
         lbCustomers.Size = new System.Drawing.Size (512, 95);
         lbCustomers.TabIndex = 3;
         txtPhone.Location = new System.Drawing.Point (384, 303);
         txtPhone.TabIndex = 18;
         txtPhone.Size = new System.Drawing.Size (160, 20);
         btnNew.Location = new System.Drawing.Point (472, 336);
         btnNew.Size = new System.Drawing.Size (75, 23);
         btnNew.TabIndex = 25;
         btnNew.Text = "New";
         btnNew.Click += new System.EventHandler (this.btnNew_Click);
         label1.Location = new System.Drawing.Point (40, 224);
         label1.Text = "Company ID";
         label1.Size = new System.Drawing.Size (88, 16);
         label1.TabIndex = 6;
         label2.Location = new System.Drawing.Point (40, 252);
         label2.Text = "Company Name";
         label2.Size = new System.Drawing.Size (88, 16);
         label2.TabIndex = 8;
         label3.Location = new System.Drawing.Point (40, 284);
         label3.Text = "Contact Name";
         label3.Size = new System.Drawing.Size (88, 16);
         label3.TabIndex = 10;
         this.Text = "Customers Update Form";
         this.AutoScaleBaseSize = new System.Drawing.Size (5, 13);
         this.ClientSize = new System.Drawing.Size (584, 421);
         this.Controls.Add (this.label9);
         this.Controls.Add (this.txtPhone);
         this.Controls.Add (this.label8);
         this.Controls.Add (this.txtContactTitle);
         this.Controls.Add (this.label7);
         this.Controls.Add (this.txtZip);
         this.Controls.Add (this.label6);
         this.Controls.Add (this.txtCity);
         this.Controls.Add (this.label5);
         this.Controls.Add (this.txtAddress);
         this.Controls.Add (this.label4);
         this.Controls.Add (this.txtContactName);
         this.Controls.Add (this.label3);
         this.Controls.Add (this.txtCompanyName);
         this.Controls.Add (this.label2);
         this.Controls.Add (this.txtCompanyID);
         this.Controls.Add (this.label1);
         this.Controls.Add (this.btnNew);
         this.Controls.Add (this.txtCustomerName);
         this.Controls.Add (this.btnUpdate);
         this.Controls.Add (this.lblMessage);
         this.Controls.Add (this.btnDelete);
         this.Controls.Add (this.lbCustomers);
      }

      // handle the new button click
      protected void btnNew_Click (object sender, System.EventArgs e)
      {
         // create a new row, populate it
         DataRow newRow = dataTable.NewRow( );
         newRow["CustomerID"]   = txtCompanyID.Text;
         newRow["CompanyName"]  = txtCompanyName.Text;
         newRow["ContactName"]  = txtContactName.Text;
         newRow["ContactTitle"] = txtContactTitle.Text;
         newRow["Address"]      = txtAddress.Text;
         newRow["City"]         = txtCity.Text;
         newRow["PostalCode"]   = txtZip.Text;
         newRow["Phone"]        = txtPhone.Text;

         // add the new row to the table
         dataTable.Rows.Add(newRow);

         // update the database
         try 
         {
           dataAdapter.Update(dataSet,"Customers"); 
           dataSet.AcceptChanges( );

           // inform the user 
           lblMessage.Text = "Updated!";
           Application.DoEvents( );

           // repopulate the list box 
           PopulateLB( );
           // clear all the text fields
           ClearFields( );
         } 
         catch (SqlException ex) 
         {
           dataSet.RejectChanges( );
           MessageBox.Show(ex.Message);
         }

      }

      // set all the text fields to empty strings
      private void ClearFields( )
      {
         txtCompanyID.Text = "";
         txtCompanyName.Text = "";
         txtContactName.Text = "";
         txtContactTitle.Text = "";
         txtAddress.Text = "";
         txtCity.Text = "";
         txtZip.Text = "";
         txtPhone.Text = "";
      }

      // handle the update button click
      protected void btnUpdate_Click (object sender, System.EventArgs e)
      {
         // get the selected row
         DataRow targetRow  = dataTable.Rows[lbCustomers.SelectedIndex];

         // inform the user
         lblMessage.Text = "Updating " +  targetRow["CompanyName"];
         Application.DoEvents( );

         // edit the row
         targetRow.BeginEdit( ); 
         targetRow["CompanyName"] = txtCustomerName.Text;
         targetRow.EndEdit( );

         // get each row that changed
         DataSet dataSetChanged = 
            dataSet.GetChanges(DataRowState.Modified);

         // test to make sure all the changed rows are without errors
         bool okayFlag = true;
         if (dataSetChanged.HasErrors)
         {
            okayFlag = false;
            string msg = "Error in row with customer ID "; 

            // examine each table in the changed DataSet
            foreach (DataTable theTable in dataSetChanged.Tables)
            {
               // if any table has errors, find out which rows
               if (theTable.HasErrors)
               {
                  // get the rows with errors
                  DataRow[] errorRows = theTable.GetErrors( );

                  // iterate through the errors and correct
                  // (in our case, just identify)
                  foreach (DataRow theRow in errorRows)
                  {
                     msg = msg + theRow["CustomerID"];
                  }
               }
            }
            lblMessage.Text = msg;
         }
         // if we have no errors
         if (okayFlag)
         {

            // update the database
            dataAdapter.Update(dataSetChanged,"Customers"); 

            // inform the user
            lblMessage.Text = "Updated " +  targetRow["CompanyName"];
            Application.DoEvents( );

            // accept the changes and repopulate the list box
            dataSet.AcceptChanges( );
            PopulateLB( );
         }
         else  // if we had errors, reject the changes
            dataSet.RejectChanges( );
      }


      // handle the delete button click
      protected void btnDelete_Click (object sender, System.EventArgs e)
      {
         // get the selected row
         DataRow targetRow  = dataTable.Rows[lbCustomers.SelectedIndex];

         // prepare message for user
         string msg = targetRow["CompanyName"] + " deleted. ";

         // delete the selected row
         targetRow.Delete( );

         // update the database
         try 
         {
           dataAdapter.Update(dataSet,"Customers"); 
           dataSet.AcceptChanges( );
           // repopulate the list box without the deleted record
           PopulateLB( );

           // inform the user
           lblMessage.Text = msg;
           Application.DoEvents( );
         } 
         catch (SqlException ex) 
         {
           dataSet.RejectChanges( );
           MessageBox.Show(ex.Message);
         }

      }

      public static void Main(string[] args) 
      {
         Application.Run(new ADOForm1( ));
      }
   }
}

Figure 14-8 shows the filled-out form just before pressing the New button, and Figure 14-9 shows the form immediately after adding the new record.

Figure 14-8. Getting ready to add a new record

Figure 14-9. After adding the new record

Note that the new record is appended to the end of the list and the text fields are cleared.